用 SELECT 的结果创建表 - MySQL中数据表操作详解_数据库技术_Linux公社-Linux系统门户网站
你好，游客
MySQL中数据表操作详解
用 SELECT 的结果创建表
来源：MySQL社区&
作者：MySQL编辑
用 SELECT 的结果创建表
关系数据库的一个重要概念是，任何数据都表示为行和列组成的表，而每条 SELECT 语句的结果也都是一个行和列组成的表。在许多情况下，来自 SELECT 的“表”仅是一个随着您的工作在显示屏上滚动的行和列的图像。在 MySQL 3.23 以前，如果想将 SELECT 的结果保存在一个表中以便以后的查询使用，必须进行特殊的安排：
1) 运行 DESCRIBE 或 SHOW COLUMNS 查询以确定想从中获取信息的表中的列类型。
2) 创建一个表，明确地指定刚才查看到的列的名称和类型。
3) 在创建了该表后，发布一条 INSERT ... SELECT 查询，检索出结果并将它们插入所创建的表中。
在 MySQL 3.23 中，全都作了改动。CREATE TABLE ... SELECT 语句消除了这些浪费时间的东西，使得能利用 SELECT 查询的结果直接得出一个新表。只需一步就可以完成任务，不必知道或指定所检索的列的数据类型。这使得很容易创建一个完全用所喜欢的数据填充的表，并且为进一步查询作了准备。
如果你在CREATE语句后指定一个SELECT，MySQL将为在SELECT中所有的单元创键新字段。例如：
mysql& CREATE TABLE test
-& (a int not null auto_increment,primary key (a), key(b))
-& SELECT b,c from test2;
这将创建一个有3个列(a，b，c)的表，其中b，c列的数据来自表test2。注意如果在拷贝数据进表时发生任何错误，表将自动被删除。
可以通过选择一个表的全部内容（无 WHERE 子句）来拷贝一个表，或利用一个总是失败的 WHERE 子句来创建一个空表，如：
mysql& CREATE TABLE test SELECT * from test2;
mysql& CREATE TABLE test SELECT * from test2 where 0;
如果希望利用 LOAD DATA 将一个数据文件装入原来的文件中，而不敢肯定是否具有指定的正确数据格式时，创建空拷贝很有用。您并不希望在第一次未得到正确的选项时以原来表中畸形的记录而告终。利用原表的空拷贝允许对特定的列和行分隔符用 LOAD DATA 的选项进行试验，直到对输入数据的解释满意时为止。在满意之后，就可以将数据装入原表了。
可结合使用 CREATE TEMPORARY TABLE 与 SELECT 来创建一个临时表作为它自身的拷贝，如：
这允许修改 my_tbl 的内容而不影响原来的内容。在希望试验对某些修改表内容的查询，而又不想更改原表内容时，这样做很有用。为了使用利用原表名的预先编写的脚本，不需要为引用不同的表而编辑这些脚本；只需在脚本的起始处增加 CREATE TEMPORARY TABLE 语句即可。相应的脚本将创建一个临时拷贝，并对此拷贝进行操作，当脚本结束时服务器会自动删除这个拷贝。
要创建一个作为自身的空拷贝的表，可以与 CREATE TEMPORARY ... SELECT 一起使用 WHERE 0 子句，例如：
但创建空表时有几点要注意。在创建一个通过选择数据填充的表时，其列名来自所选择的列名。如果某个列作为表达式的结果计算，则该列的“名称”为表达式的文本。表达式不是合法的列名，可在 mysql 中运行下列查询了解这一点：
为了正常工作，可为该列提供一个合法的别称：
如果选择了来自不同表的具有相同名称的列，将会出现一定的困难。假定表 t1 和 t2 两者都具有列 c，而您希望创建一个来自两个表中行的所有组合的表。那么可以提供别名指定新表中惟一性的列名，如：通过选择数据进行填充来创建一个表并会自动拷贝原表的索引。
用ALTER TABLE语句修改表的结构
有时你可能需要改变一下现有表的结构，那么Alter Table语句将是你的合适选择。
alter table tbl_name add col_name type
例如,给表增加一列weight
mysql&alter tabl
alter table tbl_name drop col_name
例如，删除列weight：
mysql&alter t
alter table tbl_name modify col_name type
例如，改变weight的类型：
mysql& alter table pet mo
另一种方法是：
alter table tbl_name change old_col_name col_name type
mysql& alter table pet change we
mysql&alter table p
alter table tbl_name rename new_tbl
例如，把pet表更名为animal
mysql&alter tab
改变表的类型
另外，可以为列增加或删除索引等属性，不再详述，请参阅附录。
用DROP TABLE 语句删除数据表
DROP TABLE [IF EXISTS] tbl_name [, tbl_name,...]
DROP TABLE删除一个或多个数据库表。所有表中的数据和表定义均被删除，故小心使用这个命令！
在MySQL 3.22或以后版本，你可以使用关键词IF EXISTS类避免不存在表的一个错误发生。
mysql&DROP TABLE
或者，也可以同时指定数据库和表：
mysql&DROP TABLE mytest.
本节讲述了有关表的大部分操作，现在将所述内容总结如下：
MySQL的表的三种类型
如何创建表、删除表
如何改变表的结构、名字
如何使用mysqlshow实用程序3
【内容导航】
相关资讯 & & &
& (01/24/:39)
& (04/14/:38)
& (02/03/:17)
& (01/24/:36)
& (01/29/:40)
　　　同意评论声明
　　　发表
尊重网上道德，遵守中华人民共和国的各项有关法律法规
承担一切因您的行为而直接或间接导致的民事或刑事法律责任
本站管理人员有权保留或删除其管辖留言中的任意内容
本站有权在网站内转载或引用您的评论
参与本评论即表明您已经阅读并接受上述条款MySQL 5.1 英文手册 :: 12.1.17 CREATE TABLE Syntax
:: 12.1.17 CREATE TABLE Syntax
MySQL 5.1 Reference Manual
12.1.17.&CREATE TABLE Syntax
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
[table_options]
[partition_options]
select_statement
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
{ LIKE old_tbl_name | (LIKE old_tbl_name) }
create_definition:
col_name column_definition
| [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
[index_option] ...
| {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
[index_name] [index_type] (index_col_name,...)
[index_option] ...
| {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
[index_option] ...
| [CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name,...) reference_definition
| CHECK (expr)
column_definition:
data_type [NOT NULL | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string']
[COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
[STORAGE {DISK|MEMORY|DEFAULT}]
[reference_definition]
data_type:
BIT[(length)]
| 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]
| TIMESTAMP
| DATETIME
| CHAR[(length)]
[CHARACTER SET charset_name] [COLLATE collation_name]
| VARCHAR(length)
[CHARACTER SET charset_name] [COLLATE collation_name]
| BINARY[(length)]
| VARBINARY(length)
| TINYBLOB
| MEDIUMBLOB
| LONGBLOB
| TINYTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| TEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| MEDIUMTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| LONGTEXT [BINARY]
[CHARACTER SET charset_name] [COLLATE collation_name]
| ENUM(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| SET(value1,value2,value3,...)
[CHARACTER SET charset_name] [COLLATE collation_name]
| spatial_type
index_col_name:
col_name [(length)] [ASC | DESC]
index_type:
USING {BTREE | HASH | RTREE}
index_option:
KEY_BLOCK_SIZE [=] value
| index_type
| WITH PARSER parser_name
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 [=] engine_name
| AUTO_INCREMENT [=] value
| AVG_ROW_LENGTH [=] value
| [DEFAULT] CHARACTER SET [=] charset_name
| CHECKSUM [=] {0 | 1}
| [DEFAULT] COLLATE [=] collation_name
| COMMENT [=] 'string'
| CONNECTION [=] 'connect_string'
| DATA DIRECTORY [=] 'absolute path to directory'
| DELAY_KEY_WRITE [=] {0 | 1}
| INDEX DIRECTORY [=] 'absolute path to directory'
| INSERT_METHOD [=] { NO | FIRST | LAST }
| KEY_BLOCK_SIZE [=] value
| MAX_ROWS [=] value
| MIN_ROWS [=] value
| PACK_KEYS [=] {0 | 1 | DEFAULT}
| PASSWORD [=] 'string'
| ROW_FORMAT [=] {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}
| TABLESPACE tablespace_name [STORAGE {DISK|MEMORY|DEFAULT}]
| UNION [=] (tbl_name[,tbl_name]...)
partition_options:
PARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list)
| RANGE(expr)
| LIST(expr) }
[PARTITIONS num]
[SUBPARTITION BY
{ [LINEAR] HASH(expr)
| [LINEAR] KEY(column_list) }
[SUBPARTITIONS num]
[(partition_definition [, partition_definition] ...)]
partition_definition:
PARTITION partition_name
[VALUES {LESS THAN {(expr) | MAXVALUE} | IN (value_list)}]
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
[(subpartition_definition [, subpartition_definition] ...)]
subpartition_definition:
SUBPARTITION logical_name
[[STORAGE] ENGINE [=] engine_name]
[COMMENT [=] 'comment_text' ]
[DATA DIRECTORY [=] 'data_dir']
[INDEX DIRECTORY [=] 'index_dir']
[MAX_ROWS [=] max_number_of_rows]
[MIN_ROWS [=] min_number_of_rows]
[TABLESPACE [=] tablespace_name]
[NODEGROUP [=] node_group_id]
select_statement:
[IGNORE | REPLACE] [AS] SELECT ...
(Some legal select statement)
creates a table with
the given name. You must have the
privilege for the table.
Rules for allowable table names are given in
. 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, write `mydb`.`mytbl`, not
`mydb.mytbl`.
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
privilege.
automatically commit the current active transaction if you use
the TEMPORARY keyword.
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
statement.
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
, describes what files each
storage engine creates to represent tables. If a table name
contains special characters, the names for the table files contain
encoded versions of those characters as described in
data_type represents the data type in a
column definition. spatial_type
represents a spatial data type. The data type syntax shown is
representative only. For a full description of the syntax
available for specifying column data types, as well as information
about the properties of each type, see
Some attributes do not apply to all data types.
AUTO_INCREMENT applies only to integer and
floating-point types. DEFAULT does not apply to
If neither NULL nor NOT
NULL is specified, the column is treated as though
NULL had been specified.
An integer or floating-point 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
To retrieve an AUTO_INCREMENT value after
inserting a row, use the
SQL function
function. See , and
SQL mode is enabled, you can store 0 in
AUTO_INCREMENT columns as
0 without generating a new sequence value.
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
For MyISAM tables, you can specify an
AUTO_INCREMENT secondary column in a
multiple-column key. See
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
For information about InnoDB and
AUTO_INCREMENT, see
Character data types (,
) can include
CHARACTER SET and
COLLATE attributes to specify the character
set and collation for the column. For details, see
. CHARSET is a
synonym for CHARACTER SET. Example:
CREATE TABLE t (c CHAR(20) CHARACTER SET utf8 COLLATE utf8_bin);
MySQL 5.1 interprets length specifications in
character column definitions in characters. (Versions before
MySQL 4.1 interpreted them in bytes.) Lengths for
are in bytes.
The DEFAULT clause specifies a default
value for a column. With one exception, the default value must
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
. The exception is
that you can specify
default for a
If a column definition includes no explicit
DEFAULT value, MySQL determines the default
value as described in .
columns cannot be assigned
a default value.
fails if a
date-valued default is not correct according to the
even if strict SQL mode is not enabled. For example,
c1 DATE DEFAULT '' causes
to fail with
Invalid default value for 'c1'.
Native default value handling in MySQL Cluster NDB 7.0.15, MySQL Cluster
NDB 7.1.4, and later.&
Starting with MySQL Cluster NDB 7.0.15 and MySQL Cluster NDB
7.1.4, default values for table columns are stored by
NDBCLUSTER, rather than by the
MySQL server as was previously the case. Because less data
must be sent from an SQL node to the data nodes, inserts on
tables having column value defaults can be performed more
efficiently than before.
Tables created using MySQL Cluster releases previous to the
introduction of native default value support can still be used
in MySQL Cluster NDB 7.0.15 and later, as well as well as
MySQL Cluster NDB 7.1.4 and later. However, such tables
continue to use defaults supplied by the MySQL server until
they are upgraded. This upgrade can be done by means of an
statement.
You cannot set or change a table column's default value
using an online
Tables created in versions of MySQL Cluster that support
native default values cannot be used with versions of MySQL
Cluster that lack this support.
NDBCLUSTER tables supporting
native default values are still subject to the restrictions on
default values imposed by the MySQL server. For more
information, see .
A comment for a column can be specified with the
COMMENT option, up to 255 characters long.
The comment is displayed by the
statements.
Beginning with MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB
6.3.2, it is also possible to specify a data storage format
for individual columns of NDB
tables using COLUMN_FORMAT. Allowable
column formats are ,
DYNAMIC, and DEFAULT.
is used to specify
fixed-width storage, DYNAMIC allows the
column to be variable-width, and DEFAULT
causes the column to use fixed-width or variable-width storage
as determined by the column's data type (possibly overridden
by a ROW_FORMAT specifier).
For NDB tables, the default value
for COLUMN_FORMAT is
COLUMN_FORMAT currently has no effect on
columns of tables using storage engines other than
For NDB tables, beginning with
MySQL Cluster NDB 6.2.5 and MySQL Cluster NDB 6.3.2, it is
also possible to specify whether the column is stored on disk
or in memory by using a STORAGE clause.
STORAGE DISK causes the column to be stored
on disk, and STORAGE MEMORY causes
in-memory storage to be used. The
statement used must still include a
TABLESPACE clause:
mysql& CREATE TABLE t1 (
c1 INT STORAGE DISK,
c2 INT STORAGE MEMORY
-& ) ENGINE NDB;
ERROR 1005 (HY000): Can't create table 'c.t1' (errno: 140)
mysql& CREATE TABLE t1 (
c1 INT STORAGE DISK,
c2 INT STORAGE MEMORY
-& ) TABLESPACE ts_1 ENGINE NDB;
Query OK, 0 rows affected (1.06 sec)
For NDB tables, STORAGE
DEFAULT is equivalent to STORAGE
The STORAGE clause has no effect on tables
using storage engines other than
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 value that matches an
existing row. For all engines, a UNIQUE
index allows multiple NULL values for
columns that can contain 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
In InnoDB tables, having a long
PRIMARY KEY wastes a lot of space. (See
In the created table, a PRIMARY KEY is
placed first, followed by all UNIQUE
indexes, and then the nonunique 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, ...)
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
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
...) to make it unique. You can see index
names for a table using SHOW INDEX FROM
tbl_name. See
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.
CREATE TABLE lookup
(id INT, INDEX USING BTREE (id))
ENGINE = MEMORY;
Before MySQL 5.1.10, USING can be given
only before the index column list. As of 5.1.10, the preferred
position is after the column list. Use of the option before
the column list will no longer be recognized in a future MySQL
index_option values specify
additional options for an index. USING is
one such option. For details about allowable
index_option values, see
For more information about indexes, see
In MySQL 5.1, only the MyISAM,
InnoDB, 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.
columns, indexes can
be created that use only the leading part of column values,
col_name(length)
syntax to specify an index prefix length.
columns also can be
indexed, but a prefix length must be
given. Prefix lengths are given in characters for nonbinary
string types and in bytes for binary string types. That is,
index entries consist of the first
length characters of each column
value for ,
columns, and the first
length bytes of each column value
columns. Indexing only a
prefix of column values like this can make the index file much
smaller. See .
Only the MyISAM and
InnoDB storage engines support indexing on
columns. 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
statements is
interpreted as number of characters for nonbinary data types
). 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 ar index values are
always stored in ascending order.
When you use ORDER BY or GROUP
column in a
, the server sorts values
using only the initial number of bytes indicated by the
variable. See .
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
columns. Indexing always
happens ov column prefix indexing is not
supported and any prefix length is ignored if specified. See
, for details of operation. A
WITH PARSER clause can be specified as an
index_option value to associate a
parser plugin with the index if full-text indexing and
searching operations need special handling. This clause is
legal only for FULLTEXT indexes. See
, for details on creating plugins.
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
InnoDB tables support checking of foreign
key constraints. See . Note that the
FOREIGN KEY syntax in
InnoDB is more restrictive than the syntax
presented for the
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
For other storage engines, MySQL Server parses and ignores the
FOREIGN KEY and
REFERENCES syntax in
statements. The
CHECK clause is parsed but ignored by all
storage engines. See .
For users familiar with the ANSI/ISO SQL Standard, please
note that no storage engine, including
InnoDB, recognizes or enforces the
MATCH clause used in referential
integrity constraint definitions. Use of an explicit
MATCH clause will not have the specified
effect, and also causes ON DELETE and
ON UPDATE clauses to be ignored. For
these reasons, specifying MATCH should be
The MATCH clause in the SQL standard
controls how NULL values in a composite
(multiple-column) foreign key are handled when comparing to
a primary key. InnoDB essentially
implements the semantics defined by MATCH
SIMPLE, which allow a foreign key to be all or
partially NULL. In that case, the (child
table) row containing such a foreign key is allowed to be
inserted, and does not match any row in the referenced
(parent) table. It is possible to implement other semantics
using triggers.
Additionally, MySQL and InnoDB require
that the referenced columns be indexed for performance.
However, the system does not enforce a requirement that the
referenced columns be UNIQUE or be
declared NOT NULL. The handling of
foreign key references to nonunique keys or keys that
contain NULL values is not well defined
for operations such as
or DELETE CASCADE. You are advised to use
foreign keys that reference only UNIQUE
and NOT NULL keys.
Furthermore, InnoDB does not recognize or
support &inline REFERENCES
specifications& (as defined in the SQL standard)
where the references are defined as part of the column
specification. InnoDB accepts
REFERENCES clauses only when specified as
part of a separate FOREIGN KEY
specification. For other storage engines, MySQL Server
parses and ignores foreign key specifications.
Partitioned tables do not support foreign keys. See
, for more
information.
There is a hard limit of 4096 columns per table, but the
effective maximum may be less for a given table and depends on
the factors discussed in .
The TABLESPACE and STORAGE
table options were both introduced in MySQL 5.1.6. In MySQL 5.1,
they are employed only with
NDBCLUSTER tables. The tablespace
named tablespace_name must already have
been created using CREATE TABLESPACE.
STORAGE determines the type of storage used
(disk or memory), and can be one of DISK,
MEMORY, or DEFAULT.
TABLESPACE ... STORAGE DISK assigns a table to
a MySQL Cluster Disk Data tablespace. See
, for more information.
A STORAGE clause cannot be used in a
statement without a
TABLESPACE clause.
The ENGINE table option specifies the storage
engine for the table.
The ENGINE table option takes the storage
engine names shown in the following table.