09/04: MySQL > Creating Tables Based on Existing Tables
Category: Study
Posted by: hajime osako
(A) CREATE TABLE ... SELECT ... creates a table and populates it from the result set of the SELECT statement.
(B) CREATE TABLE ... LIKE ... creates an empty table using the definition of another existing table.
The difference is (A) statement copies the column name and data type from the original table, but does not retain the column attribute information. Also the new table sues the default storage engine.
Some table attributes are not copied, even when issuing (B).
If the original table is a MyISAM table for which the DATA DIRECTORY or INDEX DIRECTORY table options are specified, those options are not copied to the new table.
Foreign Key definitions in the original table are not copied to the new table.
(B) CREATE TABLE ... LIKE ... creates an empty table using the definition of another existing table.
The difference is (A) statement copies the column name and data type from the original table, but does not retain the column attribute information. Also the new table sues the default storage engine.
Some table attributes are not copied, even when issuing (B).
If the original table is a MyISAM table for which the DATA DIRECTORY or INDEX DIRECTORY table options are specified, those options are not copied to the new table.
Foreign Key definitions in the original table are not copied to the new table.
