Вы можете создать одну таблицу из другой, добавив оператор 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 не применяются к новой таблице, если не указаны явно.