Cloning tables

If you want to clone a table, there are many options.

How to do it...

1. Use the INSERT INTO SELECT statement:

mysql> CREATE TABLE employees_clone LIKE employees;
mysql> INSERT INTO employees_clone SELECT * FROM employees;

Note that if there are any generated columns, the above statement would not work. In that case, you should give full insert statement excluding the generated columns.

mysql> INSERT INTO employees_clone SELECT * FROM employees;
ERROR 3105 (HY000): The value specified for generated column 'hire_date_year' in table 'employees_clone' is not allowed.

mysql> INSERT INTO employees_clone(emp_no, birth_date, first_name, last_name, gender, hire_date) SELECT emp_no, birth_date, first_name, last_name, gender, hire_date FROM employees;
Query OK, 300024 rows affected (3.21 sec)
Records: 300024  Duplicates: 0  Warnings: 0

Note

But the preceding statement is very slow and dangerous on big tables. Remember, if the statement fails, to restore the table state, InnoDB saves...