Вы можете создать одну таблицу из другой, добавив оператор SELECT в конце оператора CREATE TABLE:

CREATE TABLE new_tbl [AS] SELECT * FROM orig_tbl;

MySQL создаёт новые столбцы для всех элементов в SELECT. Например:

mysql> CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
    ->        PRIMARY KEY (a), KEY(b))
    ->        ENGINE=InnoDB SELECT b,c FROM test2;

Это создаёт таблицу InnoDB с тремя столбцами, a, b и c. Опция ENGINE является частью оператора CREATE TABLE и не должна использоваться после оператора SELECT; это приведёт к синтаксической ошибке. То же самое справедливо для других опций оператора CREATE TABLE, таких как CHARSET.

Обратите внимание, что столбцы из оператора SELECT добавляются справа к таблице, а не накладываются на неё. Рассмотрим следующий пример:

mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+

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

mysql> SELECT * FROM bar;
+------+---+
| m    | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)

Для каждой строки в таблице foo вставляется строка в bar со значениями из foo и значениями по умолчанию для новых столбцов.

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

Для систем хранения данных, которые поддерживают атомарные DDL и ограничения внешних ключей, создание внешних ключей не разрешено в операторах CREATE TABLE ... SELECT при использовании репликации на основе строк. Ограничения внешних ключей можно добавить позже с помощью ALTER TABLE.

Перед оператором SELECT можно добавить IGNORE или REPLACE, чтобы указать, как обрабатывать строки с дублирующими значениями уникального ключа. С IGNORE строки, дублирующие существующую строку по значению уникального ключа, отбрасываются. С REPLACE новые строки заменяют строки с тем же значением уникального ключа. Если ни IGNORE, ни REPLACE не указаны, дублирующиеся значения уникального ключа приводят к ошибке. Дополнительную информацию см. в Влиянии IGNORE на выполнение запроса.

Вы также можете использовать оператор VALUES в части SELECT оператора CREATE TABLE ... SELECT; часть VALUES оператора должна включать псевдоним таблицы с помощью предложения AS. Для именования столбцов, полученных из VALUES, укажите псевдонимы столбцов с псевдонимом таблицы; в противном случае используются имена столбцов по умолчанию column_0, column_1, column_2, ...

В противном случае, именование столбцов в создаваемой таблице подчиняется тем же правилам, что и описано ранее в этом разделе. Примеры:

mysql> CREATE TABLE tv1
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v;
mysql> TABLE tv1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        3 |        5 |
|        2 |        4 |        6 |
+----------+----------+----------+

mysql> CREATE TABLE tv2
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
mysql> TABLE tv2;
+---+---+---+
| x | y | z |
+---+---+---+
| 1 | 3 | 5 |
| 2 | 4 | 6 |
+---+---+---+

mysql> CREATE TABLE tv3 (a INT, b INT, c INT)
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
mysql> TABLE tv3;
+------+------+------+----------+----------+----------+
| a    | b    | c    |        x |        y |        z |
+------+------+------+----------+----------+----------+
| NULL | NULL | NULL |        1 |        3 |        5 |
| NULL | NULL | NULL |        2 |        4 |        6 |
+------+------+------+----------+----------+----------+

mysql> CREATE TABLE tv4 (a INT, b INT, c INT)
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);
mysql> TABLE tv4;
+------+------+------+---+---+---+
| a    | b    | c    | x | y | z |
+------+------+------+---+---+---+
| NULL | NULL | NULL | 1 | 3 | 5 |
| NULL | NULL | NULL | 2 | 4 | 6 |
+------+------+------+---+---+---+

mysql> CREATE TABLE tv5 (a INT, b INT, c INT)
     >     SELECT * FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(a,b,c);
mysql> TABLE tv5;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    3 |    5 |
|    2 |    4 |    6 |
+------+------+------+

При выборе всех столбцов и использовании имен столбцов по умолчанию, вы можете опустить SELECT *, поэтому оператор для создания таблицы tv1 также может быть записан так:

mysql> CREATE TABLE tv1 VALUES ROW(1,3,5), ROW(2,4,6);
mysql> TABLE tv1;
+----------+----------+----------+
| column_0 | column_1 | column_2 |
+----------+----------+----------+
|        1 |        3 |        5 |
|        2 |        4 |        6 |
+----------+----------+----------+

