CREATE TABLE LIKE

This statement copies the definition of an existing table, without copying any data.

Synopsis

CreateTableLikeStmt

CREATE TABLE LIKE - 图1

OptTemporary

CREATE TABLE LIKE - 图2

LikeTableWithOrWithoutParen

CREATE TABLE LIKE - 图3

OnCommitOpt

CREATE TABLE LIKE - 图4

  1. CreateTableLikeStmt ::=
  2.     'CREATE' OptTemporary 'TABLE' IfNotExists TableName LikeTableWithOrWithoutParen OnCommitOpt
  4. OptTemporary ::=
  5.     ( 'TEMPORARY' | ('GLOBAL' 'TEMPORARY') )?
  7. LikeTableWithOrWithoutParen ::=
  8.     'LIKE' TableName
  9. |   '(' 'LIKE' TableName ')'
  11. OnCommitOpt ::=
  12.     ('ON' 'COMMIT' 'DELETE' 'ROWS')?

Examples

  1. mysql> CREATE TABLE t1 (a INT NOT NULL);
  2. Query OK, 0 rows affected (0.13 sec)
  4. mysql> INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
  5. Query OK, 5 rows affected (0.02 sec)
  6. Records: 5  Duplicates: 0  Warnings: 0
  8. mysql> SELECT * FROM t1;
  9. +---+
  10. | a |
  11. +---+
  12. | 1 |
  13. | 2 |
  14. | 3 |
  15. | 4 |
  16. | 5 |
  17. +---+
  18. 5 rows in set (0.00 sec)
  20. mysql> CREATE TABLE t2 LIKE t1;
  21. Query OK, 0 rows affected (0.10 sec)
  23. mysql> SELECT * FROM t2;
  24. Empty set (0.00 sec)

Pre-split region

If the table to be copied is defined with the PRE_SPLIT_REGIONS attribute, the table created using the CREATE TABLE LIKE statement inherits this attribute, and the Region on the new table will be split. For details of PRE_SPLIT_REGIONS, see CREATE TABLE Statement.

MySQL compatibility

The CREATE TABLE LIKE statement in TiDB is fully compatible with MySQL. If you find any compatibility differences, report a bug.

See also