How to create table in mysql

MySQL CREATE TABLE Statement

The MySQL CREATE TABLE Statement

The CREATE TABLE statement is used to create a new table in a database.

Syntax

The column parameters specify the names of the columns of the table.

The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

Tip: For an overview of the available data types, go to our complete Data Types Reference.

MySQL CREATE TABLE Example

The following example creates a table called «Persons» that contains five columns: PersonID, LastName, FirstName, Address, and City:

Example

The PersonID column is of type int and will hold an integer.

The LastName, FirstName, Address, and City columns are of type varchar and will hold characters, and the maximum length for these fields is 255 characters.

The empty «Persons» table will now look like this:

Tip: The empty «Persons» table can now be filled with data with the SQL INSERT INTO statement.

Create Table Using Another Table

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

Syntax

The following SQL creates a new table called «TestTables» (which is a copy of the «Customers» table):

MySQL CREATE TABLE

Summary: in this tutorial, we will show you how to use the MySQL CREATE TABLE statement to create a new table in the database.

MySQL CREATE TABLE syntax

The CREATE TABLE statement allows you to create a new table in a database.

The following illustrates the basic syntax of the CREATE TABLE statement:

Let’s examine the syntax in greater detail.

First, you specify the name of the table that you want to create after the CREATE TABLE keywords. The table name must be unique within a database. The IF NOT EXISTS is optional. It allows you to check if the table that you create already exists in the database. If this is the case, MySQL will ignore the whole statement and will not create any new table.

Second, you specify a list of columns of the table in the column_list section, columns are separated by commas.

Third, you can optionally specify the storage engine for the table in the ENGINE clause. You can use any storage engine such as InnoDB and MyISAM. If you don’t explicitly declare a storage engine, MySQL will use InnoDB by default.

InnoDB became the default storage engine since MySQL version 5.5. The InnoDB storage engine brings many benefits of a relational database management system such as ACID transaction, referential integrity, and crash recovery. In the previous versions, MySQL used MyISAM as the default storage engine.

The following shows the syntax for a column’s definition:

Here are the details:

After the column list, you can define table constraints such as UNIQUE, CHECK, PRIMARY KEY and FOREIGN KEY.

For example, if you want to set a column or a group of columns as the primary key, you use the following syntax:

MySQL CREATE TABLE statement examples

Let’s take some examples of creating new tables.

1) MySQL CREATE TABLE simple example

The following statement creates a new table named tasks :

The tasks table has the following columns:

The task_id is the primary key column of the tasks table. It means that the values in the task_id column will uniquely identify rows in the table.

Once you execute the CREATE TABLE statement to create the tasks table, you can view its structure by using the DESCRIBE statement:

This picture shows the database diagram of the tasks table:

2) MySQL CREATE TABLE with a foreign key primary key example

Suppose each task has a checklist or to-do list. To store checklists of tasks, you can create a new table named checklists as follows:

The table checklists has a primary key that consists of two columns. Therefore, we used a table constraint to define the primary key:

You will learn more about the foreign key constraint in the subsequent tutorial.

This picture illustrates the checklists table and its relationship with the tasks table:

In this tutorial, you have learned how to use MySQL CREATE TABLE statement to create a new table in the database.

How to Create a Table in MySQL

Home » Databases » MySQL » How to Create a Table in MySQL

MySQL is a well-known, free and open-source database application. Its high performance, ease of use and data security makes it a popular database solution.

One of the most crucial processes in MySQL is creating tables to store and organize data.

In this guide, you will learn how to create a table in MySQL and insert data, as well as different ways to query the data.

Create a Table in MySQL Shell

A MySQL table stores and organizes data in columns and rows as defined during table creation.

The general syntax for creating a table in MySQL is:

Note: [IF NOT EXISTS] verifies if there is an identical table in the database. The query will not be executed if an identical table already exists.

Step 1: Log into the MySQL Shell

1. Open a terminal window and log into the MySQL shell. Use either an existing MySQL user account or log in as root.

(Replace username\root with your username. )

2. Type the password for your account.

The mysql> prompt indicates that you are logged in the MySQL shell.

Note: If you received a MySQL ‘Command Not Found’ error when trying to log into the MySQL shell, don’t worry. Read our article to fix the MySQL ‘Command Not Found’ error.

Step 2: Create a Database

Let’s create a movies database.

1. Create a database using the CREATE statement:

2. Next, verify that the database was created by showing a list of all databases. Use the SHOW statement:

The terminal prints out a list of databases and information about the time it took to perform the query:

3. Select the database to make changes to it by using the USE statement:

Step 3: Create a Table

We’ll create a table containing information about two movies:

In the process of creating a table, you need to specify the following information:

