MySQL进阶语法

2. create table

语法：

CREATE TABLE创建一个具有给定名称的表。你必须具有表的CREATE权限。

默认情况下，CREATE TABLE会在默认数据库创建表，且使用的是InnoDB存储引擎。如果表已经存在，或者没有默认数据库，或者指定的数据库不存在，则返回错误。

MySQL会存储表创建时原始的CREATE TABLE语句，包括所有说明和表选项。

本节将讨论CREATE TABLE的以下几个方面：

表名

临时表

复制表

列数据类型及属性

索引和外键

表选项

创建分区表

表名

tbl_name

可以将表名称指定为db_name.tbl_name以期在特定数据库中创建表。只要目标数据库存在，那么不论是否指定了默认数据库，这种方法都有效。如果你在名字里面使用了引用标识符，那么就需要分别引用数据库名和表名。例如，`mydb`.`mytbl`，而不能是`mydb.mytbl`

IF NOT EXISTS

如果表已经存在，则防止发生错误。注意，MySQL并不会验证现有表结构与CREATE TABLE语句指定的表结构是否相同。

临时表

创建表时可以使用关键字TEMPORARY。临时表仅在当前会话中可见，并在会话关闭时自动删除。

复制表

LIKE

可以使用CREATE TABLE ... LIKE语句根据另一个表的定义创建一个空表，该空表将包含原表所有的列属性和索引：

[AS] query_expression

要从另一个表中创建一个新表，可以在CREATE TABLE语句的末尾添加一个SELECT语句：

IGNORE | REPLACE

当使用SELECT语句复制表时，IGNORE和REPLACE选项用于指示如何处理导致唯一键产生重复的行。

列数据类型及属性

理论上每个表的列数的上限是4096，但是对于一个给定的表来说，有效的最大值可能比该值要小。

data_type

data_type表示列定义中的数据类型，spatial_type表示空间数据类型。

某些属性不适用于所有数据类型。AUTO_INCREMENT只适用于整数和浮点数类型。DEFAULT不适用于TEXT或BLOB类型；

字符数据类型（CHAR, VARCHAR, TEXT）可以包含CHARACTER SET和COLLATE属性以指定列的字符集和排序规则。其中，CHARSET等同于CHARACTER SET。例如：

MySQL5.5将字符列定义中的长度说明解释为字符数；将二进制字符列（BINARY和VARBINARY）定义中的长度说明解释为字节数

对于CHAR, VARCHAR, BINARY和VARBINARY列，可以创建仅使用列值前缀的索引，可以使用col_name(length)语法指定索引前缀的长度。TEXT和BLOB列也可以被索引，但必须指定前缀长度。对于非二进制字符串类型，前缀长度是字符数；对于二进制字符串类型，前缀长度是字节数。也就是说，对于CHAR, VARCHAR和TEXT列，索引项由列值的前length个字符组成；对于BINARY, VARBINARY和BLOB列，索引项由列值的前length个字节组成。前缀索引可以使索引文件更小。

只有InnoDB和MyISAM存储引擎支持对TEXT和BLOB列进行索引。例如：

NOT NULL | NULL

一个列如果既没有指定NULL，也没有指定NOT NULL，则等同于指定了NULL。

在MySQL 5.5中，只有InnoDB，MyISAM和MEMORY存储引擎支持索引可为NULL的列。其它情况下，你必须将索引列声明为NOT NULL，否则会出错

DEFAULT

指定列的默认值。默认值必须是常量，不能是函数或表达式。这意味着，你不能将一个DATE列的默认值设置为诸如NOW()或CURRENT_DATE之类的函数的值。例外是可以将CURRENT_TIMESTAMP指定为TIMESTAMP列的默认值。

如果列定义没有显式的DEFAULT值，则MySQL将按照第11.6节“数据类型默认值”中所描述的来确定默认值。

TEXT和BLOB列不能指定默认值

如果日期的默认值不符合NO_ZERO_IN_DATE SQL模式，则CREATE TABLE将失败，即使并没有启用严格的SQL模式。例如，c1 DATE DEFAULT '2010-00-00' 将导致CREATE TABLE失败，错误信息为：Invalid default value for 'c1'

AUTO_INCREMENT

整数或浮点数列可以具有额外的属性AUTO_INCREMENT。当你将NULL或0插入被索引的AUTO_INCREMENT列时，该列将被设置为下一个序列值，通常为value + 1，其中value是表中当前列的最大值。AUTO_INCREMENT序列从1开始。

