6.5.3 Синтаксис оператора CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)]
[table_options] [select_statement]

create_definition:
  col_name type [NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT]
            [PRIMARY KEY] [reference_definition]
  или   PRIMARY KEY (index_col_name,...)
  или   KEY [index_name] (index_col_name,...)
  или   INDEX [index_name] (index_col_name,...)
  или   UNIQUE [INDEX] [index_name] (index_col_name,...)
  или   FULLTEXT [INDEX] [index_name] (index_col_name,...)
  или   [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
            [reference_definition]
  или   CHECK (expr)

type:
        TINYINT[(length)] [UNSIGNED] [ZEROFILL]
  или   SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
  или   MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
  или   INT[(length)] [UNSIGNED] [ZEROFILL]
  или   INTEGER[(length)] [UNSIGNED] [ZEROFILL]
  или   BIGINT[(length)] [UNSIGNED] [ZEROFILL]
  или   REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
  или   DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
  или   FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
  или   DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
  или   NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
  или   CHAR(length) [BINARY]
  или   VARCHAR(length) [BINARY]
  или   DATE
  или   TIME
  или   TIMESTAMP
  или   DATETIME
  или   TINYBLOB
  или   BLOB
  или   MEDIUMBLOB
  или   LONGBLOB
  или   TINYTEXT
  или   TEXT
  или   MEDIUMTEXT
  или   LONGTEXT
  или   ENUM(value1,value2,value3,...)
  или   SET(value1,value2,value3,...)

index_col_name:
        col_name [(length)]

reference_definition:
        REFERENCES tbl_name [(index_col_name,...)]
                   [MATCH FULL | MATCH PARTIAL]
                   [ON DELETE reference_option]
                   [ON UPDATE reference_option]

reference_option:
        RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

table_options:
        TYPE = {BDB | HEAP | ISAM | InnoDB | MERGE | MRG_MYISAM | MYISAM }
или     AUTO_INCREMENT = #
или     AVG_ROW_LENGTH = #
или     CHECKSUM = {0 | 1}
или     COMMENT = "string"
или     MAX_ROWS = #
или     MIN_ROWS = #
или     PACK_KEYS = {0 | 1 | DEFAULT}
или     PASSWORD = "string"
или     DELAY_KEY_WRITE = {0 | 1}
или     ROW_FORMAT= { default | dynamic | fixed | compressed }
или     RAID_TYPE= {1 | STRIPED | RAID0 } RAID_CHUNKS=#  RAID_CHUNKSIZE=#
или     UNION = (table_name,[table_name...])
или     INSERT_METHOD= {NO | FIRST | LAST }
или     DATA DIRECTORY="абсолютный путь к каталогу"
или     INDEX DIRECTORY="абсолютный путь к каталогу"

select_statement:
        [IGNORE | REPLACE] SELECT ...  (любое корректное выражение SELECT)

Оператор CREATE TABLE создает таблицу с заданным именем в текущей базе данных. Правила для допустимых имен таблицы приведены в разделе section 6.1.2 Имена баз данных, таблиц, столбцов, индексы псевдонимы. Если нет активной текущей базы данных или указанная таблица уже существует, то возникает ошибка выполнения команды.

В версии MySQL 3.22 и более поздних имя таблицы может быть указано как db_name.tbl_name. Эта форма записи работает независимо от того, является ли указанная база данных текущей.

В версии MySQL 3.23 при создании таблицы можно использовать ключевое слово TEMPORARY. Временная таблица автоматически удаляется по завершении соединения, а ее имя действительно только в течение данного соединения. Это означает, что в двух разных соединениях могут использоваться временные таблицы с одинаковыми именами без конфликта друг с другом или с существующей таблицей с тем же именем (существующая таблица скрыта, пока не удалена временная таблица). В версии MySQL 4.0.2 для создания временных таблиц необходимо иметь привилегии CREATE TEMPORARY TABLES.

В версии MySQL 3.23 и более поздних можно использовать ключевые слова IF NOT EXISTS для того, чтобы не возникала ошибка, если указанная таблица уже существует. Следует учитывать, что при этом не проверяется идентичность структур этих таблиц.

Каждая таблица tbl_name представлена определенными файлами в директории базы данных. В случае таблиц типа MyISAM это следующие файлы:

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

Чтобы получить более полную информацию о свойствах различных типов столбцов, section 6.2 Типы данных столбцов:

  • Если не указывается ни NULL, ни NOT NULL, то столбец интерпретируется так, как будто указано NULL.
  • Целочисленный столбец может иметь дополнительный атрибут AUTO_INCREMENT. При записи величины NULL (рекомендуется) или 0 в столбец AUTO_INCREMENT данный столбец устанавливается в значение value+1, где value представляет собой наибольшее для этого столбца значение в таблице на момент записи. Последовательность AUTO_INCREMENT начинается с 1. See section 8.4.3.126 mysql_insert_id(). Если удалить строку, содержащую максимальную величину для столбца AUTO_INCREMENT, то в таблицах типа ISAM или BDB эта величина будет восстановлена, а в таблицах типа MyISAM или InnoDB - нет. Если удалить все строки в таблице командой DELETE FROM table_name (без выражения WHERE) в режиме AUTOCOMMIT, то для таблиц всех типов последовательность начнется заново. Примечание: в таблице может быть только один столбец AUTO_INCREMENT, и он должен быть индексирован. Кроме того, версия MySQL 3.23 будет правильно работать только с положительными величинами столбца AUTO_INCREMENT. В случае внесения отрицательного числа оно интерпретируется как очень большое положительное число. Это делается, чтобы избежать проблем с точностью, когда числа ``заворачиваются'' от положительного к отрицательному и, кроме того, для гарантии, что по ошибке не будет получен столбец AUTO_INCREMENT со значением 0. В таблицах MyISAM и BDB можно указать вторичный столбец AUTO_INCREMENT с многостолбцовым ключом. See section 3.5.9 Использование атрибута AUTO_INCREMENT. Последнюю внесенную строку можно найти с помощью следующего запроса (чтобы сделать MySQL совместимым с некоторыми ODBC-приложениями): 
    SELECT * FROM tbl_name WHERE auto_col IS NULL
  • Величины NULL для столбца типа TIMESTAMP обрабатываются иначе, чем для столбцов других типов. В столбце TIMESTAMP нельзя хранить литерал NULL; при установке данного столбца в NULL он будет установлен в текущее значение даты и времени. Поскольку столбцы TIMESTAMP ведут себя подобным образом, то атрибуты NULL и NOT NULL неприменимы в обычном режиме и игнорируются при их задании. С другой стороны, чтобы облегчить клиентам MySQL использование столбцов TIMESTAMP, сервер сообщает, что таким столбцам могут быть назначены величины NULL (что соответствует действительности), хотя реально TIMESTAMP никогда не будет содержать величины NULL. Это можно увидеть, применив DESCRIBE tbl_name для получения описания данной таблицы. Следует учитывать, что установка столбца TIMESTAMP в 0 не равнозначна установке его в NULL, поскольку 0 для TIMESTAMP является допустимой величиной.
  • Величина DEFAULT должна быть константой, она не может быть функцией или выражением. Если для данного столбца не задается никакой величины DEFAULT, то MySQL автоматически назначает ее. Если столбец может принимать NULL как допустимую величину, то по умолчанию присваивается значение NULL. Если столбец объявлен как NOT NULL, то значение по умолчанию зависит от типа столбца:
    • Для числовых типов, за исключением объявленных с атрибутом AUTO_INCREMENT, значение по умолчанию равно 0. Для столбца AUTO_INCREMENT значением по умолчанию является следующее значение в последовательности для этого столбца.
    • Для типов даты и времени, отличных от TIMESTAMP, значение по умолчанию равно соответствующей нулевой величине для данного типа. Для первого столбца TIMESTAMP в таблице значение по умолчанию представляет собой текущее значение даты и времени. See section 6.2.2 Типы данных даты и времени.
    • Для строковых типов, кроме ENUM, значением по умолчанию является пустая строка. Для ENUM значение по умолчанию равно первой перечисляемой величине (если явно не задано другое значение по умолчанию с помощью директивы DEFAULT).
    Значения по умолчанию должны быть константами. Это означает, например, что нельзя установить для столбца даты в качестве значения по умолчанию величину функции, такой как NOW() или CURRENT_DATE.
  • KEY является синонимом для INDEX.
  • В MySQL ключ UNIQUE может иметь только различающиеся значения. При попытке добавить новую строку с ключом, совпадающим с существующей строкой, возникает ошибка выполнения команды.
  • PRIMARY KEY представляет собой уникальный ключ KEY с дополнительным ограничением, что все столбцы с данным ключом должны быть определены как NOT NULL. В MySQL этот ключ называется PRIMARY (первичный). Таблица может иметь только один первичный ключ PRIMARY KEY. Если PRIMARY KEY отсутствует в таблицах, а некоторое приложение запрашивает его, то MySQL может превратить в PRIMARY KEY первый ключ UNIQUE, не имеющий ни одного столбца NULL.
  • PRIMARY KEY может быть многостолбцовым индексом. Однако нельзя создать многостолбцовый индекс, используя в определении столбца атрибут ключа PRIMARY KEY. Именно таким образом только один столбец будет отмечен как первичный. Необходимо использовать синтаксис PRIMARY KEY(index_col_name, ...).
  • Если ключ PRIMARY или UNIQUE состоит только из одного столбца и он принадлежит к числовому типу, то на него можно сослаться также как на _rowid (новшество версии 3.23.11).
  • Если индексу не назначено имя, то ему будет присвоено первое имя в index_col_name, возможно, с суффиксами (_2, _3, ...), делающими это имя уникальным. Имена индексов для таблицы можно увидеть, используя SHOW INDEX FROM tbl_name. SHOW Syntax.
  • Только таблицы типов MyISAM, InnoDB и BDB поддерживают индексы столбцов, которые могут иметь величины NULL. В других случаях, во избежание ошибки, необходимо объявлять такие столбцы как NOT NULL.
  • С помощью выражения col_name(length) можно указать индекс, для которого используется только часть столбца CHAR или VARCHAR. Это поможет сделать файл индексов намного меньше. See section 5.4.4 Индексы столбцов.
  • Индексацию столбцов BLOB и TEXT поддерживают только таблицы с типом MyISAM. Назначая индекс столбцу с типом BLOB или TEXT, всегда НЕОБХОДИМО указывать длину этого индекса: 
    CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
  • При использовании выражений ORDER BY или GROUP BY со столбцом типа TEXT или BLOB используются только первые max_sort_length байтов. See section 6.2.3.2 Типы данных BLOB и TEXT.
  • В версии MySQL 3.23.23 и более поздних можно создавать также специальные индексы FULLTEXT. Они применяются для полнотекстового поиска. Эти индексы поддерживаются только таблицами типа MyISAM и они могут быть созданы только из столбцов VARCHAR и TEXT. Индексирование всегда выполняется для всего столбца целиком, частичная индексация не поддерживается. Более подробно эта операция описана в разделе MySQL section 6.8 Полнотекстовый поиск в MySQL.
  • Выражения FOREIGN KEY, CHECK и REFERENCES фактически ничего не делают. Они введены только из соображений совместимости, чтобы облегчить перенос кода с других SQL-серверов и запускать приложения, создающие таблицы со ссылками. See section 1.9.3 Расширения MySQL к ANSI SQL92.
  • Для каждого столбца NULL требуется один дополнительный бит, при этом величина столбца округляется в большую сторону до ближайшего байта.
  • Максимальную длину записи в байтах можно вычислить следующим образом: 
    длина записи = 1
        + (сумма длин столбцов)
        + (количество столбцов с допустимым NULL + 7)/8
        + (количество столбцов с динамической длинной)
  • Опции table_options и SELECT реализованы только в версиях MySQL 3.23 и выше. Ниже представлены различные типы таблиц:
    Тип таблицы Описание
    BDB или Berkeley_db Таблицы с поддержкой транзакций и блокировкой страниц. See section 7.6 Таблицы BDB или Berkeley_DB.
    HEAP Данные для этой таблицы хранятся только в памяти. See section 7.4 Таблицы HEAP.
    ISAM Оригинальный обработчик таблиц. See section 7.3 Таблицы ISAM.
    InnoDB Таблицы с поддержкой транзакций и блокировкой строк. See section 7.5 Таблицы InnoDB.
    MERGE Набор таблиц MyISAM, используемый как одна таблица. See section 7.2 Таблицы MERGE.
    MRG_MyISAM Псевдоним для таблиц MERGE
    MyISAM Новый обработчик, обеспечивающий переносимость таблиц в бинарном виде, который заменяет ISAM. See section 7.1 Таблицы MyISAM.
    See section 7 Типы таблиц MySQL. Если задается тип таблицы, который не поддерживается данной версией, то MySQL выберет из возможных типов ближайший к указанному. Например, если задается TYPE=BDB и данный дистрибутив MySQL не поддерживает таблиц BDB, то вместо этого будет создана таблица MyISAM. Другие табличные опции используются для оптимизации характеристик таблицы. Эти опции в большинстве случаев не требуют специальной установки. Данные опции работают с таблицами всех типов, если не указано иное:
    Опция Описание
    AUTO_INCREMENT Следующая величина AUTO_INCREMENT, которую следует установить для данной таблицы (MyISAM).
    AVG_ROW_LENGTH Приближенное значение средней длины строки для данной таблицы. Имеет смысл устанавливать только для обширных таблиц с записями переменной длины.
    CHECKSUM Следует установить в 1, чтобы в MySQL поддерживалась проверка контрольной суммы для всех строк (это делает таблицы немного более медленными при обновлении, но позволяет легче находить поврежденные таблицы) (MyISAM).
    COMMENT Комментарий для данной таблицы длиной 60 символов.
    MAX_ROWS Максимальное число строк, которые планируется хранить в данной таблице.
    MIN_ROWS Минимальное число строк, которые планируется хранить в данной таблице.
    PACK_KEYS Следует установить в 1 для получения меньшего индекса. Обычно это замедляет обновление и ускоряет чтение (MyISAM, ISAM). Установка в 0 отключит уплотнение ключей. При установке в DEFAULT (MySQL 4.0) обработчик таблиц будет уплотнять только длинные столбцы CHAR/VARCHAR.
    PASSWORD Шифрует файл `.frm' с помощью пароля. Эта опция не функционирует в стандартной версии MySQL.
    DELAY_KEY_WRITE Установка в 1 задерживает операции обновления таблицы ключей, пока не закроется указанная таблица (MyISAM).
    ROW_FORMAT Определяет, каким образом должны храниться строки. В настоящее время эта опция работает только с таблицами MyISAM, которые поддерживают форматы строк DYNAMIC и FIXED. See section 7.1.2 Форматы таблиц MyISAM.
    При использовании таблиц MyISAM MySQL вычисляет выражение max_rows * avg_row_length, чтобы определить, насколько велика будет результирующая таблица. Если не задана ни одна из вышеупомянутых опций, то максимальный размер таблицы будет составлять 4Гб (или 2Гб если данная операционная система поддерживает только таблицы величиной до 2Гб). Это делается для того, чтобы, если нет реальной необходимости в больших файлах, ограничить размеры указателей, что позволит сделать индексы меньше и быстрее. Если опция PACK_KEYS не используется, то по умолчанию уплотняются только строки, но не числа. При использовании PACK_KEYS=1 числа тоже будут уплотняться. При уплотнении двоичных числовых ключей MySQL будет использовать сжатие префиксов. Это означает, что выгода от этого будет значительной только в случае большого количества одинаковых чисел. При сжатии префиксов для каждого ключа требуется один дополнительный байт, в котором указано, сколько байтов предыдущего ключа являются такими же, как и для следующего (следует учитывать, что указатель на строку хранится в порядке "старший-байт-в-начале" сразу после ключа - чтобы улучшить компрессию). Это означает, что при наличии нескольких одинаковых ключей в двух строках записи все последующие ``аналогичные'' ключи будут занимать только по 2 байта (включая указатель строки). Сравним: в обычном случае для хранения последующих ключей требуется размер_хранения_ключа + размер_указателя (обычно 4) байтов. С другой стороны, если все ключи абсолютно разные, каждый ключ будет занимать на 1 байт больше, если данный ключ не может иметь величину NULL (в этом случае уплотненный ключ будет храниться в том же байте, который используется для указания, что ключ равен NULL).
  • Если после команды CREATE указывается команда SELECT, то 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 и c. Отметим, что столбцы из команды SELECT присоединяются к таблице справа, а не перекрывают ее. Рассмотрим следующий пример: 
    mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)
    Каждая строка в таблице foo вносится в таблицу bar со своим значением из foo, при этом в новые столбцы в таблице bar записываются величины, заданные по умолчанию. Команда CREATE TABLE ... SELECT не создает автоматически каких-либо индексов. Это сделано преднамеренно, чтобы команда была настолько гибкой, насколько возможно. Чтобы иметь индексы в созданной таблице, необходимо указать их перед данной командой SELECT: 
    mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
    Если возникает ошибка при копировании данных в таблицу, то они будут автоматически удалены. Чтобы обеспечить возможность использовать для восстановления таблиц журнал обновлений/двоичный журнал, в MySQL во время выполнения команды CREATE TABLE ... SELECT не разрешены параллельные вставки.
  • Воспользовавшись опцией RAID_TYPE, можно разбить файл данных MyISAM на участки с тем, чтобы преодолеть 2Гб/4Гб лимит файловой системы под управлением ОС, не поддерживающих большие файлы. Разбиение не касается файла индексов. Следует учесть, что для файловых систем, которые поддерживают большие файлы, эта опция не рекомендуется! Для получения более высокой скорости ввода-вывода можно разместить RAID-директории на различных физических дисках. RAID_TYPE будет работать под любой операционной системой, если конфигурация MySQL выполнена с параметром --with-raid. В настоящее время для опции RAID_TYPE возможен только параметр STRIPED (1 и RAID0 являются псевдонимами для него). Если указывается RAID_TYPE=STRIPED для таблицы MyISAM, то MyISAM создаст поддиректории RAID_CHUNKS с именами `00', `01', `02' в директории базы данных. В каждой из этих директорий MyISAM создаст файл `table_name.MYD'. При записи данных в файл данных обработчик RAID установит соответствие первых RAID_CHUNKSIZE*1024 байтов первому упомянутому файлу, следующих RAID_CHUNKSIZE*1024 байтов - следующему файлу и так далее.
  • Опция UNION применяется, если необходимо использовать совокупность идентичных таблиц как одну таблицу. Она работает только с таблицами MERGE. See section 7.2 Таблицы MERGE. На данный момент для таблиц, сопоставляемых с таблицей MERGE, необходимо иметь привилегии SELECT, UPDATE и DELETE. Все сопоставляемые таблицы должны принадлежать той же базе данных, что и таблица MERGE.
  • Для внесения данных в таблицу MERGE необходимо указать с помощью INSERT_METHOD, в какую таблицу данная строка должна быть внесена. See section 7.2 Таблицы MERGE. Эта опция была введена в MySQL 4.0.0.
  • В созданной таблице ключ PRIMARY будет помещен первым, за ним все ключи UNIQUE и затем простые ключи. Это помогает оптимизатору MySQL определять приоритеты используемых ключей, а также более быстро определять сдублированные ключи UNIQUE.
  • Используя опции DATA DIRECTORY="каталог" или INDEX DIRECTORY="каталог", можно указать, где обработчик таблицы должен помещать свои табличные и индексные файлы. Следует учитывать, что указываемый параметр directory должен представлять собой полный путь к требуемому каталогу (а не относительный путь). Данные опции работают только для таблиц MyISAM в версии MySQL 4.0, если при этом не используется опция --skip-symlink. See section 5.6.1.2 Использование символических ссылок для таблиц.

Главы