Справочник по MySQL - Создание таблицы в базе данных MySQL (CREATE TABLE)

Создание таблицы в базе данных производится командой CREATE TABLE.

Синтаксис:

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

tbl_name
- Задает имя таблицы, которая будет создана в текущей базе данных. Если никакая база данных на момен вызова команды CREATE TABLE не была принята текущей, то возникнет ошибка выполнения команды. Начиная с MySQL 3.22 введена возможность явно указать базу данных, в которой будет создана новая таблица, при помощи синтаксиса db_name.tbl_name.
TEMPORARY
- Этот параметр используется для создания временной таблицы с именем tbl_name в течении только текущего сценария. По окончанию выполнения сценария созданная таблица удаляется. Данная возможность появилась в MySQL 3.23 В MySQL 4.0.2 для создания временных таблиц требуются привелегии CREATE TEMPORARY TABLES.
IF NOT EXISTS
- Если указан этот параметр и производится попытка создать таблицу с дублирующим именем (т.е. таблица с таким именем в текущей БД уже есть), то таблица создана не будет и сообщение об ошибке не появиться. В противном случае таблица также создана не будет, но команда вызовет ошибку. Следует отметить, что при создании сравниваются только имена таблиц. Внутренние структуры не сравниваются.
create_definition
- Определяет внутреннюю структуру создаваемой таблицы (названия и типы полей, ключи, индексы и т.д.)
Возможные синтаксисы 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)

col_name
- Задает имя столбца в создаваемой таблице.
type

- Задает тип данных для столбца col_name.
Возможные значения параметра 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,...)

Подробнее о допустимых типах столбцов и их синтаксисе смотрите Типы данных, используемые в базе данных MySQL
[NOT NULL | NULL]
- Указывавет, может ли данных столбец содержать значение NULL или нет. Если не указано, то по умолчанию принимается NULL (т.е. может содержать NULL).
[DEFAULT default_value]
- Задает значение по умолчанию для данного столбца. При вставке новой записи в таблицу командой INSERT если значение для поля col_name явно указано не было, то устанавливается значение default_value.
[AUTO_INCREMENT]

- При вставке новой записи в таблицу поле с этим атрибутом автоматически получит числовое значение, на 1 больше самого большого значения для этого поля в текущий момент времени. Данная возможность обычно используется для генерирования уникальных идентификаторов строк. Столбец, для которого применяется атрибут AUTO_INCREMENT, должен иметь целочисленный тип. В таблице может быть только один столбец с атрибутом AUTO_INCREMENT. Так же этот столбец должен быть проиндексирован. Отсчет последовательности чисел для AUTO_INCREMENT начинается с 1. Это могут быть только положительные числа.
Следующий пример создает таблицу users с 3 полями, где первое поле - уникальный идентификатор записи, второе поле - имя пользователя, а третье поле - его возраст:

CREATE TABLE `users` ( `id` INT( 11В В ) NOT NULL AUTO_INCREMENT,В В `name` CHAR( 30В В ) NOT NULL,В В `age` SMALLINT( 6В В ) NOT NULL, PRIMARY KEY( `id` ))

Вставляем четыре новые записи:

INSERT INTO `users` ( `name` ,В В `age` )VALUES( 'Катя', 12), ( 'Лена', 18), ( 'Миша', 16), ( 'Саша', 20)

Выводим все записи таблицы users:

SELECT * FROM `users` 

Полученный результат:

+----+------+-----+
| id | name | age |
+----+------+-----+
|В В 1 | Катя |В В 12 |
|В В 2 | Лена |В В 18 |
|В В 3 | Миша |В В 16 |
|В В 4 | Саша |В В 20 |
+----+------+-----+
4 rows in set (0.00 sec)

В таблицах MyISAM и BDB существует возможность задавать параметр AUTO_INCREMENT для вторичного столбца составного ключа.
В этом случае ключем записи (его уникальным значением) будет являться значение сразу двух полей. При этом первое поле является своеобразным префиксом, а второе уже как раз уникальным числовым значением для этого префикса.
Эту особенность удобно использовать, если необходимо добавлять новые данные по группам.
Покажем эту особенность на следующем примере:
Допустим, нам необходимо записывать свои контакты в таблицу. Создаем таблицу users с четырьмя полями для записи.
Первое поле содержит тип контакта (дом, работа, учеба);
второе поле - уникальный идентификатор записи;
третье поле - имя человека;
четвертое поле - его возраст.

CREATE TABLE `users` ( `category` ENUM( 'дом',В В 'работа',В В 'учеба')NOT NULL,В В `id` MEDIUMINT NOT NULL AUTO_INCREMENT,В В `name` CHAR( 30В В ) NOT NULL,В В `age` SMALLINT( 6В В ) NOT NULL, PRIMARY KEY( `id` ,В В `category` ))

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

