СОЗДАНИЕ ТАБЛИЦЫ

Синтаксис

CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...) [table_options    ]... [partition_options]
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)] [table_options   ]... [partition_options]
    select_statement
CREATE [OR REPLACE] [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
   { LIKE old_table_name | (LIKE old_table_name) }
select_statement:
    [IGNORE | REPLACE] [AS] SELECT ...   (Some legal select statement)

Описание

Используйте оператор CREATE TABLE для создания таблицы с заданным именем.

В своей самой базовой форме оператор CREATE TABLE предоставляет имя таблицы, за которым следует список столбцов, индексов и ограничений. По умолчанию таблица создается в базе данных по умолчанию. Укажите базу данных с помощью db_name.tbl_name. Если вы используете кавычки для имени таблицы, вы должны использовать кавычки для имени базы данных и имени таблицы отдельно, как в `db_name`.`tbl_name`. Это особенно полезно для CREATE TABLE ... SELECT, так как это позволяет создать таблицу в базе данных, содержащей данные из других баз данных. См. Квалификаторы идентификаторов.

Если таблица с таким же именем уже существует, возвращается ошибка 1050. Используйте IF NOT EXISTS, чтобы подавить эту ошибку и вместо этого вывести сообщение. Используйте SHOW WARNINGS, чтобы увидеть сообщения.

Оператор CREATE TABLE автоматически подтверждает текущую транзакцию, за исключением использования ключевого слова TEMPORARY.

Список допустимых идентификаторов для использования в качестве имён таблиц см. в Именах идентификаторов.

Точность микросекунд может быть в диапазоне от 0 до 6. Если точность не указана, по умолчанию она считается равной 0 по соображениям обратной совместимости.

Права

Для выполнения оператора CREATE TABLE требуется право CREATE для таблицы или базы данных.

CREATE OR REPLACE

Если используется предложение OR REPLACE, а таблица уже существует, то вместо возвращения ошибки сервер удалит существующую таблицу и заменит её новой таблицей, определённой в операторе.

Этот синтаксис был первоначально добавлен для повышения надёжности репликации, если ей необходимо откатить и повторить операторы, такие как CREATE ... SELECT на репликах.

CREATE OR REPLACE TABLE table_name (a int);

в сущности эквивалентен:

DROP TABLE IF EXISTS table_name;
CREATE TABLE table_name (a int);

с следующими исключениями:

• Если table_name была заблокирована с помощью LOCK TABLES, она продолжит быть заблокированной после выполнения оператора.
• Временные таблицы удаляются только если использовалось ключевое слово TEMPORARY. (С DROP TABLE, временные таблицы предпочтительнее удалять до обычных таблиц).

Особенности использования CREATE OR REPLACE

• Таблица сначала удаляется (если она существовала), после чего выполняется CREATE. Из-за этого, если CREATE завершается ошибкой, то таблица больше не будет существовать после выполнения оператора. Если таблица использовалась с LOCK TABLES, она будет разблокирована.
• Нельзя использовать OR REPLACE совместно с IF EXISTS.
• Реплицирующие серверы по умолчанию будут использовать CREATE OR REPLACE при репликации операторов CREATE, которые не используют IF EXISTS. Это можно изменить, установив переменную slave-ddl-exec-mode в значение STRICT.

CREATE TABLE IF NOT EXISTS

Если используется предложение IF NOT EXISTS, таблица будет создана только в случае, если таблица с таким же именем ещё не существует. Если таблица уже существует, по умолчанию будет сгенерировано предупреждение.

СОЗДАНИЕ ВРЕМЕННОЙ ТАБЛИЦЫ

Используйте ключевое слово TEMPORARY для создания временной таблицы, доступной только в текущем сеансе. Временные таблицы удаляются при завершении сеанса. Имена временных таблиц специфичны для сеанса. Они не будут конфликтовать с другими временными таблицами из других сеансов, даже если у них одинаковое имя. Они будут перекрывать имена постоянных таблиц или представлений, если они идентичны. Временная таблица может иметь то же имя, что и постоянная таблица, находящаяся в той же базе данных. В этом случае их имя будет ссылаться на временную таблицу при использовании в SQL-запросах. Для создания временных таблиц необходимо иметь право CREATE TEMPORARY TABLES на базе данных. Если движок не указан, движок будет определён значением default_tmp_storage_engine.

ROCKSDB временные таблицы не могут быть созданы путём установки переменной default_tmp_storage_engine или использования CREATE TEMPORARY TABLE LIKE. До MariaDB 10.7 они могли быть указаны, но безмолвно завершались ошибкой, и вместо этого создавалась таблица MyISAM. С MariaDB 10.7 возвращается ошибка. Явное создание временной таблицы с ENGINE=ROCKSDB никогда не разрешалось.

CREATE TABLE ... LIKE

Используйте предложение LIKE вместо полного определения таблицы, чтобы создать таблицу с такой же структурой, как у другой таблицы, включая столбцы, индексы и параметры таблицы. Определения внешних ключей, а также любые параметры таблицы DATA DIRECTORY или INDEX DIRECTORY, указанные в исходной таблице, не будут созданы.

CREATE TABLE ... SELECT

Вы можете создать таблицу, содержащую данные из других таблиц, используя оператор CREATE ... SELECT. Столбцы будут созданы в таблице для каждого поля, возвращаемого запросом SELECT.

Вы также можете определить некоторые столбцы обычным способом и добавить другие столбцы из запроса SELECT. Также можно создать столбцы обычным способом и присвоить им некоторые значения с помощью запроса, это делается для принудительного задания определенного типа или других характеристик поля. Столбцы, не указанные в запросе, будут помещены перед другими. Например:

CREATE TABLE test (a INT NOT NULL, b CHAR(10)) ENGINE=MyISAM
    SELECT 5 AS b, c, d FROM another_table;

Помните, что запрос просто возвращает данные. Если вы хотите использовать те же индексы или те же атрибуты столбцов ([NOT] NULL, DEFAULT, AUTO_INCREMENT) в новой таблице, вам нужно указать их вручную. Типы и размеры не сохраняются автоматически, если возвращаемые запросом SELECT данные не требуют полного размера, и VARCHAR может быть преобразовано в CHAR. Функция CAST() может быть использована для принудительного задания определённых типов в новой таблице.

Псевдонимы (AS) учитываются, и их следует всегда использовать при указании выражения (функция, арифметическое действие и т.д.) SELECT.

Если во время выполнения запроса произойдёт ошибка, таблица вообще не будет создана.

Если новая таблица имеет первичный ключ или индексы UNIQUE, вы можете использовать ключевые слова IGNORE или REPLACE для обработки ошибок дублирования ключей во время запроса. IGNORE означает, что новые значения не должны вставляться, если в индексе существует идентичное значение. REPLACE означает, что старые значения должны быть перезаписаны.

Если столбцов в новой таблице больше, чем строк, возвращаемых запросом, столбцы, заполненные запросом, будут размещены после других столбцов. Обратите внимание, что если включена строгая проверка SQL_MODE и столбцы, не указанные в запросе, не имеют значения DEFAULT, будет выведена ошибка, и строки не будут скопированы.

Одновременные вставки не используются во время выполнения оператора CREATE ... SELECT.

Если таблица уже существует, будет возвращена ошибка, похожая на следующую:

ERROR 1050 (42S01): Table 't' already exists

Если используется предложение IF NOT EXISTS, а таблица существует, вместо ошибки будет выведено сообщение.

Для вставки строк из запроса в существующую таблицу можно использовать INSERT ... SELECT.

Определения столбцов

create_definition:
  { col_name column_definition | index_definition | period_definition | CHECK (expr) }
column_definition:
  data_type
    [NOT NULL | NULL] [DEFAULT default_value | (expression)]
    [ON UPDATE [NOW | CURRENT_TIMESTAMP] [(precision)]]
    [AUTO_INCREMENT] [ZEROFILL] [UNIQUE [KEY] | [PRIMARY] KEY]
    [INVISIBLE] [{WITH|WITHOUT} SYSTEM VERSIONING]
    [COMMENT 'string'] [REF_SYSTEM_ID = value]
    [reference_definition]
  | data_type [GENERATED ALWAYS] 
  AS { { ROW {START|END} } | { (expression) [VIRTUAL | PERSISTENT | STORED] } }
      [UNIQUE [KEY]] [COMMENT 'string']
constraint_definition:
   CONSTRAINT [constraint_name] CHECK (expression)

CREATE TABLE b(for_key INT REFERENCES a(not_key));

Каждое определение либо создаёт столбец в таблице, либо указывает индекс или ограничение на один или несколько столбцов. Подробности по созданию индексов см. в разделе Индексы ниже.

Создайте столбец, указав имя столбца и тип данных, необязательно с параметрами столбца. Полный список типов данных, разрешённых в MariaDB, см. в Типах данных.

NULL и NOT NULL

Используйте опции NULL или NOT NULL для указания, могут ли значения в столбце быть или не быть NULL соответственно. По умолчанию значения могут быть NULL. См. также Значения NULL в MariaDB.

Параметр DEFAULT

Укажите значение по умолчанию с помощью предложения DEFAULT. Если вы не укажите DEFAULT, то действуют следующие правила:

• Если столбец не определён с NOT NULL, AUTO_INCREMENT или TIMESTAMP, будет добавлено явное DEFAULT NULL. Обратите внимание, что в MySQL и в MariaDB до версии 10.1.6, для частей первичного ключа может быть добавлено явное DEFAULT если не указано NOT NULL.

Значение по умолчанию будет использовано, если вы ВСТАВИТЬ строку без указания значения для этого столбца или если вы укажете DEFAULT для этого столбца. До MariaDB 10.2.1 вы обычно не могли указать выражение или функцию для оценки во время вставки. Вместо этого вы должны были указать постоянное значение по умолчанию. Единственное исключение состоит в том, что вы можете использовать CURRENT_TIMESTAMP в качестве значения по умолчанию для столбца TIMESTAMP, чтобы использовать текущую метку времени при вставке.

CURRENT_TIMESTAMP также может быть использовано в качестве значения по умолчанию для DATETIME

Вы можете использовать большинство функций в DEFAULT. Выражения должны быть заключены в скобки. Если вы используете недетерминированную функцию в DEFAULT , то все вставки в таблицу будут реплицированы в режиме строк. Вы даже можете ссылаться на предыдущие столбцы в DEFAULT выражении (исключая AUTO_INCREMENT столбцы):

CREATE TABLE t1 (a int DEFAULT (1+1), b int DEFAULT (a+1));
CREATE TABLE t2 (a bigint primary key DEFAULT UUID_SHORT());

В DEFAULT-оператор нельзя включать сохраненные функции или подзапросы, а столбец, используемый в операторе, должен быть определен ранее в операторе.

Можно присвоить столбцам BLOB или TEXT DEFAULT значение. В версиях до MariaDB 10.2.1 назначение значения по умолчанию для этих столбцов было невозможным.

Вы также можете использовать DEFAULT (NEXT VALUE FOR sequence)

Параметр AUTO_INCREMENT столбца

Используйте AUTO_INCREMENT, чтобы создать столбец, значение которого может автоматически устанавливаться по простому счетчику. Вы можете использовать AUTO_INCREMENT только для столбца с целочисленным типом. Столбец должен быть ключом, и в таблице может быть только один AUTO_INCREMENT столбец. Если вы вставляете строку без указания значения для этого столбца (или если вы указываете 0, NULL, или DEFAULT в качестве значения), фактическое значение будет взято из счетчика, при каждой вставке счетчик увеличивается на единицу. Вы все еще можете явно вставить значение. Если вы вставите значение, которое больше текущего значения счетчика, счетчик устанавливается на основе нового значения. Столбец AUTO_INCREMENT неявным образом NOT NULL. Используйте LAST_INSERT_ID, чтобы получить AUTO_INCREMENT значение, которое было использовано последним оператором INSERT.

Параметр ZEROFILL столбца

Если для столбца указан параметр ZEROFILL с использованием числового типа данных, то значение столбца будет дополнено UNSIGNED и пробелы, используемые по умолчанию для заполнения поля, будут заменены нулями. ZEROFILL игнорируется в выражениях или в рамках UNION. ZEROFILL — это нестандартное расширение MySQL и MariaDB.

Параметр PRIMARY KEY столбца

Используйте PRIMARY KEY , чтобы сделать столбец первичным ключом. Первичный ключ — это особый вид уникального ключа. В каждой таблице может быть не более одного первичного ключа, и он неявным образом NOT NULL.

Указание столбца как уникального ключа создаёт уникальный индекс на этом столбце. См. раздел «Определения индексов» ниже для получения дополнительной информации.

Параметр UNIQUE KEY столбца

Используйте UNIQUE KEY (или просто UNIQUE ), чтобы указать, что все значения в столбце должны быть различны. Если столбец не NOT NULL, в столбце может быть несколько строк с NULL.

Указание столбца как уникального ключа создаёт уникальный индекс на этом столбце.

См. раздел «Определения индексов» ниже для получения дополнительной информации.

Параметр COMMENT столбца

Вы можете добавить комментарий к каждому столбцу, используя COMMENT оператор. Максимальная длина составляет 1024 символа. Используйте оператор SHOW FULL COLUMNS, чтобы увидеть комментарии к столбцам.

REF_SYSTEM_ID

REF_SYSTEM_ID может быть использован для указания идентификаторов пространственной системы отсчёта для столбцов с пространственными типами данных. Например:

CREATE TABLE t1(g GEOMETRY(9,4) REF_SYSTEM_ID=101);

Сгенерированные столбцы

Сгенерированный столбец — это столбец в таблице, который не может быть явно задан в конкретное значение в запросе DML. Вместо этого его значение автоматически генерируется на основе выражения. Это выражение может генерировать значение на основе значений других столбцов в таблице, или оно может генерировать значение, вызывая встроенные функции или пользовательские функции (UDFs).

Существует два типа сгенерированных столбцов:

• PERSISTENT или STORED: Значение этого типа фактически хранится в таблице.
• VIRTUAL: Значение этого типа вообще не хранится. Вместо этого значение генерируется динамически при запросе к таблице. Этот тип является по умолчанию.

Сгенерированные столбцы иногда также называют вычисляемыми столбцами или виртуальными столбцами.

Полное описание сгенерированных столбцов и их ограничений см. в Сгенерированные (виртуальные и постоянные/хранимые) столбцы.

COMPRESSED

Некоторые столбцы могут быть сжаты. См. Независимое от движка хранения сжатие столбцов.

INVISIBLE

Столбцы могут быть сделаны невидимыми и скрытыми в определённых контекстах. См. Невидимые столбцы.

Параметр WITH SYSTEM VERSIONING столбца

Столбцы могут быть явно помечены как включённые в систему версионирования. Подробнее см. Таблицы с системой версионирования.

Параметр WITHOUT SYSTEM VERSIONING столбца

Столбцы могут быть явно помечены как исключённые из системы версионирования. Подробнее см. Таблицы с системой версионирования.

Определения индексов

index_definition:
    {INDEX|KEY} [index_name] [index_type] (index_col_name,...) [index_option] ...
  {{{|}}} {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...) [index_option] ...
  {{{|}}} [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...) [index_option] ...
  {{{|}}} [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name,...) [index_option] ...
  {{{|}}} [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH | RTREE}

index_option:
    [ KEY_BLOCK_SIZE [=] value
  {{{|}}} index_type
  {{{|}}} WITH PARSER parser_name
  {{{|}}} COMMENT 'string'
  {{{|}}} CLUSTERING={YES| NO} ]
  [ IGNORED | NOT IGNORED ]

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

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

INDEX и KEY — синонимы.

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

Категории индексов

Простые индексы

Простые индексы — это обычные индексы, которые не являются уникальными и не действуют как первичный или внешний ключ. Они также не являются «специализированными» индексами FULLTEXT или SPATIAL.

См. Начало работы с индексами: Простые индексы для получения дополнительной информации.

PRIMARY KEY

Для индексов PRIMARY KEY вы можете указать имя индекса, но оно игнорируется, и имя индекса всегда PRIMARY. Начиная с MariaDB 10.3.18 и MariaDB 10.4.8, явное сообщение об ошибке выдается при указании имени. До этого имя молча игнорировалось.

См. Начало работы с индексами: Первичный ключ для получения дополнительной информации.

UNIQUE

Ключевое слово UNIQUE означает, что индекс не будет принимать дублирующиеся значения, за исключением NULL. При попытке вставить дублирующиеся значения в уникальный индекс будет выдано сообщение об ошибке.

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

См. Начало работы с индексами: Уникальный индекс для получения дополнительной информации.

FOREIGN KEY

Для индексов FOREIGN KEY необходимо предоставить определение ссылки.

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

Сначала необходимо указать имя целевой (родительской) таблицы и список столбцов, которые должны быть индексированы и значения которых должны совпадать со значениями внешнего ключа. MATCH оператор принят для повышения совместимости с другими СУБД, но не имеет смысла в MariaDB. Операторы ON DELETE и ON UPDATE указывают, что необходимо сделать, когда оператор DELETE (или REPLACE) пытается удалить сохранённую строку из родительской таблицы и когда оператор UPDATE пытается изменить значения столбцов внешнего ключа в строке родительской таблицы, соответственно. Допускаются следующие варианты:

• RESTRICT: Операция удаления/обновления не выполняется. Оператор завершается с ошибкой 1451 (SQLSTATE '2300').
• NO ACTION: Синоним RESTRICT.
• CASCADE: Операция удаления/обновления выполняется в обеих таблицах.
• SET NULL: Обновление или удаление выполняется в родительской таблице, а соответствующие поля внешнего ключа в дочерней таблице устанавливаются в NULL. (Они не должны быть определены как NOT NULL для успешного выполнения).
• SET DEFAULT: Этот параметр в настоящее время реализован только для движка хранения PBXT, который отключен по умолчанию и больше не поддерживается. Он устанавливает поля внешнего ключа дочерней таблицы в их значениях DEFAULT при обновлении или удалении записей соответствующих ключей родительской таблицы.

Если какой-либо из пунктов опушен, поведение по умолчанию для опушенного пункта — RESTRICT.

Для получения дополнительной информации см. Внешние ключи.

FULLTEXT

Используйте ключевое слово FULLTEXT для создания полнотекстовых индексов.

Для получения дополнительной информации см. Полные текстовые индексы.

SPATIAL

Используйте ключевое слово SPATIAL для создания геометрических индексов.

Для получения дополнительной информации см. ПРОСТРАНСТВЕННЫЙ ИНДЕКС.

Параметры индексов

Параметр KEY_BLOCK_SIZE для индексов

Параметр индекса KEY_BLOCK_SIZE аналогичен параметру таблицы KEY_BLOCK_SIZE.

При использовании движка хранения InnoDB, если для всей таблицы задано ненулевое значение параметра таблицы KEY_BLOCK_SIZE, то таблица будет неявно создана с параметром таблицы ROW_FORMAT, установленным в COMPRESSED. Однако этого не происходит, если вы просто задаёте параметр индекса KEY_BLOCK_SIZE для одного или нескольких индексов в таблице. Движок хранения InnoDB игнорирует параметр индекса KEY_BLOCK_SIZE. Однако оператор SHOW CREATE TABLE может всё ещё отображать его для индекса.

Дополнительную информацию о параметре индекса KEY_BLOCK_SIZE см. в параметре таблицы KEY_BLOCK_SIZE ниже.

Типы индексов

Каждый движок хранения поддерживает некоторые или все типы индексов. Подробную информацию о разрешенных типах индексов для каждого движка хранения см. в разделе Типы индексов движка хранения.

Разные типы индексов оптимизированы для разных типов операций:

• BTREE — это тип по умолчанию и обычно является лучшим выбором. Он поддерживается всеми движками хранения. Он может использоваться для сравнения значения столбца со значением с помощью операторов =, >, >=, <, <=, BETWEEN, и LIKE. BTREE также может использоваться для поиска NULL значений. Возможны запросы к префиксу индекса.
• HASH поддерживается только движком хранения MEMORY. HASH индексы могут использоваться только для сравнений =, <= и >=. Он не может быть использован для предложения ORDER BY. Запросы к префиксу индекса невозможны.
• RTREE является значением по умолчанию для индексов SPATIAL, но если движок хранения его не поддерживает, можно использовать BTREE.

Имена столбцов индексов перечислены в скобках. После каждого столбца можно указать длину префикса. Если длина не указана, весь столбец будет индексирован. ASC и DESC могут быть указаны для совместимости с другими СУБД, но не имеют смысла в MariaDB.

Параметр WITH PARSER для индексов

Параметр индекса WITH PARSER применяется только к индексам FULLTEXT и содержит имя парсера полнотекстового поиска. Парсер полнотекстового поиска должен быть установленным плагином.

Параметр COMMENT для индексов

Комментарий длиной до 1024 символов допускается с параметром индекса COMMENT.

Параметр индекса COMMENT позволяет указать комментарий с текстом, понятным пользователю, описывающим, для чего предназначен индекс. Эта информация не используется самим сервером.

Параметр CLUSTERING для индексов

Параметр индекса CLUSTERING действителен только для таблиц, использующих движок хранения TokuDB.

ИГНОРИРОВАТЬ / НЕ ИГНОРИРОВАТЬ

Периоды

period_definition:
    PERIOD FOR SYSTEM_TIME (start_column_name, end_column_name)

MariaDB поддерживает подмножество стандартного синтаксиса для периодов. В настоящее время он используется только для создания таблиц с версиями системы. Оба столбца должны быть созданы, быть типа TIMESTAMP(6) или BIGINT UNSIGNED и генерироваться как ROW START и ROW END соответственно. Подробности см. в разделе таблицы с версиями системы.

Таблица также должна содержать предложение WITH SYSTEM VERSIONING.

Выражения ограничений

Примечание: До MariaDB 10.2.1 выражения ограничений принимались в синтаксисе, но игнорировались.

MariaDB 10.2.1 представила два способа определения ограничения:

• CHECK(expression) заданный как часть определения столбца.
• CONSTRAINT [constraint_name] CHECK (expression)

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

create table t1 (a int check(a>0) ,b int check (b> 0), constraint abc check (a>b));

Если вы используете второй формат и не даёте имени ограничению, то ограничению будет присвоено автоматически сгенерированное имя. Это делается для того, чтобы вы могли позже удалить ограничение с помощью ALTER TABLE DROP constraint_name.

Все проверки выражений ограничений можно отключить, установив переменную check_constraint_checks в значение OFF. Это полезно, например, при загрузке таблицы, которая нарушает некоторые ограничения, которые вы хотите позже найти и исправить в SQL.

Для получения дополнительной информации см. ОГРАНИЧЕНИЕ.

Параметры таблиц

Для каждой создаваемой (или изменяемой) таблицы вы можете установить некоторые параметры таблицы. Общий синтаксис установки параметров:

<OPTION_NAME> = <option_value>, [<OPTION_NAME> = <option_value> ...]

Знак равенства необязателен.

Некоторые параметры поддерживаются сервером и могут использоваться для всех таблиц независимо от движка хранения; другие параметры могут быть указаны для всех движков хранения, но имеют смысл только для некоторых из них. Кроме того, движки могут расширять CREATE TABLE новыми параметрами.

Если параметр IGNORE_BAD_TABLE_OPTIONS SQL_MODE включён, неверные параметры таблицы генерируют предупреждение; в противном случае они генерируют ошибку.

table_option:    
    [STORAGE] ENGINE [=] engine_name
  | AUTO_INCREMENT [=] value
  | AVG_ROW_LENGTH [=] value
  | [DEFAULT] CHARACTER SET [=] charset_name
  | CHECKSUM [=] {0 | 1}
  | [DEFAULT] COLLATE [=] collation_name
  | COMMENT [=] 'string'
  | CONNECTION [=] 'connect_string'
  | DATA DIRECTORY [=] 'absolute path to directory'
  | DELAY_KEY_WRITE [=] {0 | 1}
  | ENCRYPTED [=] {YES | NO}
  | ENCRYPTION_KEY_ID [=] value
  | IETF_QUOTES [=] {YES | NO}
  | INDEX DIRECTORY [=] 'absolute path to directory'
  | INSERT_METHOD [=] { NO | FIRST | LAST }
  | KEY_BLOCK_SIZE [=] value
  | MAX_ROWS [=] value
  | MIN_ROWS [=] value
  | PACK_KEYS [=] {0 | 1 | DEFAULT}
  | PAGE_CHECKSUM [=] {0 | 1}
  | PAGE_COMPRESSED [=] {0 | 1}
  | PAGE_COMPRESSION_LEVEL [=] {0 .. 9}
  | PASSWORD [=] 'string'
  | ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT|PAGE}
  | SEQUENCE [=] {0|1}
  | STATS_AUTO_RECALC [=] {DEFAULT|0|1}
  | STATS_PERSISTENT [=] {DEFAULT|0|1}
  | STATS_SAMPLE_PAGES [=] {DEFAULT|value}
  | TABLESPACE tablespace_name
  | TRANSACTIONAL [=]  {0 | 1}
  | UNION [=] (tbl_name[,tbl_name]...)
  | WITH SYSTEM VERSIONING