При использовании VALUES в качестве источника для SELECT, всегда выбираются все столбцы в новую таблицу, и отдельные столбцы не могут быть выбраны, как при выборе из именованной таблицы; каждый из следующих операторов приводит к ошибке:

CREATE TABLE tvx
    SELECT (x,z) FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);

CREATE TABLE tvx (a INT, c INT)
    SELECT (x,z) FROM (VALUES ROW(1,3,5), ROW(2,4,6)) AS v(x,y,z);

Аналогично, вы можете использовать оператор TABLE вместо оператора SELECT. Это следует тем же правилам, что и с VALUES; все столбцы исходной таблицы и их имена в исходной таблице всегда вставляются в новую таблицу. Примеры:

mysql> TABLE t1;
+----+----+
| a  | b  |
+----+----+
|  1 |  2 |
|  6 |  7 |
| 10 | -4 |
| 14 |  6 |
+----+----+

mysql> CREATE TABLE tt1 TABLE t1;
mysql> TABLE tt1;
+----+----+
| a  | b  |
+----+----+
|  1 |  2 |
|  6 |  7 |
| 10 | -4 |
| 14 |  6 |
+----+----+

mysql> CREATE TABLE tt2 (x INT) TABLE t1;
mysql> TABLE tt2;
+------+----+----+
| x    | a  | b  |
+------+----+----+
| NULL |  1 |  2 |
| NULL |  6 |  7 |
| NULL | 10 | -4 |
| NULL | 14 |  6 |
+------+----+----+

Поскольку порядок строк в базовых операторах SELECT не всегда может быть определён, операторы CREATE TABLE ... IGNORE SELECT и CREATE TABLE ... REPLACE SELECT отмечаются как небезопасные для репликации на основе операторов. Такие операторы генерируют предупреждение в журнале ошибок при использовании режима репликации на основе операторов и записываются в двоичный журнал с использованием формата на основе строк при использовании режима MIXED. См. также Раздел 19.2.1.1, «Преимущества и недостатки репликации на основе операторов и на основе строк».

Оператор CREATE TABLE ... SELECT не создаёт автоматически индексы. Это сделано намеренно, чтобы сделать оператор максимально гибким. Если вы хотите иметь индексы в создаваемой таблице, вы должны указать их перед оператором SELECT:

mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;

Для CREATE TABLE ... SELECT целевая таблица не сохраняет информацию о том, являются ли столбцы в таблице, из которой выбираются данные, генерируемыми столбцами. Часть SELECT оператора не может назначать значения генерируемым столбцам в целевой таблице.

Для CREATE TABLE ... SELECT целевая таблица сохраняет значения по умолчанию выражений из исходной таблицы.

Возможно, произойдёт преобразование типов данных. Например, атрибут AUTO_INCREMENT не сохраняется, а столбцы VARCHAR могут стать столбцами CHAR. Сохраняемые атрибуты — NULL (или NOT NULL) и, для тех столбцов, которые их имеют, CHARACTER SET, COLLATION, COMMENT и предложение DEFAULT.

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

CREATE TABLE artists_and_works
  SELECT artist.name, COUNT(work.artist_id) AS number_of_works
  FROM artist LEFT JOIN work ON artist.id = work.artist_id
  GROUP BY artist.id;

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

CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;

Для CREATE TABLE ... SELECT, если IF NOT EXISTS задан и целевая таблица существует, ничего не вставляется в целевую таблицу, и оператор не регистрируется.

Чтобы обеспечить возможность использования двоичного журнала для восстановления исходных таблиц, MySQL не допускает одновременных вставок во время CREATE TABLE ... SELECT. Дополнительную информацию см. в Разделе 15.1.1, «Поддержка атомарных операторов определения данных».

Вы не можете использовать FOR UPDATE как часть SELECT в операторе, таком как CREATE TABLE new_table SELECT ... FROM old_table .... Если вы попытаетесь это сделать, оператор завершится ошибкой.

Операции CREATE TABLE ... SELECT применяют значения ENGINE_ATTRIBUTE и SECONDARY_ENGINE_ATTRIBUTE только к столбцам. Значения таблицы и индекса ENGINE_ATTRIBUTE и SECONDARY_ENGINE_ATTRIBUTE не применяются к новой таблице, если не указаны явно.