INSERT INTO `users` ( `category` ,В В `name` ,В В `age` )VALUES( 'дом',В В 'Оля', 26), ( 'дом',В В 'Настя', 20), ( 'работа',В В 'Артем', 26), ( 'учеба',В В 'Дима', 25), ( 'работа',В В 'Саша', 27), ( 'учеба',В В 'Миша', 25), ( 'работа',В В 'Лена', 35)

Теперь просмотрим все записи в таблице users, упорядочив их по полю category и id:

SELECT * FROM `users` GROUP BY `category` ,В В `id`

Вот что получится в результате:

+----------+----+-------+-----+
| category | id | nameВ В | age |
+----------+----+-------+-----+
| домВ В В В В В |В В 1 | ОляВ В  |В В 26 |
| домВ В В В В В |В В 2 | Настя |В В 20 |
| работаВ В  |В В 3 | Артем |В В 26 |
| работаВ В  |В В 5 | СашаВ В |В В 27 |
| работаВ В  |В В 7 | ЛенаВ В |В В 35 |
| учебаВ В В В |В В 4 | ДимаВ В |В В 25 |
| учебаВ В В В |В В 6 | МишаВ В |В В 25 |
+----------+----+-------+-----+
7 rows in set (0.00 sec)

Чтобы получить значение ID последней вставленной записи, можно воспользоваться командой MySQL

SELECTLAST_INSERT_ID()

Либо функцией API mysql_insert_id().
[PRIMARY KEY]
- Задает первичный ключ таблицы.
В таблице может быть задан только одни первичный ключ. Все значения столбца, помеченного как перичный ключ, не должны содержать значения NULL. Если при создании таблицы первичный ключ явно указан не был, а приложение его запрашивает, то БД MySQL автоматически устанавливает первый столбец с параметром UNIQUE, если во всех значениях этого столбца нигде не встречается значение NULL.
В качестве первичного ключа можно задать как один, так и несколько столбцов:

PRIMARY KEY(col_1, col_2, ...)

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

PRIMARY KEY(col_1), PRIMARY KEY(col_1, col_2)

KEY
- Является синонимом к INDEX
INDEX

- Задает поля, которые будут проиндексированы.
Индексация полей полезна для ускорения работы команды SELECT (причем ускорение порой бывает очень даже хорошее).
В качестве наглядного примера пользы от использования индексов можно взять книгу. В ней индексами будет являться оглавление. Если нам в книге надо найти определенный раздел и нет оглавления, то придется всю книгу перелистывать страница за страницей, пока нужный раздел не будет найден. А с оглавлением мы быстро можем найти любое место в книге за считанные секунды. Тоже самое и с таблицей. Если у таблицы нет индексов, то при поиске определенного значения MySQL "проходится" по всем записям таблицы и пытается найти необходимое значение. Если задать поля, которые должны быть проиндексированы, то MySQL создает специальное хранилище, в котором содержатся все значения проиндесированных полей таблицы и точное местоуказание, где это значение находится. Т.е. поиск значения происходит практически мгновенно, что несомненно сказывается на скорости выполнения скрипта.
Но за удобство и скорость работы приходится платить. В случае с индексами оплатой является увеличение базы данных примерно в два раза.
В MySQL проиндексированы могут быть поля любого типа. Для ускорения работы в полях типа CHAR и VARCHAR можно индексировать только несколько первых символов.
При задании индексов необходимо учитывать, что только у таблиц типа MyISAM, InnoDB и BDB индексируемое поле может иметь значения NULL. Чтобы небыло ошибок советуют полям, которые будут проиндексированы, всегда назначать NOT NULL.
Если параметр index_name, который задает имя индекса, не указан, то индесу присвоится имя первого индексируемого столбца.
В следующем примере создадим таблицу users с полями name и age и проиндексируем таблицу по полю name по первым 12 буквам:

CREATE TABLE `users` ( `name` CHAR( 200В В ) NOT NULL,В В `age` SMALLINT( 3В В ) , INDEX( `name` ( 12В В ) ))

Если для столбцов CHAR и VARCHAR было желательным индексирование только части столбца, то для полей типа TEXT и BLOB это является обязательным. При этом поля с типом TEXT или BLOB могут быть проиндексированы только в таблице типа MyISAM.
Получить сведения о индексах таблицы tbl_name можно выполнив следующий SQL-запрос:

SHOW INDEX FROM `tbl_name` 

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

CREATE TABLE `users` ( `name` VARCHAR( 200В В ) NOT NULL,В В `address` VARCHAR( 255В В ) NOT NULL, UNIQUE( `name` ,В В `address` ))