[STORAGE] ENGINE

[STORAGE] ENGINE указывает движок хранения хранения для таблицы. Если этот параметр не используется, используется движок хранения по умолчанию. То есть, значение параметра сессии default_storage_engine, если оно задано, или значение, указанное для параметра запуска --default-storage-engine mysqld, или движок хранения по умолчанию InnoDB. Если указанный движок хранения не установлен и активен, значение по умолчанию будет использоваться, если не установлен параметр NO_ENGINE_SUBSTITUTION SQL MODE (по умолчанию). Это верно только для CREATE TABLE, а не для ALTER TABLE. Для получения списка движков хранения, которые присутствуют на вашем сервере, выполните оператор SHOW ENGINES.

AUTO_INCREMENT

AUTO_INCREMENT задаёт начальное значение для первичного ключа AUTO_INCREMENT. Это работает для таблиц MyISAM, Aria, InnoDB, MEMORY и ARCHIVE. Вы можете изменить этот параметр с помощью ALTER TABLE, но в этом случае новое значение должно быть больше наибольшего значения, которое присутствует в столбце AUTO_INCREMENT. Если движок хранения не поддерживает этот параметр, можно вставить (и затем удалить) строку, имеющую нужное значение - 1 в столбце AUTO_INCREMENT.

AVG_ROW_LENGTH

