Cloning tables
If you want to clone a table, there are many options.
How to do it...
- 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...