1. Create a table using the CREATE command. Using the information from our movies example, the command is:

2. Verify that the table is created using the DESCRIBE command:

The terminal prints out information about the table:

3. Insert movie information in column order – title, genre, director, and release year. Use the INSERT command:

4. Repeat the previous step with the second movie. Use the SELECT command to display the table:

The terminal prints out the movie table:

Note: If you want to delete the table, follow our guide on how to drop a table in MySQL.

Create a Table Using a File Script

There is an option to create a MySQL table by using a script.

1. Use your preferred text editor to create a file and enter the following syntax:

2. After entering the syntax, save the file and exit the text editor.

3. Copy the file to MySQL using the following command:

The script runs automatically after copying. In our case, it creates a table with data from the movies1.sql file.

4. Log in to the MySQL shell:

5. Verify that the script ran successfully by selecting the newly created table:

Query MySQL Data

There are several options for querying data from a MySQL table. By using the SELECT and VIEW statements, you can manipulate and find data efficiently.

Display Column Data

Display column data using the SELECT command:

The output displays the selected column and the data associated with it:

Create a View

Views are SQL queries that display data based on defined parameters.

1. Create a view named minimum_release_year to display movie titles whose release year is after 1990. Use the CREATE VIEW command and define query parameters:

2. Display the view using the SELECT command:

The output displays movies released after the year 1990.

Alter a View

The ALTER VIEW MySQL statement modifies the query parameters of the previously created view. For example, we’ll modify the minimum_release_year view to display the titles of the movies whose release year is before 2018.

1. To do so, run the ALTER VIEW command with the new parameters:

2. Use the SELECT command to verify that the view has been altered:

The output now displays movies released before 2018.

Note: Use phoenixNAP Knowledge Base to find other MySQL guides such as MySQL Triggers or MySQL Date Function and more.

After reading this guide, you should know how to create a table in MySQL and crucial commands to display the data. Learn about the most important MySQL commands and how to use them in our MySQL commands cheat sheet article with a downloadable PDF.

Working with databases and tables is crucial for data organization. If you find the MySQL shell too intimidating, consider installing Workbench for a user-friendly GUI in which you can manage and create MySQL databases. If you are interested to learn more about proper organization of data tables, make sure to check what is Database Normalization. And to check the size of a table in MySQL database, read our article how to check MySQL database and table size.

Как создавать таблицы в MySQL (Create Table)

Введение

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

Ниже разберем подробнее, как реализовать этот короткий список для MySQL наиболее эффективно.

Синтаксис Create table в MySQL и создание таблиц

Поскольку наш путь в базы данных только начинается, стоит вспомнить основы. Реляционные базы данных хранят данные в таблицах, и каждая таблица содержит набор столбцов. У столбца есть название и тип данных. Команда создания таблицы должна содержать все вышеупомянутое:

table_name — имя таблицы;

column_name — имя столбца;

column_type — тип данных столбца.

Теперь разберем процесс создания таблицы детально.

Названия таблиц и столбцов

Таблицы и столбцы стоит называть осмысленно и прозрачно, чтобы было понятно, как другому разработчику, так и вам самим спустя полгода. Даже если это учебная база только для вашего пользования, рекомендуем сразу привыкать делать правильно.

Имена могут содержать символы подчеркивания для большей наглядности. Классический пример непонятных названий — table1, table2 и т. п. Использование транслита, неясных сокращений и, разумеется, наличие орфографических ошибок тоже не приветствуется. Хороший пример коротких информативных названий: Customers, Users, Orders, так как по названию таблицы должно быть очевидно, какие данные таблица будет содержать. Эта же логика применима и к названию столбцов.

Максимальная длина названия и для таблицы, и для столбцов — 64 символа.

Типы данных столбцов

Для каждого столбца таблицы будет определен тип данных. Неправильное использование типов данных увеличивает как объем занимаемой памяти, так и время выполнения запросов к таблице. Это может быть незаметно на таблицах в несколько строк, но очень существенно, если количество строк будет измеряться десятками и сотнями тысяч, и это далеко не предел для рабочей базы данных. Проведем краткий обзор наиболее часто используемых типов:

Числовые типы

Символьные

Дата и время

Бинарные

Используются для хранения файлов, фото, документов, аудио и видеоконтента. Все это хранится в бинарном виде.

Подробный разбор типов данных, включая более специализированные типы, например, ENUM, SET или BIGINT UNSIGNED, будет в отдельной тематической статье.

Практика с примерами

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

Синтаксис create table с основными параметрами:

Тут могут появиться вопросы. Откуда MySQL знает, что номер уникален? Если еще нет должности для этого сотрудника, что будет, если оставить поле пустым?
Все это (как и многое другое) придtтся указать с помощью дополнительных параметров — атрибутов.