AVG_ROW_LENGTH — это средний размер строк. Он применяется только к таблицам, использующим движки хранения MyISAM и Aria, у которых параметр таблицы ROW_FORMAT установлен в формате FIXED.

MyISAM использует MAX_ROWS и AVG_ROW_LENGTH для определения максимального размера таблицы (по умолчанию: 256 ТБ или максимальный размер файла, разрешенный системой).

[DEFAULT] CHARACTER SET/CHARSET

[DEFAULT] CHARACTER SET (или [DEFAULT] CHARSET) используется для установки набора символов по умолчанию для таблицы. Это набор символов, используемый для всех столбцов, где явным образом не указан набор символов. Если этот параметр опущен или указано DEFAULT, будет использоваться набор символов по умолчанию базы данных. Подробности о настройке наборов символов см. в разделе Установка наборов символов и сортировок наборов символов .

CHECKSUM/TABLE_CHECKSUM

CHECKSUM (или TABLE_CHECKSUM) может быть установлено в 1 для поддержки живого контрольного значения для всех строк таблицы. Это замедляет операции записи, но CHECKSUM TABLE будет очень быстрым. Этот параметр поддерживается только для таблиц MyISAM и Aria.

[DEFAULT] COLLATE

[DEFAULT] COLLATE используется для установки сортировки по умолчанию для таблицы. Это сортировка, используемая для всех столбцов, где явным образом не указан набор символов. Если этот параметр опущен или указано DEFAULT, будет использована сортировка по умолчанию базы данных. Подробности о настройке сортировок см. в разделе Установка наборов символов и сортировок сортировок .

