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个字符的注释