MySQL Copy Table: CREATE TABLE LIKE and AS SELECT

Copying a table is common: you want a backup before a risky change, a test copy, or a working table for a report. Two statements do the job, and they copy different things.

query.sql

SQL

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_name (name)
);
INSERT INTO products (name, price) VALUES ('Notebook', 80), ('Pen', 15);

CREATE TABLE … LIKE: the structure

query.sql

SQL

CREATE TABLE products_copy LIKE products;
INSERT INTO products_copy SELECT * FROM products;
SELECT * FROM products_copy;
SHOW CREATE TABLE products_copy\G

Output

OUTPUT

+----+----------+-------+
| id | name     | price |
+----+----------+-------+
|  1 | Notebook | 80.00 |
|  2 | Pen      | 15.00 |
+----+----------+-------+
2 rows in set

*************************** 1. row ***************************
       Table: products_copy
Create Table: CREATE TABLE `products_copy` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `price` decimal(8,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set

LIKE copies columns, defaults, indexes and keys, but no rows. A second statement, INSERT ... SELECT, copies the data. This is the best way to make an exact copy.

CREATE TABLE … AS SELECT: the result of a query

query.sql

SQL

1
2
3

CREATE TABLE cheap_products AS
SELECT id, name, price FROM products WHERE price < 50;
SHOW CREATE TABLE cheap_products\G

Output

OUTPUT

*************************** 1. row ***************************
       Table: cheap_products
Create Table: CREATE TABLE `cheap_products` (
  `id` int NOT NULL DEFAULT '0',
  `name` varchar(50) NOT NULL,
  `price` decimal(8,2) NOT NULL DEFAULT '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set

AS SELECT creates a table shaped like the query result and fills it. It does not copy indexes, keys or AUTO_INCREMENT. The id column lost its PRIMARY KEY. Define the columns yourself if you need rules:

query.sql

SQL

CREATE TABLE cheap_products2 (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(8,2) NOT NULL
) AS SELECT id, name, price FROM products WHERE price < 50;
SELECT * FROM cheap_products2;

Output

OUTPUT

+----+------+-------+
| id | name | price |
+----+------+-------+
|  2 | Pen  | 15.00 |
+----+------+-------+
1 row in set

Which one

Goal	Statement
Exact copy of a table including indexes	`CREATE TABLE b LIKE a;` then `INSERT INTO b SELECT * FROM a;`
Table from a query result, no keys needed	`CREATE TABLE b AS SELECT ...;`
Table from a query result, with keys	Define the columns, then `AS SELECT` or `INSERT ... SELECT`
A snapshot to compare before and after	`LIKE` + `INSERT ... SELECT`

Cautions

LIKE does not copy foreign keys. Add them to the copy if you need them.
A copy of a big table doubles the disk use for a while and can take time. In production, do it off-peak.
Triggers are not copied.
AS SELECT with many rows holds a lock on the source rows while it reads. Under heavy traffic, copy in batches.

lightbulb

Before a risky change

CREATE TABLE orders_backup_20260715 LIKE orders; INSERT INTO orders_backup_20260715 SELECT * FROM orders; takes one minute and can save a night. Delete it once you are sure.