COMMENT

COMMENT — это комментарий к таблице. Максимальная длина составляет 2048 символов. Также используется для определения параметров таблицы при создании таблицы Spider.

CONNECTION

Используется для указания имени сервера или строки подключения для Spider, CONNECT, объединённой или FederatedX таблицы.

КАТАЛОГ ДАННЫХ/КАТАЛОГ ИНДЕКСОВ

Поддерживаются для MyISAM и Aria, а также DATA DIRECTORY поддерживается InnoDB, если серверная системная переменная innodb_file_per_table включена, но только в CREATE TABLE, а не в ALTER TABLE. Поэтому следует тщательно выбирать путь для таблиц InnoDB во время создания, так как его нельзя изменить без удаления и повторного создания таблицы. Эти параметры указывают пути для файлов данных и файлов индексов соответственно. Если эти параметры опущены, для хранения файлов данных и файлов индексов будет использоваться каталог базы данных. Обратите внимание, что эти параметры таблицы не работают для размеченных таблиц (вместо этого используйте параметры разбиения) или если сервер был запущен с параметром запуска --skip-symbolic-links. Чтобы избежать перезаписи старых файлов с тем же именем, которые могут присутствовать в каталогах, можно использовать параметр --keep_files_on_create (если файлы уже существуют, будет выдано сообщение об ошибке). Эти параметры игнорируются, если включён параметр NO_DIR_IN_CREATE SQL_MODE (полезно для реплицируемых серверов). Также обратите внимание, что символьные ссылки не могут использоваться для таблиц InnoDB.

