Поиск

Синтаксис CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] имя_таблицы
[опциитаблицы] [onepaTop_select] или
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] имя_таблицы[(] LIKE старое_имя__таблицы[)];
определение_crea te:
определение_ столбца [ [CONSTRAINT [символ]] PRIMARY KEY [тип_индекса]
{имя_столбца_индекса, ...)
I KEY [имя_индекса] [тип_индекса] {имя_столбца_индекса,.. .) INDEX [имя_индекса] [тип_индекса] {имя_столбца_индекса,...) [CONSTRAINT [символ]] UNIQUE [INDEX]
[имя_индекса] [тип_индекса] {имя_столбца_индекса,...) [FULLTEXT|SPATIAL] [INDEX] [index_name] {имя_столбца_индекса,...) [CONSTRAINT [символ]] FOREIGN KEY
[имяиндекса) {имя_столбца_индекса, ...) [определение_ссылки] I CHECK {выражение)
определение_ столбца:
имя_столбцатип[NOT NULL | NULL] [DEFAULT значение_по _умолчанию] TaUTO_INCREMENT] [[PRIMARY] KEY] [COMMENT 'строка'} [определениессылки]

(любой разрешенный оператор select)
CREATE TABLE создает таблицу с указанным именем. Необходимо иметь привилегию CREATE для таблиц.
Правила именования таблиц описаны в разделе 2.2. По умолчанию таблица создается в текущей базе данных. Если таблица уже существует, если нет текущей базы данных или если вообще нет базы данных, генерируется ошибка.
В MySQL 3.22 и более поздних версиях имя таблицы может специфицироваться как имя_базы_данных.имя_ таблицы, чтобы создать таблицу в указанной базе данных. Это работает независимо от того, есть база данных по умолчанию или нет. Если вы используете идентификаторы в кавычках, то берите в кавычки имя базы и имя таблицы отдельно. Например, 'mydb' . 'mytbl' - правильно, a 'mydb.mytbl' -нет.
Начиная с MySQL 3.23 при создании таблицы можно использовать ключевое слово TEMPORARY. Временная таблица видима только для текущего сеанса и уничтожается автоматически при закрытии соединения. Это означает, что два разных подключения могут использовать одинаковое имя временной таблицы, не конфликтуя друг с другом и с существующей постоянной таблицей с тем же именем. (Постоянная таблица остается скрытой до тех пор, пока не будет удалена временная.) Начиная с MySQL 4.0.2, для создания временных таблиц нужно иметь привилегию CREATE TEMPORARY TABLES.
В MySQL 3.23 и более поздних версиях можно использовать ключевые слова IF NOT EXISTS, при этом, если таблицы существовала, ошибка не генерируется. Отметим, что никакой проверки, чтобы существующая таблица имела структуру, идентичную той, что задана в операторе CREATE TABLE, не производится.
MySQL представляет каждую таблицу файлом формата таблицы . f rm, расположенном в каталоге базы данных. Механизм хранения может создавать для таблицы и другие файлы. В случае таблицы My ISAM механизм хранения создает три файла для таблицы по имени имя _таблицы:

Файл Назначение
имя таблицы.frm Файл описания формата таблицы.
имя_ таблицы.MYD Файл данных.
имя_ таблицы.MYI Индексный файл.

Файлы, создаваемые каждым механизмом хранения, описаны в книге MySQL. Руководство администратора (М. : Издательский дом "Вильяме", 2005, ISBN 5-8459-0805-1).
Общая информация о свойствах различных типов столбцов представлена в главе Типы столбцов Информацию о пространственных типах можно найти в главе Пространственные расширения в MySQL

  1. Если для столбца не указано ни NULL, ни NOT NULL, принимается NULL.
  2. Столбцы целочисленных типов могут иметь атрибут AUTO_INCREMENT. Когда вставляется значение NULL (рекомендуется) или 0 в индексированный столбец AUTO INCREMENT, ему присваивается следующее значение из числовой последовательности. Обычно это будет значение + 1, где значение — наибольшее значение этого столбца, имеющееся в таблице на этот момент. Числовые последовательности AUTO_INCREMENT начинаются с 1.