要想在插入一行之后检索AUTO_INCREMENT的值，请使用SQL函数LAST_INSERT_ID()或C API函数mysql_insert_id()

如果启用了NO_AUTO_VALUE_ON_ZERO SQL模式，你可以在AUTO_INCREMENT列中存储0，而不生成新的序列值。

每个表只能有一个AUTO_INCREMENT列，它必须被索引，而且不能有DEFAULT值。只有当AUTO_INCREMENT列仅包含正值时，它才可以正常工作。插入一个负数被认为是插入一个非常大的正数。这样做是为了避免数值“从正到负”产生的精度问题，并确保不会意外地得到一个包含0的AUTO_INCREMENT列。

对于MyISAM表，可以在多列索引（组合索引）中指定一个AUTO_INCREMENT辅助列。

为使MySQL与某些ODBC应用程序兼容，你可以使用如下查询获取最后插入行的AUTO_INCREMENT值：

此方法要求sql_auto_is_null变量不为0

COMMENT

可以使用COMMENT选项指定列的注释，注释最多包含1024个字符（MySQL 5.5.3之前是255个字符）。注释由SHOW CREATE TABLE和SHOW FULL COLUMNS语句中显示。

COLUMN_FORMAT

COLUMN_FORMAT用于指定NDB表中列的数据存储格式，它对其它存储引擎表中的列无影响。在MySQL 5.5及更高版本中，COLUMN_FORMAT被忽略。

STORAGE

对于NDB表，可以通过使用STORAGE子句来指定列是存储在磁盘上还是内存中。STORAGE子句对使用NDB以外的存储引擎的表没有影响。STORAGE关键字仅在与NDB Cluster一起提供的mysqld的构建中受支持；在其它任何版本的MySQL中都不识别，这种情况下使用STORAGE关键字会导致语法错误

索引和外键

CONSTRAINT symbol

如果给定了CONSTRAINT symbol子句，则symbol值在数据库中必须唯一。重复的symbol会导致错误。如果没有给出子句，或者在CONSTRAINT关键字之后不包含symbol，则会自动创建约束的名称。

PRIMARY KEY

唯一索引且全部索引列必须为NOT NULL，即唯一非空索引。如果列没有被显式地声明为NOT NULL，那么MySQL会隐式地将其声明为NOT NULL。表只能有一个PRIMARY KEY。PRIMARY KEY的名称总是PRIMARY，因此不能将其被用作任何其它类型索引的名称。

如果你没有定义PRIMARY KEY，并且应用程序需要表中的PRIMARY KEY，MySQL将返回第一个所有列非空的唯一索引作为PRIMARY KEY。

在InnoDB表中，请保持PRIMARY KEY简短，以降低辅助索引的存储开销。每个辅助索引条目包含对应行的主键列的副本。

在创建的表中，PRIMARY KEY被放在最前面，后跟全部的UNIQUE索引，最后是非唯一索引。这种排列结构有助于MySQL优化器决定优先使用哪个索引，也有助于更快地检测到重复的UNIQUE键。

PRIMARY KEY可以是多列索引（组合索引）。但是，你不能在列说明中使用PRIMARY KEY键属性创建一个多列索引。这样做只会将单个列标记为primary。你必须使用单独的PRIMARY KEY(index_col_name, ...)子句。

如果PRIMARY KEY只包含一个整数列，你可以在SELECT语句中使用_rowid引用该列。

在MySQL中，PRIMARY KEY的名称是PRIMARY。对于其它索引，如果没有指定名称，MySQL将为其分配一个与第一个索引列同名的名称，并加上可选的后缀（_2, _3, ...）使其唯一。你可以使用SHOW INDEX FROM tbl_name查看表的索引名称。

KEY | INDEX

KEY通常是INDEX的同义词。在列定义中，键属性PRIMARY KEY也可以指定为KEY。这是为了与其它数据库系统兼容而实现的。

UNIQUE

唯一索引创建了一个约束，它要求索引中的所有值必须是不同的。如果尝试添加与现有行的键值匹配的新行，就会出现错误。对于所有的存储引擎，唯一索引允许可为NULL的索引列同时包含多个NULL值。

如果唯一索引只包含一个整数列，那么你可以在SELECT语句中使用_rowid引用该列。