Часто таблицы создаются и заполняются скриптами. Если мы вызовем команду CREATE TABLE Staff, а таблица Staff уже есть в базе, команда выдаст ошибку. Поэтому перед созданием разумно проверить, содержит ли уже база таблицу Staff. Достаточно добавить IF NOT EXISTS, чтобы выполнить эту проверку в MySQL, то есть вместо

Повторный запуск команды выведет предупреждение:

Если таблица уже создана и нужно создать таблицу с тем же именем с «чистого листа», старую таблицу можно удалить командой:

Возможности SQL в «Облачных базах данных»

Атрибуты (ATTRIBUTES) и ограничения (CONSTRAINTS)

PRIMARY KEY

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

Пользы индексов на примерах: для поиска уникального значения среди 10000 строк придется проверить, в худшем случае, все 10000 без индекса, с индексом — всего 14. Поиск по миллиону записей займет не больше в 20 проверок — это реализация идеи бинарного поиска.

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

NOT NULL

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

Изменим команду CREATE TABLE, добавив NOT NULL ограничения: таким образом, мы обозначим обязательные для заполнения столбцы (т.е. столбцы, поля в которых не могут оставаться пустыми при наличии записи в таблице):

DEFAULT

Можно указать значение по умолчанию, т.е. текст или число, которые будут сохранены, если не указано другое значение. Применяется не ко всем типам: BLOB, TEXT, GEOMETRY и JSON не поддерживают это ограничение.
Эта величина должна быть константой, функция или выражение не допустимы.

Продолжим изменять команду, установив ограничение DEFAULT для поля BOOLEAN.

Для типа данных BOOLEAN можно использовать встроенные константы FALSE и TRUE. Вместо DEFAULT(FALSE) можно указать DEFAULT(0) — эти записи эквивалентны.

AUTO_INCREMENT

Каждый раз, когда в таблицу будет добавлена запись, значение этого столбца автоматически увеличится. На всю таблицу этот атрибут применим только к одному столбцу, причем этот столбец должен быть ключом. Рекомендуется использовать для целочисленных значений. Нельзя сочетать с DEFAULT.

Теперь номер сотрудника будет автоматически последовательно увеличиваться при каждой новой записи в таблицу.

Интересно, что при CREATE TABLE MySQL не позволяет установить стартовое значение для AUTO_INCREMENT. Можно назначить стартовое значение для счетчика AUTO_INCREMENT уже созданной таблицы.

Первая запись после такой модификации получит >

UNIQUE

Это ограничение устанавливает, что все значения данного столбца будут уникальны в пределах таблицы, и создает индекс. Можно применять к столбцам с поддержкой NULL, но так как NULL будет считаться уникальным значением, возможна только одна NULL-запись.

CHECK

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

Синтаксис позволяет устанавливать CHECK как в описании столбца при CREATE TABLE:

так отдельно от описания столбцов:

В этих случаях название проверки будет определено автоматически. При вставке данных, не прошедших проверку, будет сообщение об ошибке Check constraint ‘staff_chk_1’ is violated. Ситуация усложняется, когда установлено несколько CHECK, поэтому рекомендуется давать понятное имя.

Воспользуемся полной командой для создания CHECK и определим не только ограничение даты рождения, но и допустимые форматы телефона через регулярное выражение.

Для добавления ограничений используем оператор CONSTRAINT, при этом, все названия уникальны, как и имена таблиц. Учитывая, что по умолчанию названия включают в себя и имя таблицы, рекомендуем придерживаться этого правила. Если используется CONSTRAINT, мы обязаны дать имя ограничению, которое вводим.

FOREIGN KEY или внешний ключ

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

Сначала указывается выражение FOREIGN KEY и набор столбцов таблицы, откуда строим FOREIGN KEY. Затем ключевое слово REFERENCES указывает на имя внешней таблицы и набор столбцов этой внешней таблицы. В конце можно добавить операторы ON DELETE и ON UPDATE, с помощью которых настраивается поведение при удалении или обновлении данных в главной таблице. Это делать не обязательно, так как предусмотрено поведение по умолчанию. Поведение по умолчанию запрещает удалять или изменять записи из внешней таблицы, если на эти записи есть ссылки по внешнему ключу.

Возможные опции для ON DELETE и ON UPDATE:

CASCADE: автоматическое удаление/изменение строк зависимой таблицы при удалении/изменении связанных строк главной таблицы.
SET NULL: при удалении/изменении связанных строк главной таблицы будет установлено значение NULL в строках зависимой таблицы. Столбец зависимой таблицы должен поддерживать установку NULL, т.е. параметр NOT NULL в этом случае устанавливать нельзя.
RESTRICT: не даёт удалить/изменить строку главной таблицы при наличии связанных строк в зависимой таблице. Если не указана иная опция, по умолчанию будет использовано NO ACTION, что, по сути, то же самое, что и RESTRICT.

