Создание таблиц по результатам выборки данных

Одной из ключевых концепций реляционных баз данных является то, что вся информация представляется в виде таблиц, состоящих из столбцов и строк. Результат работы каждого оператора select также является таблицей из столбцов и строк. В любом случае, таблица , являющаяся результатом работы оператора select-, изображается в виде строк и столбцов. До версии 3.23 для сохранения результатов работы оператора select в новой таблице пользователю необходимо бьшо выполнить следующие действия.

1. Запустить запросы describe или show columns для определения типов столбцов в таблицах, из которых будет выбрана информация.

2. Создать таблицу и явно объявить в ней имена и типы столбцов, которые стати известны на предьщущем щаге.

3. После создания таблицы сделать запрос insert . . . select для добавления найденных значений в таблицу.

В версии 3.23 все не так. Теперь оператор create table . . . select позволяет создавать таблицу на основании произвольного оператора select. В этом случае все можно сделать одним действием, не определяя при этом структуры создаваемой таблицы. Это исключительно облегчает задачу создания таблицы, уже заполненной данными.

Таким образом, таблицу можно скопировать полностью или с помощью заведомо неработающего предложения where.

CREATE TABLE new tbl name SELECT * FROM tbl name

CREATE TABLE new tbl name SELECT * FROM tbl name WHERE 1=0

Таким приемом можно воспользоваться при зафузке данных в таблицу оператором load data, когда нет полной уверенности в том, что формат данных определен верно, и необходимо немного поэкспериментировать со структурой таблицы. Пустая копия позволит делать это до тех пор, пока пользователь не будет полностью удовлетворен результатами. После этого данные можно смело загружать в реальную таблицу.

Аналогичная последовательность действий допустима и для оператора create temporary table:

CREATE TEMPORARY TABLE new tbl name SELECT * FROM tbl name

Это позволит модифицировать содержимое таблицы mytbl, не нанося вреда основной таблице. Такая возможность окажет существенную помощь при отладке запросов, изменяющих содержимое таблицы. При этом не нужно подвергать серьезным изменениям уже существующие сценарии. Достаточно добавить оператор create temporary table в начало сценария. Такой сценарий создаст временную копию и будет ее обрабатывать, а после завершения работы сценария сервер удалит временную копию.

Для создания таблицы, являющейся пустой копией самой себя, воспользуйтесь комбинацией предложения WHERE с оператором CREATE

TEMPORARY ... SELECT.

CREATE TEMPORARY TABLE new tbl name SELECT * FROM tbl name WHERE 1=0

Однако такой метод создания таблиц на лету имеет свои недостатки. При создании таблицы таким образом, ее столбцы получают имена, соответствующие именам таблицы-родителя. Вычисляемые столбцы могут иметь в качестве имени текст выражения. А выражение в качестве имени столбца недопустимо. Это можно проверить, запустив следующий запрос:

mysql>CREATE TABLE ту 1Дз1 SELECT 1 ;

Error 1166 at line 1: Incorrect column name 1

Для того чтобы этот запрос заработал, присвойте такому столбцу псевдоним:

mysql>CREATE TABLE шу 1±>1 SELECT 1 AS my col;

Query OK, 1 row affected (0.01 sec)

Аналогичная ситуация происходит при создании новой таблицы на основании данных, взятых из двух таблиц, имеющих в своем составе одноименные столбцы. Предположим, что таблицы tl-n t2 имеют в своей структуре столбец с. Чтобы решить эту проблему, присвоим псевдонимы столбцам с из обеих таблиц:

CREATE TABLE t3 SELECT tl.c AS cl, t2.с AS c2 FROM tl,t2;

При создании таблиц таким образом индексы не копируются.

Оператор DROP TABLE

Процедура удаления таблицы значительно проще процедуры создания. При этом не требуется предварительно определять структуру таблицы. Достаточно только указать ее имя:

DROP TABLE tbl name

Функции оператора DROP TABLE в СУБД MySQL несколько расширены. Первое - появилась возложность удалять сразу несколько таблиц, указав их имена в одном операторе:

DROP TABLE tbl namel, tbl name2 ...

Второе - с помощью условного оператора IF EXISTS можно проверить, существует ли такая таблица:

DROP TABLE IF EXISTS tbl name

Такая конструкция оператора DROP TABLE может очень пригодиться при работе с клиентской программой mysql. При обнаружении ошибки он обычно завершает работу, а ошибочная ситуация возникает при по-

пытке удалить несуществующую таблицу. Предположим, что есть сценарий, создающий таблицы, задействованные в других сценариях. В таком случае необходимо убедиться, что этот сценарий имеет чистое поле деятельности для своей работы. При первом запуске сценария, в начале которого используется обычный оператор drop table, обязательно произойдет сбой, потому что такие файлы никогда не создавались. При использовании модифицированного синтаксиса с использованием if EXISTS таких проблем не возникает. Если таблицы существуют, они будут удалены, если нет, сценарий просто будет выполняться дальще.

Создание и удаление индексов

Индексы являются основным средством ускорения доступа к содержимому таблиц, особенно это касается запросов, включающих объединение нескольких таблиц. Это достаточно важная тема и она обсуждается в главе 4, Оптимизация запросов (там вы узнаете, зачем используются индексы, как они работают, как с их помощью оптимизировать работу запросов). В этом разделе мы рассмотрим характеристики индексов и синтаксис, который используется для их создания и удаления.

Характеристики индексов

СУБД MySQL обеспечивает достаточно гибкие способы составления индексов. Таблицу можно индексировать как по одному столбцу, так и по комбинации столбцов. Одна таблица может иметь несколько индексов. Это позволит производить быстрый просмотр одной таблицы по нескольким столбцам. Любой строковый тип (кроме ENUM или SET) можно индексировать по первым п символам слева. Если первые п символов уникальны, это уменьшит размер индекса и увеличить скорость обработки.

Конечно, на создание индексов тоже существуют некоторые ограничения, но по мере развития СУБД MySQL их круг будет сужаться. Вот таблица, которая показывает различные возможности индексирования таблиц в форматах ISAM и MylASM.