DATA DIRECTORY работает, создавая символьные ссылки из места, где таблица обычно находилась (внутри datadir), в место, указанное параметром. По соображениям безопасности, чтобы избежать обхода системы привилегий, сервер не допускает символьные ссылки внутри datadir. Следовательно, DATA DIRECTORY нельзя использовать для указания расположения внутри datadir. Попытка сделать это приведёт к ошибке 1210 (HY000) Incorrect arguments to DATA DIRECTORY.

DELAY_KEY_WRITE

Поддерживается MyISAM и Aria, и может быть установлено в 1 для ускорения операций записи. В этом случае при изменении данных индексы не обновляются до закрытия таблицы. Запись изменений в файл индекса целиком может быть намного быстрее. Однако обратите внимание, что этот параметр применяется только если серверная переменная delay_key_write установлена в 'ON'. Если она 'OFF', отложенные записи индексов всегда отключены, а если 'ALL', отложенные записи индексов всегда используются, независимо от значения DELAY_KEY_WRITE.

ШИФРОВАННО

Параметр таблицы ENCRYPTED может использоваться для ручного задания состояния шифрования таблицы InnoDB. Для получения дополнительной информации см. Шифрование InnoDB.

Aria не поддерживает параметр таблицы ENCRYPTED. См. MDEV-18049.