Рассмотрим пример:
Для таблицы Staff было определено текстовое поле position для хранения должности.
Так как список сотрудников в компании обычно больше, чем список занимаемых должностей, есть смысл создать справочник должностей.

Поскольку из Staff мы будем ссылаться на Positions, таблица персонала Staff будет зависимой от Positions. Изменим синтаксис CREATE TABLE для таблицы Staff, чтобы должность была ссылкой на запись в таблице Positions.

При CREATE TABLE, чтобы не усложнять описание столбца, рекомендуется указывать внешний ключ и все его атрибуты после перечисления создаваемых столбцов.
Можно ли добавить внешний ключ, если таблица уже создана и в ней есть данные? Можно! Для внесения изменений в таблицу используем ALTER TABLE.

Или в развернутой форме, определяя имя ключа fk_position_id явным образом:

Главное условие в этом случае — согласованность данных. Это значит, что для всех записей внешнего ключа position_id должно найтись соответствие в целевой таблице Positions по столбцу id.

Создание таблиц на основе уже существующих, временные таблицы

Мы рассмотрели создание таблицы с «чистого листа», но есть два других способа:

Создание таблицы на основе уже существующей таблицы. Копирует структуру — количество, названия и типы столбцов, индексы, все ограничения, кроме внешних ключей. Как мы помним, внешний ключ создает индекс. При создании через LIKE индексы в новой таблице будут построены также, как и в старой, но внешние ключи не скопируются. Таблица будет создана без записей и без счетчиков AUTO_INCREMENT.

SELECT

Можно создать таблицу на основе SELECT-запроса — результат этой выборки будет записан в новую таблицу. Такая таблица не будет иметь индексов, ограничений и ключей. Все столбцы, с учетом порядка, типов данных и названий, будут взяты из запроса — поля из SELECT станут столбцами новой таблицы. При этом можно переопределить изначальные названия полей, что особенно актуально, когда в выборку попадают столбцы с одинаковыми названиями (на уровне таблицы названия столбцов всегда уникальны).

Разберем пример создания новой таблицы через SELECT, используя две таблицы в выборке — Staff и Positions. В запросе определим три поля: id, staff, position — это будут столбцы новой таблицы StaffData211015 (срез сотрудников на определённую дату). Без присвоения псевдонимов (name as staff, name as position) в выборке получилось бы два одинаковых поля name, что не позволило бы создать таблицу из-за duplicate column name ошибки.

TEMPORARY

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

Чтобы обозначить таблицу как временную, нужно добавить TEMPORARY в CREATE TABLE:

Работа с уже созданной таблицей

Когда таблица создана, работа с ней только начинается. Операторы и команды для работы с данными рассмотрены в другой статье, а сейчас посмотрим, что же можно исправить, если потребовалось внести изменения.

Переименование

Ключевая команда — RENAME.

Удаление данных

Изменение структуры таблицы

Команда ALTER TABLE включает в себя множество опций, рассмотрим основные вместе с примерами на таблице Staff.

Добавление столбцов

Добавим три столбца: электронную почту, возраст и наличие автомобиля. Так как в таблице уже есть записи, мы не можем пока что отметить эти поля как NOT NULL, по умолчанию они будут позволять хранить NULL.

Удаление столбцов

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

Значение по умолчанию

Выставим значение по умолчанию для столбца has_auto:

Изменение типа данных столбца

Для столбца name изменим тип данных:

Максимальная длина поля была увеличена. Если не указать NOT NULL явно, то поле станет NULL по умолчанию.

Установка CHECK

Добавим ограничение формата для email через регулярное выражение:

Заключение

Любой путь начинается с первых шагов. В работе с базами данных этими шагами является создание структуры таблиц. Продуманная композиция сущностей (таблиц) и связей между ними — основа проектирования любого вашего приложения от интернет-магазинов до мощных систем управления предприятиями.

How to create table in mysql

CREATE TABLE creates a table with the given name. You must have the CREATE privilege for the table.

By default, tables are created in the default database, using the InnoDB storage engine. An error occurs if the table exists, if there is no default database, or if the database does not exist.

MySQL has no limit on the number of tables. The underlying file system may have a limit on the number of files that represent tables. Individual storage engines may impose engine-specific constraints. InnoDB permits up to 4 billion tables.

For information about the physical representation of a table, see Section 13.1.17.1, “Files Created by CREATE TABLE”.

There are several aspects to the CREATE TABLE statement, described under the following topics in this section:

Table Name