FULLTEXT

全文索引是一种用于全文搜索的特殊类型的索引。只有MyISAM存储引擎支持全文索引。它们只能由CHAR, VARCHAR和TEXT列创建。而且必须索引整个列，而不能仅使用列前缀，任何指定的前缀长度都将被忽略。如果全文索引和搜索操作需要特殊的处理，则可以通过index_option中的WITH PARSER子句将解析器插件与索引相关联。

SPATIAL

你可以在空间数据类型上创建空间索引。只有MyISAM表支持空间类型，且索引列必须声明为NOT NULL。

FOREIGN KEY

MySQL支持外键，它允许跨表交叉引用相关数据；以及外键约束，它有助于保持这种扩展数据的一致性。

采用InnoDB存储引擎的分区表不支持外键。

CHECK

CHECK子句被解析，但被所有存储引擎忽略

index_col_name

index_col_name说明可以以ASC或DESC结尾。这些关键字主要用于将来的扩展，用以指定索引值正序或降序存储。目前，它们被解析但被忽略。索引值始终以升序存储。

对于InnoDB表，通过length属性指定的前缀最长767字节，如果开启了innodb_large_prefix则可以扩展到3072字节。

前缀限制以字节为单位。在CREATE TABLE, ALTER TABLE和CREATE INDEX语句中，为非二进制字符串类型（CHAR, VARCHAR, TEXT）指定的前缀长度被解释为字符数；为二进制字符串类型（BINARY, VARBINARY, BLOB）指定的前缀长度被解释为字节数。在为使用多字节字符集的非二进制字符串列指定前缀长度时，请注意这一点。

index_type

某些存储引擎允许你在创建索引时指定索引类型。index_type说明符的语法是USING type_name。

例如：

USING的首选位置是在索引列列表之后。它可以在列列表之前给出，但支持在该位置上使用该选项已经被弃用，并将在以后的MySQL版本中删除。

index_option

index_option值指定索引的其它选项

KEY_BLOCK_SIZE

对于MyISAM表，KEY_BLOCK_SIZE用于指定索引键块的大小，以字节为单位。该值仅为提示，如果有必要，一个不同的大小将被使用。为单个索引定义指定的KEY_BLOCK_SIZE值将覆盖表级别的KEY_BLOCK_SIZE值。

with parser

如果全文索引和搜索操作需要特殊的处理，则可以在index_option中使用with parser子句将解析器插件与索引相关联。此子句仅对FULLTEXT索引有效。

comment

从MySQL 5.5.3开始，索引定义可以包含最多1024个字符的可选注释

reference_definition

InnoDB表支持外键约束检查。被引用表的列必须被明确地命名。外键上既有on delete也有on update操作。

对于其它存储引擎，MySQL服务器解析并忽略create table语句中的foreign key和references语法。

重要

对于熟悉ANSI/IOS SQL标准的用户，请注意，包括InnoDB在内没有任何存储引擎识别或执行引用完整性约束定义中的match子句。显式地使用match子句不会产生指定的效果，还会导致on delete和on update子句被忽略。出于这些原因，我们应该避免指定match。

在SQL标准中，match子句控制复合（多列）外键中的NULL值在与主键进行比较时如何处理。InnoDB实际上实现了match simple中定义的语义，它允许外键部分或全部为NULL。在这种情况下，包含这样外键的行可以插入到子表中，但不匹配被引用表（父表）中的任何行。可以使用触发器实现其它语义。

此外，MySQL要求被引用的列必须被索引，以提高性能。然而，InnoDB不强制要求被引用的列是UNIQUE或NOT NULL。对非唯一索引或者包含NULL值的索引进行外键引用，对于像upadte或delete cascade这样的操作没有很好的定义。因此，建议只对唯一非空索引或主键进行外键引用。

MySQL解析但忽略“内联引用说明”，其中引用被定义为列说明的一部分。仅当references子句被指定为单独的foreign key说明的一部分时，才会被MySQL接受。

reference_option

表选项

1. create index

语法：create「unique | fulltext | spatial」index「idx_name」on「tbl_name」(indexed_col_name, ...) [index_option]

功能：创建新的索引

选项和参数：

indexed_col_name：col_name [(length)]

index_option：

        key_block_size [=] value

        index_type

index_type：

        using {BTREE | HASH}

示例：