Для получения дополнительной информации см. Шифрование данных в состоянии покоя.

ENCRYPTION_KEY_ID

Параметр таблицы ENCRYPTION_KEY_ID может использоваться для ручного задания ключа шифрования таблицы InnoDB. Для получения дополнительной информации см. Шифрование InnoDB.

Aria не поддерживает параметр таблицы ENCRYPTION_KEY_ID. См. MDEV-18049.

Для получения дополнительной информации см. Шифрование данных в состоянии покоя.

IETF_QUOTES

Для хранилища CSV параметр IETF_QUOTES, когда установлен в YES, включает IETF-совместимый анализ вложенных кавычек и запятых. Включение этого параметра для таблицы повышает совместимость с другими инструментами, использующими CSV, но не совместимо с таблицами MySQL CSV или таблицами MariaDB CSV, созданными без этого параметра. Отключено по умолчанию.

INSERT_METHOD

INSERT_METHOD используется только с таблицами MERGE. Этот параметр определяет, в какую базовую таблицу следует вставлять новые строки. Если установить его в 'NO' (что является значением по умолчанию), новые строки в таблицу добавить нельзя (но можно выполнять INSERT непосредственно над базовыми таблицами). FIRST означает, что строки вставляются в первую таблицу, а LAST означает, что строки вставляются в последнюю таблицу.