Rules for permissible table names are given in Section 9.2, “Schema Object Names”.

Prevents an error from occurring if the table exists. However, there is no verification that the existing table has a structure identical to that indicated by the CREATE TABLE statement.

Temporary Tables

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only within the current session, and is dropped automatically when the session is closed. For more information, see Section 13.1.17.2, “CREATE TEMPORARY TABLE Statement”.

Table Cloning and Copying

To create one table from another, add a SELECT statement at the end of the CREATE TABLE statement:

The IGNORE and REPLACE options indicate how to handle rows that duplicate unique key values when copying a table using a SELECT statement.

Column Data Types and Attributes

There is a hard limit of 4096 columns per table, but the effective maximum may be less for a given table and depends on the factors discussed in Section 8.4.7, “Limits on Table Column Count and Row Size”.

data_type represents the data type in a column definition. For a full description of the syntax available for specifying column data types, as well as information about the properties of each type, see Chapter 11, Data Types.

Some attributes do not apply to all data types. AUTO_INCREMENT applies only to integer and floating-point types. DEFAULT does not apply to the BLOB or TEXT types.

MySQL 5.6 interprets length specifications in character column definitions in characters. Lengths for BINARY and VARBINARY are in bytes.

Only the InnoDB and MyISAM storage engines support indexing on BLOB and TEXT columns. For example:

If neither NULL nor NOT NULL is specified, the column is treated as though NULL had been specified.

Specifies a default value for a column. For more information about default value handling, including the case that a column definition includes no explicit DEFAULT value, see Section 11.5, “Data Type Default Values”.

If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled and a date-valued default is not correct according to that mode, CREATE TABLE produces a warning if strict SQL mode is not enabled and an error if strict mode is enabled. For example, with NO_ZERO_IN_DATE enabled, c1 DATE DEFAULT ‘2010-00-00’ produces a warning.

To retrieve an AUTO_INCREMENT value after inserting a row, use the LAST_INSERT_ID() SQL function or the mysql_insert_id() C API function. See Section 12.16, “Information Functions”, and mysql_insert_id().

If the NO_AUTO_VALUE_ON_ZERO SQL mode is enabled, you can store 0 in AUTO_INCREMENT columns as 0 without generating a new sequence value. See Section 5.1.10, “Server SQL Modes”.

For MyISAM tables, you can specify an AUTO_INCREMENT secondary column in a multiple-column key. See Section 3.6.9, “Using AUTO_INCREMENT”.

To make MySQL compatible with some ODBC applications, you can find the AUTO_INCREMENT value for the last inserted row with the following query:

This method requires that sql_auto_is_null variable is not set to 0. See Section 5.1.7, “Server System Variables”.

A comment for a column can be specified with the COMMENT option, up to 1024 characters long. The comment is displayed by the SHOW CREATE TABLE and SHOW FULL COLUMNS statements. It is also shown in the COLUMN_COMMENT column of the INFORMATION_SCHEMA.COLUMNS table.

In NDB Cluster, the maximum possible offset for a column defined with COLUMN_FORMAT=FIXED is 8188 bytes. For more information and possible workarounds, see Section 18.2.7.5, “Limits Associated with Database Objects in NDB Cluster”.

For NDB tables, it is possible to specify whether the column is stored on disk or in memory by using a STORAGE clause. STORAGE DISK causes the column to be stored on disk, and STORAGE MEMORY causes in-memory storage to be used. The CREATE TABLE statement used must still include a TABLESPACE clause:

Indexes and Foreign Keys

Several keywords apply to creation of indexes and foreign keys. For general background in addition to the following descriptions, see Section 13.1.13, “CREATE INDEX Statement”, and Section 13.1.17.5, “FOREIGN KEY Constraints”.

The CONSTRAINT symbol clause may be given to name a constraint. If the clause is not given, or a symbol is not included following the CONSTRAINT keyword, MySQL automatically generates a constraint name. The symbol value, if used, must be unique per schema (database), per constraint type. A duplicate symbol results in an error. See also the discussion about length limits of generated constraint identifiers at Section 9.2.1, “Identifier Length Limits”.

The SQL standard specifies that all types of constraints (primary key, unique index, foreign key, check) belong to the same namespace. In MySQL, each constraint type has its own namespace per schema. Consequently, names for each type of constraint must be unique per schema.

In InnoDB tables, keep the PRIMARY KEY short to minimize storage overhead for secondary indexes. Each secondary index entry contains a copy of the primary key columns for the corresponding row. (See Section 14.6.2.1, “Clustered and Secondary Indexes”.)

In the created table, a PRIMARY KEY is placed first, followed by all UNIQUE indexes, and then the nonunique indexes. This helps the MySQL optimizer to prioritize which index to use and also more quickly to detect duplicated UNIQUE keys.

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as described in Unique Indexes.

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements, as described in Unique Indexes.