create index idx_test1 on employee (sex, name);

create unique index idx_test2 on employee (id, name) using BTREE;

说明：

① create index被映射为alter table语句，用以创建索引。请注意，create index不能创建主键，创建主键需使用alter table；

② 通常应该在创建表的同时，即create table语句中，创建所需的全部索引。对于InnoDB表尤为如此，因为InnoDB表的主键决定了数据文件中行的物理布局。create index语句的作用是帮助你向已经存在的表中添加索引；

③ (col1, col2, ...)形式的列列表用于创建一个多列索引（组合索引），此时索引键的键值由所有列值组合而成；

④ 所有存储引擎都允许唯一索引在可为NULL的列上同时包含多个NULL值；

⑤ 支持空间数据类型（POINT和GEOMETRY）的存储引擎有：InnoDB, MyISAM, NDB和ARCHIVE；

1.1 创建前缀索引

对于字符串类型的列，可以使用值的前缀来创建索引

语法：create index「idx_name」on「tbl_name」(col_name(length));

参数：col_name(length)用于指定索引前缀长度

优势：使用前缀索引可以使索引文件更小，这样可以节省大量磁盘空间，甚至还可以加速insert操作

说明：

① 可以为char, varchar, binary和varbinary类型的列创建前缀索引；

② blob和text列必须使用前缀索引， 且只有InnoDB和MyISAM存储引擎支持对blob和text列进行索引；

③ 前缀限制以字节为单位。在create table, alter table, create index语句中，为非二进制字符串类型(char, varchar, text)指定的前缀长度被解释为字符长度；为二进制字符串类型(binary, varbinary, blob)指定的前缀长度则被解释为字节长度。在为多字节字符集的非二进制字符串类型的列指定前缀长度时，应考虑到这一点；

④ 是否支持前缀索引以及前缀索引的长度限制，与存储引擎有关。例如，正常情况下InnoDB表的前缀限制为767字节，如果开启innodb_large_prefix选项则可以长达3072字节；对于MyISAM表，前缀限制为1000字节；

示例：

create index idx_name on customer (name(10));

该语句使用name列的前10个字符创建索引（假定name是非二进制字符串类型）

1.2 索引的特性

◇ 全文索引：

① 只有MyISAM存储引擎支持全文索引；

② 索引列必须为char, varchar或者text类型；

③ 必须索引整个列，不能使用列前缀（前缀长度自动被忽略）；

◇ 空间索引：

① 只有MyISAM存储引擎支持空间索引。为其它存储引擎指定空间索引将导致错误；

② 索引列不能为NULL；

③ 必须索引整个列，不能使用列前缀；

◇ 普通索引（非空间索引）：

① 只有InnoDB, MyISAM和MEMORY存储引擎支持对可以为NULL的列进行索引；

② 只有InnoDB和MyISAM存储引擎支持对blob和text列进行索引；

③ 除主键外，索引中的列可以为NULL；

④ 非空间索引的索引类型取决于存储引擎，目前使用B-Tree；

⑤ 除POINT外，非空间索引中的空间列必须使用列前缀，必须指定列前缀长度，单位字节；

⑥ 除ARCHIVE外，所有支持空间数据类型的存储引擎，都支持非空间索引；

1.3 索引选项

在索引列列表之后，可以给出索引选项。index_option值可以为：

◇ key_block_size [=] value

对于MyISAM表，key_block_size用于指定索引键的块大小，以字节为单位。该值仅为提示，如有必要，一个不同的大小将被使用。为单个索引定义指定的key_block_size值将覆盖表级别的key_block_size值

InnoDB存储引擎不支持索引级别的key_block_size

◇ index_type

某些存储引擎允许你在创建索引时指定索引类型，例如：

create index id_test on lookup (id) using BTREE;

下图显示了不同存储引擎允许支持的索引类型。在有多个索引类型的情况下，当没有显式给出索引类型说明符时，默认使用第一个。表中未列出的存储引擎不支持在索引定义中使用index_type子句

index_type子句不能用于全文索引和空间索引。全文索引的实现依赖于具体的存储引擎。空间索引固定被实现为R-Tree

如果你指定的索引类型对存储引擎无效，而另一索引类型对该存储引擎有效，且不影响查询结果，那么存储引擎将使用有效的索引类型

◇ comment 'string'

索引定义可以包含长达1024个字符的注释