KEY_BLOCK_SIZE

KEY_BLOCK_SIZE используется для определения размера блоков ключей в байтах или килобайтах. Однако это значение является просто подсказкой, и движок хранилища может его изменить или проигнорировать. Если KEY_BLOCK_SIZE установлено в 0, будет использовано значение по умолчанию движка хранилища.

При использовании движка хранилища InnoDB, если вы задаёте ненулевое значение для параметра таблицы KEY_BLOCK_SIZE для всей таблицы, таблица будет неявно создана с параметром таблицы ROW_FORMAT, установленным в COMPRESSED.

MIN_ROWS/MAX_ROWS

MIN_ROWS и MAX_ROWS сообщают движку хранилища о том, сколько строк планируется хранить как минимум и как максимум. Эти значения не будут использоваться как реальные ограничения, но они помогут движку хранилища оптимизировать таблицу. MIN_ROWS используется только движком хранилища MEMORY для определения минимального объёма памяти, который всегда выделяется. MAX_ROWS используется для определения минимального размера индексов.

PACK_KEYS

PACK_KEYS можно использовать для определения того, будут ли сжаты индексы. Установите его в 1 для сжатия всех ключей. При значении 0 сжатие не будет использоваться. При значении DEFAULT будут сжаты только длинные строки. Несжатые ключи быстрее.

PAGE_CHECKSUM

PAGE_CHECKSUM применимо только к таблицам Aria и определяет, следует ли использовать контрольные суммы страниц для индексов и данных для дополнительной безопасности.

PAGE_COMPRESSED

PAGE_COMPRESSED используется для включения сжатия страниц InnoDB для таблиц InnoDB.

УРОВЕНЬ СЖАТИЯ СТРАНИЦ

PAGE_COMPRESSION_LEVEL используется для задания уровня сжатия для сжатия страниц InnoDB для таблиц InnoDB. Таблица также должна иметь параметр таблицы PAGE_COMPRESSED установленным в 1.

Допустимые значения для PAGE_COMPRESSION_LEVEL - от 1 (лучшая скорость) до 9 (лучшее сжатие).

ПАРОЛЬ

PASSWORD не используется.

ТИП RAID

RAID_TYPE - устаревший параметр, так как поддержка RAID отключена с MySQL 5.0.

ФОРМАТ СТРОКИ

Параметр таблицы ROW_FORMAT задаёт формат строки для файла данных. Возможные значения зависят от движка.

Поддерживаемые форматы строк MyISAM

Для MyISAM поддерживаются следующие форматы строк:

• FIXED
• DYNAMIC
• COMPRESSED

Формат строки COMPRESSED может быть задан только командой командной строки myisampack.

Для получения дополнительной информации см. Форматы хранения MyISAM.

Поддерживаемые форматы строк Aria

Для Aria поддерживаются следующие форматы строк:

• PAGE
• FIXED
• DYNAMIC.

Для получения дополнительной информации см. Форматы хранения Aria.

Поддерживаемые форматы строк InnoDB

Для InnoDB поддерживаются следующие форматы строк:

• COMPACT
• REDUNDANT
• COMPRESSED
• DYNAMIC.

Если параметр таблицы ROW_FORMAT установлен в FIXED для таблицы InnoDB, сервер вернёт либо ошибку, либо предупреждение в зависимости от значения системной переменной innodb_strict_mode. Если системная переменная innodb_strict_mode установлена в OFF, выдаётся предупреждение, и MariaDB создаст таблицу, используя формат строки по умолчанию для конкретной версии MariaDB. Если системная переменная innodb_strict_mode установлена в ON, будет выдана ошибка.

Для получения дополнительной информации см. Форматы хранения InnoDB.

Другие движки хранилища и ROW_FORMAT

Другие движки хранилища не поддерживают параметр таблицы ROW_FORMAT.

ПОСЛЕДОВАТЕЛЬНОСТЬ

Если таблица является последовательностью, то SEQUENCE будет установлено в 1.

STATS_AUTO_RECALC

STATS_AUTO_RECALC указывает, следует ли автоматически пересчитывать постоянные статистические данные (см. STATS_PERSISTENT, ниже) для таблицы InnoDB. Если установлено в 1, статистика пересчитывается, когда изменилось более 10% данных. Если установлено в 0, статистика пересчитывается только при выполнении ANALYZE TABLE. Если установлено в DEFAULT, или опущено, применяется значение, заданное системной переменной innodb_stats_auto_recalc. См. Постоянная статистика InnoDB.

STATS_PERSISTENT

