(mysql.info) create-table
Info Catalog
(mysql.info) create-index
(mysql.info) data-definition
(mysql.info) drop-database
13.1.5 `CREATE TABLE' Syntax
----------------------------
Menu
* silent-column-changes Silent Column Specification Changes
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] TBL_NAME
[(CREATE_DEFINITION,...)]
[TABLE_OPTIONS] [SELECT_STATEMENT]
Or:
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] TBL_NAME
[(] LIKE OLD_TBL_NAME [)];
CREATE_DEFINITION:
COLUMN_DEFINITION
| [CONSTRAINT [SYMBOL]] PRIMARY KEY [INDEX_TYPE] (INDEX_COL_NAME,...)
| KEY [INDEX_NAME] [INDEX_TYPE] (INDEX_COL_NAME,...)
| INDEX [INDEX_NAME] [INDEX_TYPE] (INDEX_COL_NAME,...)
| [CONSTRAINT [SYMBOL]] UNIQUE [INDEX]
[INDEX_NAME] [INDEX_TYPE] (INDEX_COL_NAME,...)
| [FULLTEXT|SPATIAL] [INDEX] [INDEX_NAME] (INDEX_COL_NAME,...)
| [CONSTRAINT [SYMBOL]] FOREIGN KEY
[INDEX_NAME] (INDEX_COL_NAME,...) [REFERENCE_DEFINITION]
| CHECK (EXPR)
COLUMN_DEFINITION:
COL_NAME TYPE [NOT NULL | NULL] [DEFAULT DEFAULT_VALUE]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'STRING'] [REFERENCE_DEFINITION]
TYPE:
TINYINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
| SMALLINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
| MEDIUMINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
| INT[(LENGTH)] [UNSIGNED] [ZEROFILL]
| INTEGER[(LENGTH)] [UNSIGNED] [ZEROFILL]
| BIGINT[(LENGTH)] [UNSIGNED] [ZEROFILL]
| REAL[(LENGTH,DECIMALS)] [UNSIGNED] [ZEROFILL]
| DOUBLE[(LENGTH,DECIMALS)] [UNSIGNED] [ZEROFILL]
| FLOAT[(LENGTH,DECIMALS)] [UNSIGNED] [ZEROFILL]
| DECIMAL(LENGTH,DECIMALS) [UNSIGNED] [ZEROFILL]
| NUMERIC(LENGTH,DECIMALS) [UNSIGNED] [ZEROFILL]
| DATE
| TIME
| TIMESTAMP
| DATETIME
| YEAR
| CHAR(LENGTH) [BINARY | ASCII | UNICODE]
| VARCHAR(LENGTH) [BINARY]
| BINARY(LENGTH)
| VARBINARY(LENGTH)
| TINYBLOB
| BLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
| TEXT [BINARY]
| MEDIUMTEXT [BINARY]
| LONGTEXT [BINARY]
| ENUM(VALUE1,VALUE2,VALUE3,...)
| SET(VALUE1,VALUE2,VALUE3,...)
| SPATIAL_TYPE
INDEX_COL_NAME:
COL_NAME [(LENGTH)] [ASC | DESC]
REFERENCE_DEFINITION:
REFERENCES TBL_NAME [(INDEX_COL_NAME,...)]
[MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
[ON DELETE REFERENCE_OPTION]
[ON UPDATE REFERENCE_OPTION]
REFERENCE_OPTION:
RESTRICT | CASCADE | SET NULL | NO ACTION
TABLE_OPTIONS: TABLE_OPTION [TABLE_OPTION] ...
TABLE_OPTION:
{ENGINE|TYPE} [=] ENGINE_NAME
| AUTO_INCREMENT [=] VALUE
| AVG_ROW_LENGTH [=] VALUE
| [DEFAULT] CHARACTER SET CHARSET_NAME [COLLATE COLLATION_NAME]
| CHECKSUM [=] {0 | 1}
| COMMENT [=] 'STRING'
| CONNECTION [=] 'CONNECT_STRING'
| MAX_ROWS [=] VALUE
| MIN_ROWS [=] VALUE
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'STRING'
| DELAY_KEY_WRITE [=] {0 | 1}
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| UNION [=] (TBL_NAME[,TBL_NAME]...)
| INSERT_METHOD [=] { NO | FIRST | LAST }
| DATA DIRECTORY [=] 'ABSOLUTE PATH TO DIRECTORY'
| INDEX DIRECTORY [=] 'ABSOLUTE PATH TO DIRECTORY'
SELECT_STATEMENT:
[IGNORE | REPLACE] [AS] SELECT ... (SOME LEGAL SELECT STATEMENT)
`CREATE TABLE' creates a table with the given name. You must have the
`CREATE' privilege for the table.
Rules for allowable table names are given in legal-names. By
default, the table is created in the default database. An error occurs
if the table exists, if there is no default database, or if the
database does not exist.
The table name can be specified as DB_NAME.TBL_NAME to create the table
in a specific database. This works regardless of whether there is a
default database, assuming that the database exists. If you use quoted
identifiers, quote the database and table names separately. For
example, ``mydb`.`mytbl`' is legal, but ``mydb.mytbl`' is not.
You can use the `TEMPORARY' keyword when creating a table. A
`TEMPORARY' table is visible only to the current connection, and is
dropped automatically when the connection is closed. This means that two
different connections can use the same temporary table name without
conflicting with each other or with an existing non-`TEMPORARY' table
of the same name. (The existing table is hidden until the temporary
table is dropped.) To create temporary tables, you must have the
`CREATE TEMPORARY TABLES' privilege.
The keywords `IF NOT EXISTS' prevent an error from occurring if the
table exists. However, there is no verification that the existing table
has a structure identical to that indicated by the `CREATE TABLE'
statement. _Note_: If you use `IF NOT EXISTS' in a `CREATE TABLE ...
SELECT' statement, any rows selected by the `SELECT' part are inserted
regardless of whether the table already exists.
MySQL represents each table by an `.frm' table format (definition) file
in the database directory. The storage engine for the table might
create other files as well. In the case of `MyISAM' tables, the storage
engine creates data and index files. Thus, for each `MyISAM' table
TBL_NAME, there are three disk files:
*File* *Purpose*
`TBL_NAME.frm' Table format (definition) file
`TBL_NAME.MYD' Data file
`TBL_NAME.MYI' Index file
storage-engines, describes what files each storage engine
creates to represent tables.
TYPE represents the data type is a column definition. `spatial_type'
represents a spatial data type. For general information on the
DONTPRINTYET properties of data types other than the spatial types, see
data-types. For information about spatial data types, see *Note
DONTPRINTYET properties of data types other than the spatial types, see
data-types. For information about spatial data types, see
spatial-extensions.
* If neither `NULL' nor `NOT NULL' is specified, the column is
treated as though `NULL' had been specified.
* An integer column can have the additional attribute
`AUTO_INCREMENT'. When you insert a value of `NULL' (recommended)
or `0' into an indexed `AUTO_INCREMENT' column, the column is set
to the next sequence value. Typically this is `VALUE+1', where
VALUE is the largest value for the column currently in the table.
`AUTO_INCREMENT' sequences begin with `1'.
To retrieve an `AUTO_INCREMENT' value after inserting a row, use
the `LAST_INSERT_ID()' SQL function or the `mysql_insert_id()' C
API function. See information-functions, and
mysql-insert-id.
If the `NO_AUTO_VALUE_ON_ZERO' SQL mode is enabled, you can store
`0' in `AUTO_INCREMENT' columns as `0' without generating a new
sequence value. See server-sql-mode.
* There can be only one `AUTO_INCREMENT' column per table,
it must be indexed, and it cannot have a `DEFAULT' value. An
`AUTO_INCREMENT' column works properly only if it contains only
positive values. Inserting a negative number is regarded as
inserting a very large positive number. This is done to avoid
precision problems when numbers `wrap' over from positive to
negative and also to ensure that you do not accidentally get an
`AUTO_INCREMENT' column that contains `0'.
For `MyISAM' and `BDB' tables, you can specify an `AUTO_INCREMENT'
secondary column in a multiple-column key. See
example-auto-increment.
To make MySQL compatible with some ODBC applications, you can find
the `AUTO_INCREMENT' value for the last inserted row with the
following query:
SELECT * FROM TBL_NAME WHERE AUTO_COL IS NULL
* The attribute `SERIAL' can be used as an alias for `BIGINT
UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE'.
* Character data types (`CHAR', `VARCHAR', `TEXT') can include
`CHARACTER SET' and `COLLATE' attributes to specify the character
set and collation for the column. For details, see
charset. `CHARSET' is a synonym for `CHARACTER SET'. Example:
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
MySQL 5.0 interprets length specifications in character column
definitions in characters. (Versions before MySQL 4.1 interpreted
them in bytes.)
* The `DEFAULT' clause specifies a default value for a column. With
one exception, the default value must be a constant; it cannot be
a function or an expression. This means, for example, that you
cannot set the default for a date column to be the value of a
function such as `NOW()' or `CURRENT_DATE'. The exception is that
you can specify `CURRENT_TIMESTAMP' as the default for a
`TIMESTAMP' column. See timestamp-4-1.
If a column definition includes no explicit `DEFAULT' value, MySQL
determines the default value as described in
data-type-defaults.
`BLOB' and `TEXT' columns cannot be assigned a default value.
* A comment for a column can be specified with the `COMMENT' option.
The comment is displayed by the `SHOW CREATE TABLE' and `SHOW FULL
COLUMNS' statements.
* `KEY' is normally a synonym for `INDEX'. The key attribute `PRIMARY
KEY' can also be specified as just `KEY' when given in a column
definition. This was implemented for compatibility with other
database systems.
* A `UNIQUE' index creates a constraint such that all values in the
index must be distinct. An error occurs if you try to add a new
row with a key that matches an existing row. The exception to this
is that if a column in the index is allowed to contain `NULL'
values, it can contain multiple `NULL' values. This exception does
not apply to `BDB' tables, for which a column with a `UNIQUE'
index allows only a single `NULL'.
* A `PRIMARY KEY' is a unique index where all key columns must be
defined as `NOT NULL'. If they are not explicitly declared as `NOT
NULL', MySQL declares them so implicitly (and silently). A table
can have only one `PRIMARY KEY'. If you do not have a `PRIMARY
KEY' and an application asks for the `PRIMARY KEY' in your tables,
MySQL returns the first `UNIQUE' index that has no `NULL' columns
as the `PRIMARY KEY'.
* In the created table, a `PRIMARY KEY' is placed first, followed by
all `UNIQUE' indexes, and then the non-unique indexes. This helps
the MySQL optimizer to prioritize which index to use and also more
quickly to detect duplicated `UNIQUE' keys.
* A `PRIMARY KEY' can be a multiple-column index. However, you
cannot create a multiple-column index using the `PRIMARY KEY' key
attribute in a column specification. Doing so only marks that
single column as primary. You must use a separate `PRIMARY
KEY(index_col_name, ...)' clause.
* If a `PRIMARY KEY' or `UNIQUE' index consists of only one column
that has an integer type, you can also refer to the column as
`_rowid' in `SELECT' statements.
* In MySQL, the name of a `PRIMARY KEY' is `PRIMARY'. For other
indexes, if you do not assign a name, the index is assigned the
same name as the first indexed column, with an optional suffix
(`_2', `_3', `...') to make it unique. You can see index names for
a table using `SHOW INDEX FROM TBL_NAME'. See show-index.
* Some storage engines allow you to specify an index type when
creating an index. The syntax for the INDEX_TYPE specifier is
`USING TYPE_NAME'.
Example:
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
For details about `USING', see create-index.
For more information about how MySQL uses indexes, see
mysql-indexes.
* In MySQL 5.0, only the `MyISAM', `InnoDB', `BDB', and `MEMORY'
storage engines support indexes on columns that can have `NULL'
values. In other cases, you must declare indexed columns as `NOT
NULL' or an error results.
* With `COL_NAME(LENGTH)' syntax in an index specification, you can
create an index that uses only part of a column. Index entries
consist of the first LENGTH characters of each column value for
`CHAR' and `VARCHAR' columns, and the first LENGTH bytes of each
column value for `BINARY' and `VARBINARY' columns. Indexing only a
prefix of column values like this can make the index file much
smaller. See indexes.
The `MyISAM', `BDB', and `InnoDB' storage engines support indexing
on `BLOB' and `TEXT' columns. When indexing a `BLOB' or `TEXT'
column, you _must_ specify a prefix length for the index. For
example:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
Prefixes can be up to 1000 bytes long (767 bytes for `InnoDB'
tables). Note that prefix limits are measured in bytes, whereas
the prefix length in `CREATE TABLE' statements is interpreted as
number of characters for non-binary data types (`CHAR', `VARCHAR',
`TEXT'). Take this into account when specifying a prefix length
for a column that uses a multi-byte character set.
* An INDEX_COL_NAME specification can end with `ASC' or `DESC'.
These keywords are allowed for future extensions for specifying
ascending or descending index value storage. Currently, they are
parsed but ignored; index values are always stored in ascending
order.
* When you use `ORDER BY' or `GROUP BY' on a `TEXT' or `BLOB' column
in a `SELECT', the server sorts values using only the initial
number of bytes indicated by the `max_sort_length' system
variable. See blob.
* You can create special `FULLTEXT' indexes, which are used for
full-text searches. Only the `MyISAM' storage engine supports
`FULLTEXT' indexes. They can be created only from `CHAR',
`VARCHAR', and `TEXT' columns. Indexing always happens over the
entire column; partial indexing is not supported and any prefix
length is ignored if specified. See fulltext-search, for
details of operation.
* You can create `SPATIAL' indexes on spatial data types. Spatial
types are supported only for `MyISAM' tables and indexed columns
must be declared as `NOT NULL'. See spatial-extensions.
* `InnoDB' tables support checking of foreign key constraints. See
innodb. Note that the `FOREIGN KEY' syntax in `InnoDB' is
more restrictive than the syntax presented for the `CREATE TABLE'
statement at the beginning of this section: The columns of the
referenced table must always be explicitly named. `InnoDB'
supports both `ON DELETE' and `ON UPDATE' actions on foreign keys.
For the precise syntax, see innodb-foreign-key-constraints.
For other storage engines, MySQL Server parses and ignores the
`FOREIGN KEY' and `REFERENCES' syntax in `CREATE TABLE'
statements. The `CHECK' clause is parsed but ignored by all
storage engines. See ansi-diff-foreign-keys.
* For `MyISAM' tables, each `NULL' column takes one bit extra,
rounded up to the nearest byte. The maximum row length in bytes can
be calculated as follows:
row length = 1
+ (SUM OF COLUMN LENGTHS)
+ (NUMBER OF NULL COLUMNS + DELETE_FLAG + 7)/8
+ (NUMBER OF VARIABLE-LENGTH COLUMNS)
DELETE_FLAG is 1 for tables with static row format. Static tables
use a bit in the row record for a flag that indicates whether the
row has been deleted. DELETE_FLAG is 0 for dynamic tables because
the flag is stored in the dynamic row header.
These calculations do not apply for `InnoDB' tables, for which
storage size is no different for `NULL' columns than for `NOT
NULL' columns.
The `ENGINE' table option specifies the storage engine for the table.
`TYPE' is a synonym, but `ENGINE' is the preferred option name.
The `ENGINE' table option takes the storage engine names shown in the
following table.
*Storage Engine* *Description*
`ARCHIVE' The archiving storage engine. See
archive-storage-engine.
`BDB' Transaction-safe tables with page locking. Also
known as `BerkeleyDB'. See
bdb-storage-engine.
`CSV' Tables that store rows in comma-separated values
format. See csv-storage-engine.
`EXAMPLE' An example engine. See
example-storage-engine.
`FEDERATED' Storage engine that accesses remote tables. See
federated-storage-engine.
`HEAP' This is a synonym for `MEMORY'.
`ISAM' Not available in MySQL 5.0. If you are upgrading
(_OBSOLETE_) to MySQL 5.0 from a previous version, you should
convert any existing `ISAM' tables to `MyISAM'
_before_ performing the upgrade.
`InnoDB' Transaction-safe tables with row locking and
foreign keys. See innodb.
`MEMORY' The data for this storage engine is stored only
in memory. See memory-storage-engine.
`MERGE' A collection of `MyISAM' tables used as one
table. Also known as `MRG_MyISAM'. See
merge-storage-engine.
`MyISAM' The binary portable storage engine that is the
default storage engine used by MySQL. See
myisam-storage-engine.
`NDBCLUSTER' Clustered, fault-tolerant, memory-based tables.
Also known as `NDB'. See ndbcluster.
If a storage engine is specified that is not available, MySQL uses the
default engine instead. Normally, this is `MyISAM'. For example, if a
table definition includes the `ENGINE=BDB' option but the MySQL server
does not support `BDB' tables, the table is created as a `MyISAM'
table. This makes it possible to have a replication setup where you have
transactional tables on the master but tables created on the slave are
non-transactional (to get more speed). In MySQL 5.0, a warning occurs
if the storage engine specification is not honored.
The other table options are used to optimize the behavior of the table.
In most cases, you do not have to specify any of them. These options
apply to all storage engines unless otherwise indicated:
* `AUTO_INCREMENT'
The initial `AUTO_INCREMENT' value for the table. In MySQL 5.0,
this works for `MyISAM' and `MEMORY' tables. It is also supported
for `InnoDB' as of MySQL 5.0.3. To set the first auto-increment
value for engines that do not support the `AUTO_INCREMENT' table
option, insert a `dummy' row with a value one less than the
desired value after creating the table, and then delete the dummy
row.
For engines that support the `AUTO_INCREMENT' table option in
`CREATE TABLE' statements, you can also use `ALTER TABLE TBL_NAME
AUTO_INCREMENT = N' to reset the `AUTO_INCREMENT' value.
* `AVG_ROW_LENGTH'
An approximation of the average row length for your table. You
need to set this only for large tables with variable-size rows.
When you create a `MyISAM' table, MySQL uses the product of the
`MAX_ROWS' and `AVG_ROW_LENGTH' options to decide how big the
resulting table is. If you don't specify either option, the
maximum size for a table is 65,536TB of data (4GB before MySQL
5.0.6). (If your operating system does not support files that
large, table sizes are constrained by the file size limit.) If you
want to keep down the pointer sizes to make the index smaller and
faster and you don't really need big files, you can decrease the
default pointer size by setting the `myisam_data_pointer_size'
system variable, which was added in MySQL 4.1.2. (See
server-system-variables.) If you want all your tables to be able
to grow above the default limit and are willing to have your
tables slightly slower and larger than necessary, you can increase
the default pointer size by setting this variable.
* `[DEFAULT] CHARACTER SET'
Specify a default character set for the table. `CHARSET' is a
synonym for `CHARACTER SET'.
* `COLLATE'
Specify a default collation for the table.
* `CHECKSUM'
Set this to 1 if you want MySQL to maintain a live checksum for
all rows (that is, a checksum that MySQL updates automatically as
the table changes). This makes the table a little slower to
update, but also makes it easier to find corrupted tables. The
`CHECKSUM TABLE' statement reports the checksum. (`MyISAM' only.)
* `COMMENT'
A comment for the table, up to 60 characters long.
* `CONNECTION'
The connection string for a `FEDERATED' table. This option is
available as of MySQL 5.0.13; before that, use a `COMMENT' option
for the connection string.
* `MAX_ROWS'
The maximum number of rows you plan to store in the table. This
is not a hard limit, but rather an indicator that the table must
be able to store at least this many rows.
* `MIN_ROWS'
The minimum number of rows you plan to store in the table.
* `PACK_KEYS'
Set this option to 1 if you want to have smaller indexes. This
usually makes updates slower and reads faster. Setting the option
to 0 disables all packing of keys. Setting it to `DEFAULT' tells
the storage engine to pack only long `CHAR' or `VARCHAR' columns.
(`MyISAM' only.)
If you do not use `PACK_KEYS', the default is to pack strings, but
not numbers. If you use `PACK_KEYS=1', numbers are packed as well.
When packing binary number keys, MySQL uses prefix compression:
* Every key needs one extra byte to indicate how many bytes of
the previous key are the same for the next key.
* The pointer to the row is stored in high-byte-first order
directly after the key, to improve compression.
This means that if you have many equal keys on two consecutive
rows, all following `same' keys usually only take two bytes
(including the pointer to the row). Compare this to the ordinary
case where the following keys takes `storage_size_for_key +
pointer_size' (where the pointer size is usually 4). Conversely,
you get a significant benefit from prefix compression only if you
have many numbers that are the same. If all keys are totally
different, you use one byte more per key, if the key is not a key
that can have `NULL' values. (In this case, the packed key length
is stored in the same byte that is used to mark if a key is
`NULL'.)
* `PASSWORD'
Encrypt the `.frm' file with a password. This option does nothing
in the standard MySQL version.
* `DELAY_KEY_WRITE'
Set this to 1 if you want to delay key updates for the table until
the table is closed. See the description of the `delay_key_write'
system variable in server-system-variables. (`MyISAM'
only.)
* `ROW_FORMAT'
Defines how the rows should be stored. For `MyISAM' tables, the
option value can be `FIXED' or `DYNAMIC' for static or
variable-length row format. `myisampack' sets the type to
`COMPRESSED'. See myisam-table-formats.
Starting with MySQL 5.0.3, for `InnoDB' tables, rows are stored in
compact format (`ROW_FORMAT=COMPACT') by default. The non-compact
format used in older versions of MySQL can still be requested by
specifying `ROW_FORMAT=REDUNDANT'.
* `RAID_TYPE'
`RAID' support has been removed as of MySQL 5.0. For information
on `RAID', see
`http://dev.mysql.com/doc/refman/4.1/en/create-table.html'.
* `UNION'
`UNION' is used when you want to access a collection of identical
`MyISAM' tables as one. This works only with `MERGE' tables. See
merge-storage-engine.
You must have `SELECT', `UPDATE', and `DELETE' privileges for the
tables you map to a `MERGE' table. (_Note_: Formerly, all tables
used had to be in the same database as the `MERGE' table itself.
This restriction no longer applies.)
* `INSERT_METHOD'
If you want to insert data into a `MERGE' table, you must specify
with `INSERT_METHOD' the table into which the row should be
inserted. `INSERT_METHOD' is an option useful for `MERGE' tables
only. Use a value of `FIRST' or `LAST' to have inserts go to the
first or last table, or a value of `NO' to prevent inserts. See
merge-storage-engine.
* `DATA DIRECTORY', `INDEX DIRECTORY'
By using `DATA DIRECTORY='DIRECTORY'' or `INDEX
DIRECTORY='DIRECTORY'' you can specify where the `MyISAM' storage
engine should put a table's data file and index file. The
directory must be the full pathname to the directory, not a
relative path.
These options work only when you are not using the
-skip-symbolic-links option. Your operating system must also have
a working, thread-safe `realpath()' call. See
symbolic-links-to-tables, for more complete information.
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'.
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:
mysql> SELECT * FROM foo;
+---+
| n |
+---+
| 1 |
+---+
mysql> CREATE TABLE bar (m INT) SELECT n FROM foo;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM bar;
+------+---+
| m | n |
+------+---+
| NULL | 1 |
+------+---+
1 row in set (0.00 sec)
For each row in table `foo', a row is inserted in `bar' with the values
from `foo' and default values for the new columns.
In a table resulting from `CREATE TABLE ... SELECT', columns named
only in the `CREATE TABLE' part come first. Columns named in both parts
or only in the `SELECT' part come after that. The data type of `SELECT'
columns can be overridden by also specifying the column in the `CREATE
TABLE' part.
If any errors occur while copying the data to the table, it is
automatically dropped and not created.
`CREATE TABLE ... SELECT' does not automatically create any indexes for
you. This is done intentionally to make the statement as flexible as
possible. If you want to have indexes in the created table, you should
specify these before the `SELECT' statement:
mysql> CREATE TABLE bar (UNIQUE (n)) SELECT n FROM foo;
Some conversion of data types might occur. For example, the
`AUTO_INCREMENT' attribute is not preserved, and `VARCHAR' columns can
become `CHAR' columns.
When creating a table with `CREATE ... SELECT', make sure to alias any
function calls or expressions in the query. If you do not, the `CREATE'
statement might fail or result in undesirable column names.
CREATE TABLE artists_and_works
SELECT artist.name, COUNT(work.artist_id) AS number_of_works
FROM artist LEFT JOIN work ON artist.id = work.artist_id
GROUP BY artist.id;
You can also explicitly specify the type for a generated column:
CREATE TABLE foo (a TINYINT NOT NULL) SELECT b+1 AS a FROM bar;
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;
`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.
You can precede the `SELECT' by `IGNORE' or `REPLACE' to indicate how
to handle rows that duplicate unique key values. With `IGNORE', new
rows that duplicate an existing row on a unique key value are
discarded. With `REPLACE', new rows replace rows that have the same
unique key value. If neither `IGNORE' nor `REPLACE' is specified,
duplicate unique key values result in an error.
To ensure that the binary log can be used to re-create the original
tables, MySQL does not allow concurrent inserts during `CREATE TABLE
... SELECT'.
Info Catalog
(mysql.info) create-index
(mysql.info) data-definition
(mysql.info) drop-database
automatically generated byinfo2html