CREATE TABLE command is used to initialize a new table. It works pretty well when we create a new table from scratch. However, if we want to clone already existent schema of an existent table, rewriting the declaration of each field can be painful. Fortunately, SQL comes with a LIKE operator that we can apply to the CREATE TABLE operation.
To illustrate the LIKE use in the context of a new table creation we'll start by initializing a simple table with 2 fields:
mysql> CREATE TABLE test_like (id INT(11) NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL); Query OK, 0 rows affected (0,35 sec) mysql> desc test_like; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0,00 sec)
In order to see whether CREATE LIKE copies the values or not, let's add 2 rows:
mysql> INSERT INTO test_like (id, name) VALUES (1, 'a'), (2, 'b'); Query OK, 2 rows affected (0,12 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM test_like; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ 2 rows in set (0,00 sec)
Now, let's clone the table structure into a new one with the help of LIKE operator:
mysql> CREATE TABLE test_like_copied LIKE test_like; Query OK, 0 rows affected (0,34 sec) mysql> DESC test_like_copied; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0,00 sec)
As you can see, the table was created from the schema belonging to the base table.The executed command didn't copy the data from the source table:
mysql> SELECT * FROM test_like_copied; Empty set (0,00 sec)