STATS_PERSISTENT указывает, будут ли оставаться на диске статистические данные InnoDB, созданные с помощью ANALYZE TABLE. Его можно установить в 1 (на диске), 0 (не на диске, поведение по умолчанию до MariaDB 10) или DEFAULT (то же самое, что и опустить опцию), в этом случае будет применено значение, заданное системной переменной innodb_stats_persistent. Постоянные статистические данные, хранящиеся на диске, позволяют статистике сохраняться после перезапуска сервера и обеспечивают лучшую стабильность планов запросов. См. Постоянная статистика InnoDB.

STATS_SAMPLE_PAGES

STATS_SAMPLE_PAGES указывает, сколько страниц используется для выборки статистики индексов. Если 0 или DEFAULT, используется значение по умолчанию, значение innodb_stats_sample_pages. См. Постоянная статистика InnoDB.

TRANSACTIONAL

TRANSACTIONAL применимо только для таблиц Aria. В будущем таблицы Aria, созданные с этой опцией, будут полностью транзакционными, но в настоящее время это обеспечивает форму защиты от сбоев. Для получения дополнительной информации см. Двигатель хранения Aria.

UNION

UNION необходимо указать при создании таблицы MERGE. Эта опция содержит список таблиц MyISAM, к которым обращается новая таблица, разделенных запятыми. Список заключен в скобки. Пример: UNION = (t1,t2)

WITH SYSTEM VERSIONING

WITH SYSTEM VERSIONING используется для создания таблиц с системной версией.

Разбиение

partition_options:
    PARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list)
        | RANGE(expr)
        | LIST(expr)
        | SYSTEM_TIME [INTERVAL time_quantity time_unit] [LIMIT num] }
    [PARTITIONS num]
    [SUBPARTITION BY
        { [LINEAR] HASH(expr)
        | [LINEAR] KEY(column_list) }
      [SUBPARTITIONS num]
    ]
    [(partition_definition [, partition_definition] ...)]
partition_definition:
    PARTITION partition_name
        [VALUES {LESS THAN {(expr) | MAXVALUE} | IN (value_list)}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]
        [(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
    SUBPARTITION logical_name
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'comment_text' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]
        [NODEGROUP [=] node_group_id]

Если используется PARTITION BY предложение, таблица будет разбита. Для разделов и подразделов метод разбиения должен быть указан явно. Методы разбиения:

• [LINEAR] HASH создает хеш-ключ, который будет использоваться для чтения и записи строк. Функция разбиения может быть любым допустимым выражением SQL, которое возвращает INTEGER число. Таким образом, можно использовать метод HASH для целочисленного столбца или для функций, которые принимают целочисленные столбцы в качестве аргумента. Однако VALUES LESS THAN и VALUES IN предложения не могут использоваться с HASH. Пример:

CREATE TABLE t1 (a INT, b CHAR(5), c DATETIME)
    PARTITION BY HASH ( YEAR(c) );

[LINEAR] HASH может использоваться и для подразделений.

• [LINEAR] KEY аналогичен HASH, но индекс имеет равномерное распределение данных. Кроме того, выражение может быть только столбцом или списком столбцов. VALUES LESS THAN и VALUES IN предложения не могут использоваться с KEY.
• RANGE разбивает строки на основе диапазона значений, используя оператор VALUES LESS THAN. VALUES IN не разрешено с RANGE. Функция разбиения может быть любым допустимым выражением SQL, которое возвращает одно значение.
• LIST назначает разделы на основе столбца таблицы с ограниченным набором возможных значений. Это аналогично RANGE, но VALUES IN должно использоваться как минимум для 1 столбца, а VALUES LESS THAN запрещено.
• SYSTEM_TIME разбиение используется для таблиц с системной версией, чтобы хранить исторические данные отдельно от текущих данных.

Только HASH и KEY могут использоваться для подтаблиц, и они могут быть [LINEAR].

Можно определить до 1024 разделов и подразделов.

Число определенных разделов можно необязательно указать как PARTITION count. Это можно сделать, чтобы избежать указания всех разделов индивидуально. Но вы также можете объявить каждый отдельный раздел и дополнительно указать PARTITIONS count предложение; в этом случае число PARTITION должно быть равно count.

См. также Обзор типов разбиения.

Последовательности

CREATE TABLE также можно использовать для создания ПОСЛЕДОВАТЕЛЬНОСТИ. См. CREATE SEQUENCE и Обзор последовательностей.

Атомарные DDL

Примеры

create table if not exists test (
a bigint auto_increment primary key,
name varchar(128) charset utf8,
key name (name(32))
) engine=InnoDB default charset latin1;

Этот пример демонстрирует несколько моментов:

• Использование IF NOT EXISTS; если таблица уже существует, она не будет создана. Для клиента не будет ошибки, только предупреждение.
• Как создать PRIMARY KEY, который автоматически генерируется.
• Как указать набор символов для всей таблицы и другой для столбца.
• Как создать индекс (name), который индексирует только частично (чтобы сэкономить место).

Следующие предложения будут работать только начиная с MariaDB 10.2.1.

CREATE TABLE t1(
  a int DEFAULT (1+1),
  b int DEFAULT (a+1),
  expires DATETIME DEFAULT(NOW() + INTERVAL 1 YEAR),
  x BLOB DEFAULT USER()
);

См. также

• Имена идентификаторов
• ALTER TABLE
• DROP TABLE
• Наборы символов и наборы кодировок
• SHOW CREATE TABLE
• Двигатели хранения могут добавлять свои собственные атрибуты для столбцов, индексов и таблиц.
• Переменная slave-ddl-exec-mode.