Начиная с MySQL 4.1.1, указание флага NO_AUTO_VALUEJDN_ZERO для опции сервера —-sql-mode либо для системной переменной sqljnode позволяет сохранять 0 в столбце AUTO_INCREMENT без генерации следующего значения последовательности.
»• На заметку!

  • В таблице может быть только один столбец AUTO_INCREMENT, он должен быть проиндексирован •Ци он не может иметь значения по умолчанию. Начиная с MySQL 3.23, столбец AUTO_INCREMENT работает правильно, только если содержит положительные значения. Вставка отрицательно значения интерпретируется как вставка очень большого положительного. Это сделано для того, чтобы избежать проблем точности, когда число "заворачивается" из положительного в отрицательное, а также для того, чтобы гарантировать, что вы никогда случайно не получите значение 0 в столбце AUTO_INCREMENT.
    Для таблиц My ISAM и BDB можно определить дополнительный столбец
    AUTO_INCREMENT в составном ключе.
    Чтобы обеспечить совместимость MySQL с некоторыми ODBC-приложениями, вы можете получить последнее значение AUTO_INCREMENT, вставленное в строку, спомощью следующего запроса:
    SELECT * FROM имя_таблицы WHERE столбец_АиТ0_1ЫСКЕМЕЫТ IS NULL
  • Начиная с MySQL 4.1, определения символьных столбцов могут содержать атрибут CHARACTER SET для указания набора символов для столбца и, необязательно, порядка сопоставления. Подробности см. в главеПоддержка наборов символов CREATE TABLE t (с CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
    Кроме того, MySQL 4.1 интерпретирует спецификацию длины символьных столбцов в символах (ранние версии интерпретировали ее в байтах).
  • Значение NULL для столбцов типа TIMESTAMP обрабатывается иным образом, чем для других типов столбцов. Вы не можете вставлять литеральный NULL в столбцы TIMESTAMP. Присвоение значения NULL таким столбцам устанавливает их в текущую дату и время. Поскольку столбцы TIMESTAMP ведут себя подобным образом, атрибуты NULL и NOT NULL для этих столбцов игнорируются.
    С другой стороны, чтобы облегчить клиентам MySQL применение столбцов типа timestamp, сервер сообщает, что этим столбцам можно присваивать значение NULL (что истинно), даже несмотря на то, что столбцы этого типа никогда не содержат такого значения. Это можно увидеть, воспользовавшись DESCRIBE имя_таблицы для получения информации о структуре таблицы.

    Отметим, что присвоение столбцу TIMESTAMP значения 0 - это не то же самое, что присвоение NULL, поскольку 0 является допустимым значением TIMESTAMP.

  • Значение DEFAULT должно быть константой, оно не может быть функцией или выражением. Это означает, например, что нельзя установить в качестве значения поумолчанию для столбца типа даты функцию NOW() или CURRENT_DATE. Если никакого значения DEFAULT для столбца не указано, то MySQL автоматически присваивает свое, как описано ниже.
Если столбец допускает Null, значением по умолчанию становится NULL. Если столбец объявлен как NOT NULL, значение по умолчанию зависит от типа:

  1. Для числовых типов, кроме тех, что имеют атрибут AUTO_INCREMENT, устанавливается 0. Для столбцов AUTO_INCREMENT значение по умолчанию — это следующее значение в числовой последовательности.
  2. Для типов дат и времени, кроме TIMESTAMP, умолчанием будет соответствующее "нулевое" значение данного типа. Для первого столбца TIMESTAMP в таблице значением по умолчанию является текущая дата и время. См. раздел Типы даты и времени
  3. Для строковых типов, кроме ENUM, значением по умолчанию является пустая строка. Для ENUM значение по умолчанию - первое в перечислении.

Столбцам типа BLOB и TEXT не может быть назначено значение по умолчанию.

    • Комментарий для столбца может быть указан в опции COMMENT. Комментарий отображается операторами SHOW CREATE TABLE и SHOW FULL COLUMNS. Эта опция действительна, начиная с MySQL 4.1 (в более ранних версиях она разрешалась, но игнорировалась).
    • Начиная с MySQL 4.1.0, атрибут SERIAL может использоваться в качестве псевдонима для BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE. Это средство обеспечения совместимости.
    • Обычно KEY - синоним для INDEX. Начиная с MySQL 4.1, ключевой атрибут PRIMARY KEY также может быть указан для столбцов, как и просто KEY. Это было введено для совместимости с другими системами управления базами данных.
    • В MySQL индекс UNIQUE - это такой индекс, в котором все значения ключа должны быть уникальными. При попытке вставить строку, ключ индекса которой повторяет уже существующий, генерируется ошибка. Исключением из этого правила являются столбцы, входящие в ключ индекса и допускающие значения NULL.
      Это исключение не касается таблиц BDB, в которых уникально индексированный столбец может содержать только один NULL.
    • Первичный ключ (PRIMARY KEY) - это уникальный KEY, в котором все ключевые столбцы определены как NOT NULL. Если они не объявлены явно как NOT NULL, MySQL сделает это неявно (и молча). Если у вас нет первичного ключа в таблице, а приложение его требует, MySQL возвращает первый UNlQUE-индекс, который не содержит в ключей NULL-столбцов, в качестве PRIMARY KEY.
    • В созданной таблице PRIMARY KEY размещается первым, за ним следую

  • PRIMARY KEY может быть индексом с составным ключом. Однако вы не можете создать составной индекс, используя атрибут PRIMARY KEY в спецификации столбца. Если это сделать, только первый столбец будет отмечен как первичный ключ. Вы должны использовать отдельную конструкцию PRIMARY KEY {имя_столбца_индекса,...).
  • Если PRIMARY KEY или UNlQUE-индекс построены по одному столбцу целочисленного типа, вы также можете ссылаться на этот столбец в операторах SELECT как на _rowid (введено в MySQL 3.23.11).
  • Начиная с MySQL 4.1.0, некоторые механизмы хранения позволяют специфицировать тип индекса при его создании. Синтаксис спецификации типа индекса выглядит как USING имя_типа. Допустимые значения имя_типа, которые поддерживаются различными механизмами хранения, показаны ниже. Там, где перечислено несколько типов индексов, по умолчанию (при отсутствии спецификации типа) является первый из них:

    Пример:
    CREATE TABLE lookup
    (id INT, INDEX USING BTREE (id)) ENGINE = MEMORY;
    При спецификации типа индекса в качестве синонима USING имя_типа можно использовать TYPE имя_типа. Однако USING - предпочтительная форма. Кроме того, если спецификация индекса определена словом TYPE, то имя индекса в этом случае обязательно, так как в отличие от USING, слово TYPE не является зарезервированным словом, и при отсутствии имени индекса будет интерпретироваться как это имя.
    Если вы указываете недопустимый для данного механизма хранения тип индекса, но существует другой допустимый тип, который механизм может использовать без влияния на результаты запросов, механизм использует этот тип.

    1. Только механизмы хранения MylSAM, InnoDB, BDB и (начиная с MySQL 4.0.2) MEMORY поддерживают индексы по столбцам, которые могут содержать значения NULL. В остальных случаях вы должны декларировать столбцы, входящие в ключ индекса, как NOT NULL, иначе возникнет ошибка.
    2. Используя синтаксис col_name [длина) в спецификации индекса, вы можете создать индекс, который в ключе использует только первые длина символов значений столбца CHAR или VARCHAR. Индексирование только префикса значений столбца может существенно уменьшить размер индексного файла.

    Механизм хранения MylSAM и (начиная с MySQL 4.0.14) InnoDB также поддерживают индексацию столбцов TEXT и BLOB. При индексации столбцов TEXT и BLOB длину индексируемого префикса указывать обязательно, например: CREATE TABLE test (blob col BLOB, INDEX(blob col (10)));

    Префиксы могут быть до 255 символов длиной (или 1000 символов для таблиц MylSAM и innoDB в MySQL версии 4.1.2 и выше). Следует отметить, что максимальная длина индексируемого префикса измеряется в байтах, в то время как длина префикса в спецификации CREATE TABLE интерпретируется как количество символов. Об этом необходимо помнить, когда индексируемый столбец использует многобайтный набор символов.

    1. Спецификация имя_столбца_индекса может завершаться словом ASC или DESC. Эти слова предусмотрены для будущих расширений, чтобы указывать порядок хранения индексируемых значений - по возрастанию или по убыванию. В настоящее время они распознаются, но игнорируются. Индексные значения всегда хранятся в возрастающем порядке.
    2. При использовании конструкций ORDER BY или GROUP BY для столбцов BLOB или TEXT, сервер сортирует значения, используя только определенное число начальных байтов, которое задается системной переменной max_sort_length. См. раздел Типы BLOB и TEXT
    3. В MySQL 3.23.23 и более поздних версиях есть возможность создавать индексы FULLTEXT. Они используются для полнотекстового поиска. Индексы FULLTEXT поддерживают только таблицы MyISAM. Они могут быть созданы только на столбцах CHAR, VARCHAR или TEXT. Индексация при этом выполняется по полной длине значения столбцы. Частичная индексация префикса не поддерживается, и если указать длину префикса, она игнорируется. См. детали в разделеФункции полнотекстового поиска
    4. В MySQL 4.1 и выше можно создавать индексы SPATIAL по столбцам пространственных (spatial) типов. Пространственные типы поддерживаются только механизмом хранения MyISAM и индексируемые столбцы должны быть объявлены как NOT NULL. См. главу Пространственные расширения в MySQL
    5. В MySQL 3.23АЛ и выше таблицы InnoDB поддерживают проверку ограничений целостности внешних ключей. Отметим, что синтаксис FOREIGN KEY для InnoDB более ограничивающий, чем синтаксис оператора CREATE TABLE, представленный в начале раздела. Столбцы таблиц, на которые ссылается внешний ключ, должны быть всегда именованы явно. InnoDB поддерживает действия ON DELETE и ON UPDATE для внешних ключей, начиная с версии MySQL 3.23.50 и MySQL 4.0.8 соответственно. Точный синтаксис представлен в разделе Создание внешних ключей

    Для других механизмов хранения сервер MySQL разбирает синтаксис FOREIGN KEY и REFERENCES в операторе CREATE TABLE, но без каких-либо действий. Конструкция CHECK распознается, однако игнорируется всеми механизмами хранения.

    • Для таблиц MyISAM и ISAM каждый NULL-столбец требует дополнительного бита, который округляется до ближайшего байта. Максимальная длина в байтах может быть рассчитана следующим образом:
    длина строки = 1
    + {сумма длин столбцов)
    + {количество NULL-столбцов + флаг_удаления + 7)/8
    + {количество столбцов переменной длины)
    флаг_удаления равен 1 для таблиц со статическим форматом записи. Статические таблицы используют бит в записи строки в качестве признака того, что запись удалена. флаг_удаления равен 0 для динамических таблиц, потому что флаг сохраняется в динамическом заголовке строки.

Эти вычисления неприменимы к таблицам InnoDB, в которых размер хранения не отличается для столбцов NULL и NOT NULL.
Часть опции_таблицы синтаксиса CREATE TABLE может применяться в MySQL 3.23 и выше.
Опции ENGINE и TYPE специфицируют механизм хранения для таблицы. Опция ENGINE появилась в MySQL 4.0.18 (для серии 4.0) и в 4.1.2 (для серии 4.1). Это предпочтительный вариант наименования опции в этих версиях, a TYPE считается устаревшей. TYPE еще поддерживается в серии выпусков 4.x, но вероятно, будет исключена в MySQL 5.1.
Опции ENGINE и TYPE принимают следующие значения:

Если указывается несуществующий механизм хранения, вместо него MySQL использует MyISAM. Например, если определение таблицы включает опцию ENGINE=BDB, а сервер MySQL не поддерживает таблиц BDB, то таблица создается как MyISAM. Это дает возможность настроить среду репликации, в которой применяются транзакционные таблицы на главном сервере и нетранзакционные - на подчиненном (чтобы получить более высокую скорость). В MySQL 4.1.1 генерируется предупреждение, если заданные механизм хранения не поддерживается.
Характеристики механизмов хранения подробно обсуждаются в книге MySQL. Руководство администратора (М. : Издательский дом "Вильяме", 2005, ISBN 5-8459-0805-1).
Другие опции таблиц служат для оптимизации поведения таблицы. В большинстве случаев вам не придется указывать ни одну из них. Опции работают со всеми механизмами хранения, если не указано другое:

  1. AUTO_INCREMENT. Начальное значение AUTO_INCREMENT для таблицы. Это работает только с таблицами MyISAM. Для того чтобы установить первое значение последовательности для таблицы InnoDB, необходимо вставить строку со значением соответствующей столбцы, на единицу меньшим, чем желательное начальное значение, а затем удалить ее.
  2. AVG_ROW_LENGTH. Предполагаемое среднее значение длины строки в таблице. Это необходимо устанавливать только для больших таблиц с записями переменной длины.

Когда создается таблица MyISAM, MySQL использует произведение значений опций maxJrows на avg_ROW_length, чтобы рассчитать предполагаемый размер таблицы. Если не указана любая из этих опций, максимальный размер таблицы составит 4 Гбайт (или 2 Гбайт, если операционная система поддерживает файлы размером только 2 Гбайт). Смысл этого состоит в уменьшении размера указателя записей, чтобы сделать индексы меньшими и быстрыми в случае, когда нет необходимости в большой таблице. Если вы хотите всем таблицам обеспечить возможность роста за пределами ограничения в 4 Гбайт, и согласны с тем, что даже маленькие таблицы будут занимать несколько больше места и работать медленнее, чем необходимо, можете увеличить размер указателя записей по умолчанию, установив системную переменную myisam data_pointer_size, которая была добавлена в MySQL 4.I .2.

  1. CHECKSUM. Установите значение этой опции равным 1, если хотите, чтобы MySQL поддерживал живую контрольную сумму всех строк таблицы (то есть контрольную сумму, которую MySQL обновляет автоматически при каждом изменении таблицы). Это немного замедлит обновления данных в таблице, но облегчит поиск поврежденных таблиц. Оператор CHECKSUM TABLE сообщает эту контрольную сумму (только для MyISAM).
  2. COMMENT. Комментарий к таблице, не более 60 символов длиной.
  3. MAX__ROWS. Максимальное количество строк, которое планируется хранить в таблице.
  4. MIN__ROWS. Минимальное количество строк, которое планируется хранить в таблице.
  5. PACK_KEYS. Эту опцию нужно установить в 1, если необходимо сделать индексы поменьше. Обычно это немного замедляет запись, однако ускоряет чтение. Установка этой опции в 0 отключает упаковку ключей. Установка ее в default (MySQL 4.0) сообщает механизму хранения, что паковать нужно только длинные столбцы типа CHAR/VARCHAR (только для ISAM и MylSAM).

Если не указывать опцию PACK KEYS, по умолчанию пакуются только строки, но не числа. Если указать PACK_KEYS=1, то числа тоже пакуются. При упаковке двоичных числовых ключей MySQL применяет сжатие префиксов:

  1. Каждый ключ требует дополнительного байта для указания того, сколько байт предыдущего ключа повторяются в следующем.
  2. Указатель строки сохраняется в порядке "старший байт первый" непосредственно после ключа, чтобы улучшить сжатие.

Это означает, что если у вас много равных ключей в двух последовательных строках, все последующие "такие же" ключи обычно займут только два байта (включая указатель на строку).
Сравните это с обычным случаем, когда каждый следующий ключ потребует для хранения размер__хранения_для_ключа + размер_указателя (размер указателя обычно равен 4). Однако выгода от сжатия префиксов будет ощутимой только в том случае, когда есть много равных значений числового ключа. Когда все ключи абсолютно разные, то на каждый ключ потребуется на один байт больше, если ключ не может иметь значений NULL. (В этом случае длина упакованного ключа будет сохранена в том же байте, который служит для хранения признака того, что ключ NULL).

PASSWORD. Шифрует файл .frm с использованием пароля. В стандартной версии MySQL эта опция не делает ничего.

  • DELAy_key_write. Эту опцию нужно установить в 1, если вы хотите отложить обновления ключей таблицы до момента ее закрытия (только для MyISAM).
  • ROW_FORMAT. Определяет, как должна сохраняться строка. В настоящее время эта опция работает только с таблицами MyISAM. Ее значением может быть FIXED или DYNAMIC, соответственно, для статического формата строки или формата с переменной длиной, myisampack устанавливает тип COMPRESSED.
  • RAID_TYPE. Опция RAID_TYFE помогает преодолеть лимит размера файла данных MyISAM в 2 Гбайт/4 Гбайт (не касается индексных файлов) в операционных системах, которые не поддерживают большие файлы.

Вы можете повысить скорость операций ввода-вывода, поместив RAID-каталоги на разные физические диски. На данный момент допускается только RAID_TYPE и STRIPPED. 1 и RAID0 - это псевдонимы для STRIPPED.
Если вы указываете опцию RAID_TYPE для таблицы MyISAM, указывайте также RAID_CHUNKS и raid_Chunksize. Максимальное значение RAID__CHUNKS равно 255. MylSAM создаст подкаталоги RAID_CHUNKS с именами 00,01, 02,... 09, 0а, 0Ь,... в каталоге базы данных. В каждом их этих подкаталогов MyISAM создаст файл имя_таблицы.ШХ). При записи в файл данных обработчик RAID отображает первые RAID_CHUNKSIZE*1024 байт на первый файл, следующие RAID_C.HUNKSIZE*1024 байт на второй файл и так далее.
Опция RAID_TYPE работает под управлением любой операционной системы, если MySQL был собран с помощью configure —with-raid. Чтобы определить, поддерживает ли сервер RAID-таблицы, выполните SHOW VARIABLES LIKE 'have_raid' и посмотрите, установлено ли значение переменной have_raid в YES.

  • union. Применяется, когда нужно использовать коллекцию идентичных таблиц как одну. Это работает только с таблицами MERGE.