FULLTEXT
- Задает поля, к которым в последствии может быть применен полнотекстный поиск.
Полнотекстный поиск является средством MySQL, направленным на поиск нужной информации в базе данных и выводе результатов в соответствии с релевантностью найденных строк относительно поискового запроса.
Полнотестный поиск введен в MySQL начиная с версии 3.23.23 для таблиц типа MyISAM и только для полей типа VARCHAR и TEXT.
При индексировании полей с ключом FULLTEXT происходит индексация всего значения, а не его части (т.е. задать для индексации первые n-символов НЕЛЬЗЯ).

FOREIGN KEY и CHECK
- Введены для совместимости при переносе кода с других SQL-баз данных при запуске приложений, создающих таблицы со ссылками.
Фактически ничего не делают.
table_options

- Задает дополнительные параметры создаваемой таблицы.
Данная возможность появилась в MySQL начиная с версии 3.23.
Возможные дополнительные параметры:

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="абсолютный путь к каталогу" 

TYPE
- Задает тип создаваемой таблицы.
Возможные типы таблиц в MySQL:
BDB - Таблицы с поддержкой транзакций и блокировкой страниц.
HEAP - Данные этой таблицы храняться только в памяти.
ISAM - Оригинальный обработчик таблиц.
InnoDB - Таблицы с поддержкой транзакций и блокировкой строк.
MERGE - Набор таблиц MyISAM, используемый как одна таблица.
MRG_MYISAM - Псевдоним для MERGE.
MyISAM - Новый обработчик, обеспечивающий переносимость таблиц в бинарном виде, который заменяет ISAM.

AUTO_INCREMENT
- Устанавливает для данной таблицы следующее значение AUTO_INCREMENT.
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.
RAID_TYPE

- Воспользовавшись опцией 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

- Опция UNION применяется, если необходимо использовать совокупность идентичных таблиц как одну таблицу. Она работает только с таблицами MERGE. На данный момент для таблиц, сопоставляемых с таблицей MERGE, необходимо иметь привилегии SELECT, UPDATE и DELETE. Все сопоставляемые таблицы должны принадлежать той же базе данных, что и таблица MERGE.
INSERT_METHOD

- Для внесения данных в таблицу MERGE необходимо указать с помощью INSERT_METHOD, в какую таблицу данная строка должна быть внесена. Эта опция была введена в MySQL 4.0.0.
DATA DIRECTORY и INDEX DIRECTORY

- Используя опции DATA DIRECTORY="каталог" или INDEX DIRECTORY="каталог", можно указать, где обработчик таблицы должен помещать свои табличные и индексные файлы. Следует учитывать, что указываемый параметр directory должен представлять собой полный путь к требуемому каталогу (а не относительный путь). Данные опции работают только для таблиц MyISAM в версии MySQL 4.0, если при этом не используется опция --skip-symlink.
select_statement
- Добавляет к создаваемой таблице поля и значения, полученные в результате работы команды SELECT.
Приведем наглядный пример.
Допустим, у нас есть таблица с именами городов:

CREATE TABLE `city` ( `name` CHAR( 200В В ) NOT NULL)

INSERT INTO `city` VALUES( 'Москва'), ( 'Рязань'), ( 'Луховицы'), ( 'Коломна')

и мы хотим создать таблицу с именами пользователей и названием городов где они живут:

CREATE TABLE `users` ( `id` INT( 11В В ) NOT NULL AUTO_INCREMENT,В В `name` CHAR( 200В В ) NOT NULL, PRIMARY KEY( `id` ))SELECT * FROM `city` 

Теперь таблица user имеет стобцы и содержит значения:
Приведенный пример никакой смысловой нагрузки не несет, т.к. поле name не получило никаких значений. Здесь просто показан принцып присоединения слева новых столбцов при помощи конструкции SELECT.
Еще пример:

CREATE TABLE `city_new` SELECT `id` ,В В `city_name` AS `name` FROM `users` 

SELECT * FROM `city_new` 

+----+----------+
| id | nameВ В В В  |
+----+----------+
|В В 1 | МоскваВ В  |
|В В 2 | РязаньВ В  |
|В В 3 | Луховицы |
|В В 4 | КоломнаВ В |
+----+----------+
4 rows in set (0.00 sec)

См. также

Типы данных, используемые в базе данных MySQL
Создание базы данных MySQL (CREATE DATABASE)
Удаление базы данных MySQL (DROP DATABASE)
Создание таблицы в базе данных MySQL (CREATE TABLE)
Удаление таблицы из базы данных MySQL (DROP TABLE)
Переименование таблицы (ALTER TABLE RENAME)
Вставка столбцов (ALTER TABLE ADD)
Изменение свойств столбца (ALTER TABLE CHANGE)
Удаление столбцов (ALTER TABLE DROP)
Вставка строки в таблицу INSERT
Удаление строк из таблицы (DELETE FROM)
Обновление записей в таблице(UPDATE)
Поиск записей в таблице(SELECT)