Lets say you have a table at your mysql. Now you want to create a new table which will have exactly the same table structure of old one. Using the LIKE keyword of mysql you can easily create you desired table.
This will create a table "new_table" which will have exactly the same structure of "old_table". Remember, the data will not copy to the "new_table". You have to write other command to do that. The command for creating the table with data will be,
This will create the "new_table" which will have the same structure "old_table", and will have all the data into it. You can filter the data with WHERE condition at SELECT query. for example,
You can copy the data using INSERT command after creating a table using LIKE keyword. The mysql query will be,
mysql> CREATE TABLE new_table LIKE old_table;This will create a table "new_table" which will have exactly the same structure of "old_table". Remember, the data will not copy to the "new_table". You have to write other command to do that. The command for creating the table with data will be,
mysql> CREATE TABLE new_table SELECT * FROM old_table;This will create the "new_table" which will have the same structure "old_table", and will have all the data into it. You can filter the data with WHERE condition at SELECT query. for example,
mysql> CREATE TABLE new_table SELECT * FROM old_table WHERE id > 10;You can copy the data using INSERT command after creating a table using LIKE keyword. The mysql query will be,
mysql> INSERT INTO new_table SELECT * FROM old_table;
Comments
Brian in NY
mysql> CREATE TABLE new_table SELECT * FROM old_table;
This will not always create a new table with the same structure as the old table. It will create a table which fits the data exactly.
For example - you have a field which is a VARCHAR(30) but the longest string in that field is only 20 chars long - the new table will be initialised as a VARCHAR(20).
The correct procedure would be to create a new table in the likeness of the old:
mysql> CREATE TABLE new_table LIKE old_table;
and then copying the data in:
mysql> INSERT INTO new_table SELECT * FROM old_table;