Partitioned tables employing the InnoDB storage engine do not support foreign keys. See Section 19.6, “Restrictions and Limitations on Partitioning”, for more information.

The CHECK clause is parsed but ignored by all storage engines.

Prefixes, defined by the length attribute, can be up to 767 bytes long for InnoDB tables or 3072 bytes if the innodb_large_prefix option is enabled. For MyISAM tables, the prefix length limit is 1000 bytes.

The preferred position for USING is after the index column list. It can be given before the column list, but support for use of the option in that position is deprecated; expect it to be removed in a future MySQL release.

index_option values specify additional options for an index.

For MyISAM tables, KEY_BLOCK_SIZE optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. A KEY_BLOCK_SIZE value specified for an individual index definition overrides the table-level KEY_BLOCK_SIZE value.

For information about the table-level KEY_BLOCK_SIZE attribute, see Table Options.

A WITH PARSER clause can be specified as an index_option value to associate a parser plugin with the index if full-text indexing and searching operations need special handling. This clause is valid only for FULLTEXT indexes. See The MySQL Plugin API, for details on creating plugins.

Index definitions can include an optional comment of up to 1024 characters.

For more information about permissible index_option values, see Section 13.1.13, “CREATE INDEX Statement”. For more information about indexes, see Section 8.3.1, “How MySQL Uses Indexes”.

For reference_definition syntax details and examples, see Section 13.1.17.5, “FOREIGN KEY Constraints”.

InnoDB and NDB tables support checking of foreign key constraints. The columns of the referenced table must always be explicitly named. Both ON DELETE and ON UPDATE actions on foreign keys are supported. For more detailed information and examples, see Section 13.1.17.5, “FOREIGN KEY Constraints”.

For other storage engines, MySQL Server parses and ignores the FOREIGN KEY syntax in CREATE TABLE statements.

MySQL parses but ignores “ inline REFERENCES specifications ” (as defined in the SQL standard) where the references are defined as part of the column specification. MySQL accepts REFERENCES clauses only when specified as part of a separate FOREIGN KEY specification. For more information, see Section 1.7.2.3, “FOREIGN KEY Constraint Differences”.

Table Options

Table options are used to optimize the behavior of the table. In most cases, you do not have to specify any of them. These options apply to all storage engines unless otherwise indicated. Options that do not apply to a given storage engine may be accepted and remembered as part of the table definition. Such options then apply if you later use ALTER TABLE to convert the table to use a different storage engine.

Specifies the storage engine for the table, using one of the names shown in the following table. The engine name can be unquoted or quoted. The quoted name ‘DEFAULT’ is recognized but ignored.

Engine substitution can be controlled by the setting of the NO_ENGINE_SUBSTITUTION SQL mode, as described in Section 5.1.10, “Server SQL Modes”.

For engines that support the AUTO_INCREMENT table option in CREATE TABLE statements, you can also use ALTER TABLE tbl_name AUTO_INCREMENT = N to reset the AUTO_INCREMENT value. The value cannot be set lower than the maximum value currently in the column.

An approximation of the average row length for your table. You need to set this only for large tables with variable-size rows.

When you create a MyISAM table, MySQL uses the product of the MAX_ROWS and AVG_ROW_LENGTH options to decide how big the resulting table is. If you don’t specify either option, the maximum size for MyISAM data and index files is 256TB by default. (If your operating system does not support files that large, table sizes are constrained by the file size limit.) If you want to keep down the pointer sizes to make the index smaller and faster and you don’t really need big files, you can decrease the default pointer size by setting the myisam_data_pointer_size system variable. (See Section 5.1.7, “Server System Variables”.) If you want all your tables to be able to grow above the default limit and are willing to have your tables slightly slower and larger than necessary, you can increase the default pointer size by setting this variable. Setting the value to 7 permits table sizes up to 65,536TB.

[DEFAULT] CHARACTER SET

Set this to 1 if you want MySQL to maintain a live checksum for all rows (that is, a checksum that MySQL updates automatically as the table changes). This makes the table a little slower to update, but also makes it easier to find corrupted tables. The CHECKSUM TABLE statement reports the checksum. ( MyISAM only.)

Specifies a default collation for the table.

A comment for the table, up to 2048 characters long.

The connection string for a FEDERATED table.

Older versions of MySQL used a COMMENT option for the connection string.

When creating MyISAM tables, you can use the DATA DIRECTORY=’ directory ‘ clause, the INDEX DIRECTORY=’ directory ‘ clause, or both. They specify where to put a MyISAM table’s data file and index file, respectively. Unlike InnoDB tables, MySQL does not create subdirectories that correspond to the database name when creating a MyISAM table with a DATA DIRECTORY or INDEX DIRECTORY option. Files are created in the directory that is specified.

