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:
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...