CREATE TABLE
Syntax
1039
Important
The original
CREATE TABLE
statement, including all specifications and table
options are stored by MySQL when the table is created. The information is
retained so that if you change storage engines, collations or other settings using
an
ALTER TABLE
statement, the original table options specified are retained.
This enables you to change between
InnoDB
and
MyISAM
table types even
though the row formats supported by the two engines are different.
Because the text of the original statement is retained, but due to the way
that certain values and options may be silently reconfigured (such as the
ROW_FORMAT
), the active table definition (accessible through
DESCRIBE
or with
SHOW TABLE STATUS
) and the table creation string (accessible through
SHOW
CREATE TABLE
) will report different values.
Cloning or Copying a Table
You can create one table from another by adding a
SELECT
statement at the end of the
CREATE
TABLE
statement:
CREATE TABLE
new_tbl
SELECT * FROM
orig_tbl
;
For more information, see
Section 13.1.10.1, “
CREATE TABLE ... SELECT
Syntax”
.
Use
LIKE
to create an empty table based on the definition of another table, including any column
attributes and indexes defined in the original table:
CREATE TABLE
new_tbl
LIKE
orig_tbl
;
The copy is created using the same version of the table storage format as the original table. The
SELECT
[578]
privilege is required on the original table.
LIKE
works only for base tables, not for views.
CREATE TABLE ... LIKE
does not preserve any
DATA DIRECTORY
or
INDEX DIRECTORY
table
options that were specified for the original table, or any foreign key definitions.
If the original table is a
TEMPORARY
table,
CREATE TABLE ... LIKE
does not preserve
TEMPORARY
.
To create a
TEMPORARY
destination table, use
CREATE TEMPORARY TABLE ... LIKE
.
In MySQL 5.0, changes to the SQL mode do not affect
CREATE TABLE ... LIKE
. If the current SQL
mode is different from the mode in effect when the original table was created, the statement succeeds
even if the table definition is invalid for the new mode.
13.1.10.1.
CREATE TABLE ... SELECT
Syntax
You can create one table from another by adding a
SELECT
statement at the end of the
CREATE
TABLE
statement:
CREATE TABLE
new_tbl
SELECT * FROM
orig_tbl
;
MySQL creates new columns for all elements in the
SELECT
. For example:
mysql>
CREATE TABLE test (a INT NOT NULL AUTO_INCREMENT,
->
PRIMARY KEY (a), KEY(b))
->
ENGINE=MyISAM SELECT b,c FROM test2;
This creates a
MyISAM
table with three columns,
a
,
b
, and
c
. The
ENGINE
option is part of the
CREATE
TABLE
statement, and should not be used following the
SELECT
; this would result in a syntax error.
The same is true for other
CREATE TABLE
options such as
CHARSET
.
Notice that the columns from the
SELECT
statement are appended to the right side of the table, not
overlapped onto it. Take the following example:
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