As of MySQL 5.6.35, you must have the FILE privilege to use the DATA DIRECTORY or INDEX DIRECTORY table option.

Table-level DATA DIRECTORY and INDEX DIRECTORY options are ignored for partitioned tables. (Bug #32091)

Set this to 1 if you want to delay key updates for the table until the table is closed. See the description of the delay_key_write system variable in Section 5.1.7, “Server System Variables”. ( MyISAM only.)

If you want to insert data into a MERGE table, you must specify with INSERT_METHOD the table into which the row should be inserted. INSERT_METHOD is an option useful for MERGE tables only. Use a value of FIRST or LAST to have inserts go to the first or last table, or a value of NO to prevent inserts. See Section 15.7, “The MERGE Storage Engine”.

For MyISAM tables, KEY_BLOCK_SIZE optionally specifies the size in bytes to use for index key blocks. The value is treated as a hint; a different size could be used if necessary. A KEY_BLOCK_SIZE value specified for an individual index definition overrides the table-level KEY_BLOCK_SIZE value.

Oracle recommends enabling innodb_strict_mode when specifying KEY_BLOCK_SIZE for InnoDB tables. When innodb_strict_mode is enabled, specifying an invalid KEY_BLOCK_SIZE value returns an error. If innodb_strict_mode is disabled, an invalid KEY_BLOCK_SIZE value results in a warning, and the KEY_BLOCK_SIZE option is ignored.

InnoDB only supports KEY_BLOCK_SIZE at the table level.

The maximum number of rows you plan to store in the table. This is not a hard limit, but rather a hint to the storage engine that the table must be able to store at least this many rows.

The maximum MAX_ROWS value is 4294967295; larger values are truncated to this limit.

The minimum number of rows you plan to store in the table. The MEMORY storage engine uses this option as a hint about memory use.

When packing binary number keys, MySQL uses prefix compression:

Every key needs one extra byte to indicate how many bytes of the previous key are the same for the next key.

The pointer to the row is stored in high-byte-first order directly after the key, to improve compression.

Defines the physical format in which the rows are stored.

Row format choices differ depending on the storage engine used for the table.

For InnoDB tables:

Rows are stored in compact format ( ROW_FORMAT=COMPACT ) by default.

To enable compression for InnoDB tables, specify ROW_FORMAT=COMPRESSED and follow the procedures in Section 14.9, “InnoDB Table Compression”.

When you specify a non-default ROW_FORMAT clause, consider also enabling the innodb_strict_mode configuration option.

For additional information about InnoDB row formats, see Section 14.11, “InnoDB Row Formats”.

Specifies whether to automatically recalculate persistent statistics for an InnoDB table. The value DEFAULT causes the persistent statistics setting for the table to be determined by the innodb_stats_auto_recalc configuration option. The value 1 causes statistics to be recalculated when 10% of the data in the table has changed. The value 0 prevents automatic recalculation for this table; with this setting, issue an ANALYZE TABLE statement to recalculate the statistics after making substantial changes to the table. For more information about the persistent statistics feature, see Section 14.8.11.1, “Configuring Persistent Optimizer Statistics Parameters”.

Specifies whether to enable persistent statistics for an InnoDB table. The value DEFAULT causes the persistent statistics setting for the table to be determined by the innodb_stats_persistent configuration option. The value 1 enables persistent statistics for the table, while the value 0 turns off this feature. After enabling persistent statistics through a CREATE TABLE or ALTER TABLE statement, issue an ANALYZE TABLE statement to calculate the statistics, after loading representative data into the table. For more information about the persistent statistics feature, see Section 14.8.11.1, “Configuring Persistent Optimizer Statistics Parameters”.

A STORAGE clause cannot be used in a CREATE TABLE statement without a TABLESPACE clause.

Used to access a collection of identical MyISAM tables as one. This works only with MERGE tables. See Section 15.7, “The MERGE Storage Engine”.

Formerly, all tables used had to be in the same database as the MERGE table itself. This restriction no longer applies.

Table Partitioning

Not all options shown in the syntax for partition_options at the beginning of this section are available for all partitioning types. Please see the listings for the following individual types for information specific to each type, and see Chapter 19, Partitioning, for more complete information about the workings of and uses for partitioning in MySQL, as well as additional examples of table creation and other statements relating to MySQL partitioning.

Partitions can be modified, merged, added to tables, and dropped from tables. For basic information about the MySQL statements to accomplish these tasks, see Section 13.1.7, “ALTER TABLE Statement”. For more detailed descriptions and examples, see Section 19.3, “Partition Management”.

The expression ( expr ) used in a PARTITION BY clause cannot refer to any columns not in the table being created; such references are specifically not permitted and cause the statement to fail with an error. (Bug #29444)

Hashes one or more columns to create a key for placing and locating rows. expr is an expression using one or more table columns. This can be any valid MySQL expression (including MySQL functions) that yields a single integer value. For example, these are both valid CREATE TABLE statements using PARTITION BY HASH :

PARTITION BY HASH uses the remainder of expr divided by the number of partitions (that is, the modulus). For examples and additional information, see Section 19.2.4, “HASH Partitioning”.

The LINEAR keyword entails a somewhat different algorithm. In this case, the number of the partition in which a row is stored is calculated as the result of one or more logical AND operations. For discussion and examples of linear hashing, see Section 19.2.4.1, “LINEAR HASH Partitioning”.

This causes MySQL 5.6.10 and earlier servers to ignore the option, which would otherwise cause a syntax error in those versions. If you plan to load a dump made on a MySQL 5.5.31 or later MySQL 5.5 server where you use tables that are partitioned or subpartitioned by KEY into a MySQL 5.6 server previous to version 5.6.11, be sure to consult Section 2.11.3, “Changes in MySQL 5.6”, before proceeding. (The information found there also applies if you are loading a dump containing KEY partitioned or subpartitioned tables made from a MySQL 5.6.11 or later server into a MySQL 5.5.30 or earlier server.)

Suppose that you have a table that you wish to partition on a column containing year values, according to the following scheme.

A table implementing such a partitioning scheme can be realized by the CREATE TABLE statement shown here:

RANGE COLUMNS( column_list )

Each value used in a VALUES LESS THAN value list must match the type of the corresponding column exactly; no conversion is made. For example, you cannot use the string ‘1’ for a value that matches a column that uses an integer type (you must use the numeral 1 instead), nor can you use the numeral 1 for a value that matches a column that uses a string type (in such a case, you must use a quoted string: ‘1’ ).

VALUES IN is used with a list of values to be matched. For instance, you could create a partitioning scheme such as the following:

LIST COLUMNS( column_list )

The table defined by the following CREATE TABLE statement provides an example of a table using LIST COLUMNS partitioning:

The number of partitions may optionally be specified with a PARTITIONS num clause, where num is the number of partitions. If both this clause and any PARTITION clauses are used, num must be equal to the total number of any partitions that are declared using PARTITION clauses.

The number of subpartitions can be indicated using the SUBPARTITIONS keyword followed by an integer value.

Rigorous checking of the value used in PARTITIONS or SUBPARTITIONS clauses is applied and this value must adhere to the following rules:

The value must be a positive, nonzero integer.

No leading zeros are permitted.

Each partition may be individually defined using a partition_definition clause. The individual parts making up this clause are as follows:

Specifies a logical name for the partition.

For range partitioning, each partition must include a VALUES LESS THAN clause; for list partitioning, you must specify a VALUES IN clause for each partition. This is used to determine which rows are to be stored in this partition. See the discussions of partitioning types in Chapter 19, Partitioning, for syntax examples.

An optional COMMENT clause may be used to specify a string that describes the partition. Example:

The maximum length for a partition comment is 1024 characters.

DATA DIRECTORY and INDEX DIRECTORY

DATA DIRECTORY and INDEX DIRECTORY may be used to indicate the directory where, respectively, the data and indexes for this partition are to be stored. Both the data_dir and the index_dir must be absolute system path names.

As of MySQL 5.6.35, you must have the FILE privilege to use the DATA DIRECTORY or INDEX DIRECTORY partition option.

DATA DIRECTORY and INDEX DIRECTORY behave in the same way as in the CREATE TABLE statement’s table_option clause as used for MyISAM tables.

One data directory and one index directory may be specified per partition. If left unspecified, the data and indexes are stored by default in the table’s database directory.

On Windows, the DATA DIRECTORY and INDEX DIRECTORY options are not supported for individual partitions or subpartitions of MyISAM tables, and the INDEX DIRECTORY option is not supported for individual partitions or subpartitions of InnoDB tables. These options are ignored on Windows, except that a warning is generated. (Bug #30459)

The DATA DIRECTORY and INDEX DIRECTORY options are ignored for creating partitioned tables if NO_DIR_IN_CREATE is in effect. (Bug #24633)

MAX_ROWS and MIN_ROWS

May be used to specify, respectively, the maximum and minimum number of rows to be stored in the partition. The values for max_number_of_rows and min_number_of_rows must be positive integers. As with the table-level options with the same names, these act only as “ suggestions ” to the server and are not hard limits.

May be used to designate a tablespace for the partition. Used for NDB Cluster only.