На данный момент вы должны иметь привилегии SELECT, UPDATE и DELETE на таблицы, отображаемые в merge. Первоначально все используемые таблицы должны были располагаться в том же каталоге, что и таблица MERGE. В MySQL 4.1.1 это ограничение снято.

  • INSERT_METHOD. Если необходимо вставлять данные в таблицу MERGE, вы должны указывать с помощью опции INSERT_METHOD, в какую физическую таблицу должна вставляться строка. INSERT_METHOD - опция, применимая только к таблицам MERGE. Она была представлена впервые в MySQL 4.O.O.
  • DATA DIRECTORY, INDEX DIRECTORY

Используя DATA DIRECTORY='icarajJor1 ИЛИ INDEX DIRECTORY='каталог', вы можете указать, где механизм хранения MyISAM должен размещать файлы данных и индексов. Имейте в виду, что нужно указывать полный путь, а не относительный. Эти опции работают только с таблицами MyI SAM, начиная с MySQL 4.0 и выше, когда вы не применяете опцию -skip-symbolic-links. Ваша операционная система также должна иметь работающий, безопасный в отношении потоков системный вызов realpath ().

Начиная с MySQL 3.23, вы можете создавать одну таблицу из другой, добавляя SELECT в конец оператора CREATE TABLE:
CREATE TABLE имя_новой_ та блицы SELECT * FROM имя_исходной_таблицы; MySQL создаст новые столбцы для всех элементов SELECT. Например:
mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT, -> PRIMARY KEY (a), KEY(b)) -> TYPE=MyISAM SELECT b,c FROM test2;
Здесь создается таблица MyISAM с тремя столбцами, a, b и с. Следует отметить, что столбцы из оператора Select добавляются в правую часть таблицы, не перемешиваясь с имеющимися. Рассмотрим следующий пример:

Для каждой строки таблицы foo вставляется строка в bar, со значениями, выбранными из foo, и значениями по умолчанию для новых столбцов.
Если при этом происходит какая-либо ошибка, новая таблица не создается.
CREATE TABLE.. .SELECT не создает автоматически какие-либо индексы. Так сделано намерено, чтобы обеспечить максимально возможную гибкость данного оператора. Если вы хотите иметь индексы в создаваемой таблице, то должны будете указать следующее перед оператором SELECT:
mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
При этом могут происходить некоторые преобразования типов столбцов. Например, не сохраняется атрибут AUTO_INCREMENT, и столбцы VARCHAR меняют тип на CHAR.
При создании таблицы оператором CREATE TABLE.. .SELECT убедитесь, что все вызовы функций и выражения, используемые в запросе, снабжены псевдонимами. Если этого не сделать, то оператор CREATE завершится аварийно, или же вы получите нежелательные имена столбцов в новой таблице.
CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works FROM artist LEFT JOIN work ON artist.id = work.artist_id GROUP BY artist.id;
Начиная с MySQL 4.1, типы сгенерированных столбцов можно задавать явно:

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
В MySQL 4.1 вы также можете применить LIKE для создания пустой таблицы на основе определения другой таблицы, включая любьй атрибуты столбцов и индексы, которые имеются в исходной таблице:
CREATE TABLE имя_новой__таблицыLIKE имя исходнойтаблицы;
CREATE TABLE...LIKE не копирует ОПЦИИ DATA DIRECTORY ИЛИ INDEX DIRECTORY, Kоторые были специфицированы в исходной таблице.
Вы можете предварить SELECT словами IGNORE или REPLACE, чтобы указать, как нужно обрабатывать записи с дублированными значениями уникальных ключей. Если указано IGNORE, новые записи с дублированными уникальными ключами отбрасываются. Если же указано REPLACE, новые записи заменяют существующие с такими же значениями уникальных ключей. Если не указано ни ignore, ни replace, то появление записей с дублированными значениями ключей приводит к ошибке.
Чтобы гарантировать, что журнал обновления/бинарный журнал могут быть использованы для пересоздания исходных таблиц, MySQL не разрешает параллельные вставки ВО время работы CREATE TABLE. .. SELECT.

