There are two methods to duplicate a table in MySQL. Here's how:
1. Duplicate Table Structure and Data
In the first method, you can duplicate a table's structure and data using CREATE TABLE table_a SELECT * FROM table.
For example, suppose you have a table called users:
SELECT * FROM users;
-- +----+-------------------+---------------------------------------------+
-- | id | email | password |
-- +----+-------------------+---------------------------------------------+
-- | 1 | alice@example.com | $2y$10$abcdefghijk1234567890hashedpw1 |
-- | 2 | bob@example.com | $2y$10$abcdefghijk1234567890hashedpw2 |
-- | 3 | carol@example.com | $2y$10$abcdefghijk1234567890hashedpw3 |
-- | 4 | dave@example.com | $2y$10$abcdefghijk1234567890hashedpw4 |
-- | 5 | eve@example.com | $2y$10$abcdefghijk1234567890hashedpw5 |
-- +----+-------------------+---------------------------------------------+
-- 5 rows in set (0.00 sec)
You can duplicate the table structure and data as follows:
CREATE TABLE users_backup SELECT * FROM users;
-- Query OK, 5 rows affected (0.06 sec)
-- Records: 5 Duplicates: 0 Warnings: 0
Here's the result:
SELECT * FROM users_backup;
-- +----+-------------------+---------------------------------------------+
-- | id | email | password |
-- +----+-------------------+---------------------------------------------+
-- | 1 | alice@example.com | $2y$10$abcdefghijk1234567890hashedpw1 |
-- | 2 | bob@example.com | $2y$10$abcdefghijk1234567890hashedpw2 |
-- | 3 | carol@example.com | $2y$10$abcdefghijk1234567890hashedpw3 |
-- | 4 | dave@example.com | $2y$10$abcdefghijk1234567890hashedpw4 |
-- | 5 | eve@example.com | $2y$10$abcdefghijk1234567890hashedpw5 |
-- +----+-------------------+---------------------------------------------+
-- 5 rows in set (0.00 sec)
This method duplicates the table's structure and data, but not its full definition. Therefore, constraints like primary keys, foreign keys, and indexes will not be copied.
2. Duplicate Table Structure and Definition
In the second method, you can duplicate a table's structure and definition using CREATE TABLE table_a LIKE table.
For example, suppose you have a table called users:
SELECT * FROM users;
-- +----+-------------------+---------------------------------------------+
-- | id | email | password |
-- +----+-------------------+---------------------------------------------+
-- | 1 | alice@example.com | $2y$10$abcdefghijk1234567890hashedpw1 |
-- | 2 | bob@example.com | $2y$10$abcdefghijk1234567890hashedpw2 |
-- | 3 | carol@example.com | $2y$10$abcdefghijk1234567890hashedpw3 |
-- | 4 | dave@example.com | $2y$10$abcdefghijk1234567890hashedpw4 |
-- | 5 | eve@example.com | $2y$10$abcdefghijk1234567890hashedpw5 |
-- +----+-------------------+---------------------------------------------+
-- 5 rows in set (0.00 sec)
You can duplicate the table structure and definition as follows:
CREATE TABLE users_2 LIKE users;
-- Query OK, 0 rows affected (0.05 sec)
Here's the result:
DESC users_2;
-- +----------+--------------+------+-----+---------+----------------+
-- | Field | Type | Null | Key | Default | Extra |
-- +----------+--------------+------+-----+---------+----------------+
-- | id | int unsigned | NO | PRI | NULL | auto_increment |
-- | email | varchar(255) | NO | UNI | NULL | |
-- | password | varchar(255) | YES | | NULL | |
-- +----------+--------------+------+-----+---------+----------------+
-- 3 rows in set (0.01 sec)
To duplicate the data as well, run this:
INSERT INTO users_2 SELECT * FROM users;
-- Query OK, 5 rows affected (0.01 sec)
-- Records: 5 Duplicates: 0 Warnings: 0
Here's the result:
SELECT * FROM users_2;
-- +----+-------------------+---------------------------------------------+
-- | id | email | password |
-- +----+-------------------+---------------------------------------------+
-- | 1 | alice@example.com | $2y$10$abcdefghijk1234567890hashedpw1 |
-- | 2 | bob@example.com | $2y$10$abcdefghijk1234567890hashedpw2 |
-- | 3 | carol@example.com | $2y$10$abcdefghijk1234567890hashedpw3 |
-- | 4 | dave@example.com | $2y$10$abcdefghijk1234567890hashedpw4 |
-- | 5 | eve@example.com | $2y$10$abcdefghijk1234567890hashedpw5 |
-- +----+-------------------+---------------------------------------------+
-- 5 rows in set (0.00 sec)
Top comments (0)
Some comments may only be visible to logged-in visitors. Sign in to view all comments.