т все уникальные индексы, затем неуникальные индексы. Это помогает оптимизатору MySQL правильно расставлять приоритеты в выборе индексов и быстрее обнаруживать дублирующие уникальные ключи.

Создание внешних ключей
Начиная с MySQL 3.23.44, механизм InnoDB оснащен ограничениями внешних ключей. Синтаксис определения внешнего ключа InnoDB выглядит так:
[CONSTRAINT символ] FOREIGN KEY [идентификатор] {имя__столбца__индекса, ...)
REFERENCES шя_таблиць1 {имя_столбца__индекса,...)
[ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT | SET DEFAULT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION | RESTRICT | SET DEFAULT}]
Обе таблицы должны иметь тип InnoDB. В ссылающейся таблице должен быть индекс, у которого столбец внешнего ключа является первым в списке ключевых столбцов. У таблицы, на которую осуществляется ссылка, должен быть индекс, в ключе которого столбец, на который осуществляется ссылка, указан первым. Индексы на префиксах столбцов для внешних ключей не поддерживаются.
InnoDB требует индексов на внешних и ссылаемых ключах, чтобы проверка ограничений внешнего ключа выполнялась быстрее и не требовала сканирования таблиц.
Начиная с MySQL 4.1.2, эти индексы создаются автоматически. В более старых версиях индексы должны были создаваться явно, в противном случае попытки создания внешних ключей завершались ошибкой.
Связанные столбцы во внешнем ключе и в ключе, на который они ссылаются, должны иметь одинаковый внутренний тип данных InnoDB, чтобы их можно было сравнивать без преобразования типов. Размер и наличие знака столбцов целочисленных типов должны быть одинаковыми. Длины строковых типов не обязательно должны быть равны. Если вы выполняете действие SET NULL, убедитесь, что столбец дочерней таблицы не объявлен как not null.
Если MySQL сообщает об ошибке 1005 при попытке выполнить CREATE TABLE, и сообщение об ошибке ссылается на номер errno 150, это означает, что создание таблицы завершилось неудачей, потому что ограничение внешнего ключа не было корректно сформировано. Подобным же образом, если ALTER TABLE завершается аварийно и возвращает ссылку на номер errno 1 50, это означает, что определение внешнего ключа неверно сформировано для изменяемой таблицы. Начиная с MySQL 4.0.13, вы можете использовать SHOW INNODB STATUS, чтобы отобразить детальное объяснение последней произошедшей на сервере ошибки, связанной с внешним ключом.
Начиная с MySQL 3.23.50, InnoDB не проверяет ссылочную целостность внешних ключей или ключей, на которые осуществляется ссылка, если их столбцы содержат значение NULL.
Отклонение от стандарта SQL. Если в родительской таблице есть несколько строк, имеющих одинаковые значения ключей, на которые ссылается внешний ключ, InnoDB при проверке ограничений внешнего ключа поступает так, будто родительских строк с одинаковыми значениями ключей не существует. Например, если вы определили ограничение типа RESTRICT, и существуют дочерние записи с несколькими родительскими записями, InnoDB не позволяет удалять ни одну из этих родительских записей.
Начиная с MySQL 3.23.50, вы также можете ассоциировать конструкции ON DELETE CASCADE или ON DELETE SET NULL с ограничением внешнего ключа. Соответствующая опция ON UPDATE доступна, начиная с версии 4.0.8. Если указано ON DELETE CASCADE и удаляется строка в родительской таблице, то дочерние записи, у которых значение внешнего ключа совпадает со значением ссылаемого ключа в родительской записи, также автоматически удаляются. Если указано ON DELETE SET NULL и удаляется строка в родительской таблице, то дочерние записи автоматически обновляются таким образом, что столбцы внешнего ключа принимают значение NULL.
SET DEFAULT распознается, но игнорируется.
InnoDB выполняет каскадные операции по алгоритму поиска в глубину, на основе записей в индексах, соответствующих ограничениям внешних ключей.
Отклонение от стандарта SQL. Если ON UPDATE CASCADE или ON UPDATE SET NULL рекурсивно обновляют myже таблицу, которая уже обновляется каскадным способом, это работает как при RESTRICT. Это означает, что вы не можете иметь операции on UPDATE CASCADE или ON UPDATE SET NULL, ссылающиеся сами на себя. Так сделано для предотвращения бесконечного зацикливания каскадных обновлений. С другой стороны, ссылающиеся на себя ON DELETE SET NULL допускаются, начиная с версии 4.0.13. Ссылающиеся на себя ON DELETE CASCADE возможны с тех пор, как реализовано ON DELETE.
Простой пример, в котором связываются родительская и дочерняя таблицы через одностолбцовый внешний ключ:
CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id) ) TYPE=INNODB; CREATE TABLE child(id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent (id)
ON DELETE CASCADE ) TYPE=INNODB;
Более сложный пример, в котором таблица product_order имеет внешний ключ на две другие таблицы. Один их них ссылается на двухстолбцовый индекс в таблице product, а другой -на одностолбцовый в таблице customer:
CREATE TABLE product (category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id)) TYPE=INNODB;

CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) TYPE=INNODB; CREATE TABLE product_order (no INT NOT NULL AUTO_ INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product__category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) TYPE=INNODB;
Начиная с MySQL 3.23.50, анализатор выражений innoDB позволяет использовать обратные кавычки вокруг имен таблиц и столбцов в конструкциях FOREIGN KEY... REFERENCES... Начиная с MySQL 4.0.5, анализатор выражений InnoDB принимает во внимание установку системной переменной lower_case_table_names.

Неявные изменения спецификаций столбцов
В некоторых случаях MySQL без предупреждений изменяет спецификации столбцов по сравнению с теми, что были заданы оператором CREATE TABLE или ALTER TABLE:

  1. Столбцы VARCHAR длиной менее четырех символов получают тип CHAR.
  2. Если любой из столбцов таблицы имеет переменную длину, вся строка в результате имеет переменную длину. Поэтому, если таблица содержит любой столбец с типом переменной длины (VARCHAR, TEXT или BLOB), все столбцы CHAR длиной более трех символов становятся VARCHAR. Это не влияет на то, как столбцы будут использоваться. В MySQL тип VARCHAR - это просто другой способ хранения символов. MySQL выполняет это преобразование, потому что это позволяет сэкономить место на диске и ускоряет операции работы с таблицей.
  3. Начиная с MySQL 4.1.0, столбцы типа CHAR и VARCHAR длиной более 255 символов преобразуются к минимальному типу TEXT, который может вместить заданное количество символов. Например, varchar (500) преобразуется в text, a VARCHAR (200000) - в MEDIUMTEXT. Это средство обеспечения совместимости.
  4. Размер отображения типа TIMESTAMP исключен из MySQL 4.1 из-за изменений, которые претерпел тип столбцов TIMESTAMP в этой версии. До MySQL 4.1 размер отображения TIMESTAMP должен был быть в пределах от 2 до 14. Если указать размер отображения 0 или более 14, он принимался равным 14. Нечетные величины размера от 1 до 13 округлялись до ближайшего большего четного.
  5. Невозможно присвоить литеральное значение NULL столбцу TIMESTAMP. Присвоение NULL устанавливает его в текущую дату и время. Поскольку столбцы TIMESTAMP ведут себя подобным образом, атрибуты NULL и NOT NULL неприменимы к ним в привычном смысле и потому игнорируются. DESCRIBE имя_таблицы всегда сообщает, что столбцы TIMESTAMP могут принимать значения NULL.
  6. Столбцы, входящие в определение первичного ключа становятся NOT NULL независимо от того, задано ли это явным образом.
  7. Начиная с MySQL 3.23.51, завершающие пробелы автоматически удаляются из значений-членов ENUM и SET при создании таблицы.
  8. MySQL отображает некоторые типы столбцов, используемые другими поставщиками SQL-серверов, в типы MySQL. См. раздел Использование типов столбцов их других систем управления базами данных/li>
  9. Если вы включите конструкцию USING, чтобы специфицировать тип индекса, который не допустим для данного механизма хранения, но при этом существует другой тип индексов, который может быть использован без влияния на исполнение запросов, то механизм хранения использует этот тип.

Чтобы увидеть, изменил ли MySQL тип столбцов по сравнению с заданным вами, воспользуйтесь оператором DESCRIBE или SHOW CREATE TABLE после создания или изменения таблиц.
Если вы сжимаете таблицу с помощью myisampack, также могут произойти и другие изменения типов столбцов.