MySQL系统学习二：创建表语法之create table

MySQL中创建表并管理表是使用MySQL运行业务的开始，你得创建表才能往里写数据不是？MySQL的创建表和管理表，也即create table和alter table。创建表的语法细节较多，管理表的语法又涉及到字段本身和（或）索引修改，固将create table和alter table分为两个单元，深入学习。

MySQL的create table语句英文版帮助文档：

MySQL5.5版本：https://dev.mysql.com/doc/refman/5.5/en/create-table.html

MySQL5.6版本：https://dev.mysql.com/doc/refman/5.6/en/create-table.html

一、create table语句

create table的所有可能的结构就不再详细列出，一般最常用的语句结构为：

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
	[(create_definition,...)]
	[table_options]
	[partition_options]
	select_statement

TEMPORARY：该关键字表示用MySQL create table新建的表为临时表，此表在当前会话结束后将自动消失。临时表主要被应用于存储过程中；尚未学习到存储过程，暂时不理这个关键词。

IF NOT EXISTS：实际上是在建表前加上一个判断，只有该表目前尚不存在时才执行create table操作。用此选项可以避免表已经存在无法再新建的错误。

tbl_name：你所要创建的表的表名。该表名必须符合标识符规则。通常的做法是在表名中仅使用字母、数字及下划线。

create_definition：是创建数据表的核心关键所在，定义了表的字段和字段的属性，即定义了表的列和各列的属性。create_definition表示的作用直接的说明就是：

定义字段、字段的类型、字段的默认值、字段的字符集、字段的编码集和字段注释（字段评注）等字段相关的内容。
定义字段的索引，key、index、primary key之类的；

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]

其中column_name可选如下：

1、数字类型

常用数字类型有：tinyint、smallint、mediumint、int（integer）、bigint、float、double（real）、decimal（numeric）

其中整形常常用作主键字段，主键字段又以int型最重要。

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

微小范围的整形，占用存储空间1字节（byte），有符号型范围：-128（2^7）至127（2^7-1），无符号型（unsigned）范围：0至255（2^8-1），M值默认为4

留意tinyint(1)语句中括号内的数字，仅表示该字段显示的位数【同理smallint、mediumint、int、bigint类似】；TINYINT[(M)]中，M默认为4；这里的4仅表示这个tinyint字段的显示位数是4位，若该字段指定了ZEROFILL属性，那么查询该字段时不够4位长度的整数将补前导0至4位长度；若指定M为1，也指定了ZEROFILL，字段值位数大于1将显示正常位数。tinyint也可以用来表示布尔值（boolean），一般零值表示false，非零值表示true。

SMALLINT[(M)] [UNSIGNED] [ZEROFILL]

小范围的整形，占用存储空间2字节（btye），有符号型范围：-32768（-2^15）至32767（2^15-1），无符号型（unsigned）范围：0至65535（2^16-1），M值默认为6（记忆技巧：最大值为5位，让最大值也补齐一个0，那么就是6了，tinyint、mediumint、int规则类似，bigint无符号型最大值位数为20位，而有符号型最大值位数为19位，bigint的M值默认为20有点差异~）

MEDIUMINT[(M)] [UNSIGNED] [ZEROFILL]

中范围的整形，占用存储空间3字节（byte），有符号型范围：-8388608（-2^23）至8388607（2^23-1），无符号型（unsigned）范围：0至65535（2^24-1），M值默认为6

INT[(M)] [UNSIGNED] [ZEROFILL]

同义词：INTEGER

大范围的整形，占用存储空间4字节（byte），有符号型范围：-2147483648（-2^31）至2147483647（2^32-1），无符号型（unsigned）范围：0至4294967295（2^32-1），M值默认为11

BIGINT[(M)] [UNSIGNED] [ZEROFILL]

超大范围的整形，占用存储空间8字节（byte），有符号型范围：-9223372036854775808（-2^63）至9223372036854775807（2^63-1），无符号型（unsigned）范围：0至18446744073709551615（2^64-1），M值默认为20

----

上述整形中的M值只是指定显示宽度，并不是限定了存储值的位数；当指定了ZEROFILL属性后，就可以看到指定M值的显示宽度了，比如int型指定了M值为6，插入一个数字234，并且指定了ZEROFILL属性，那么查询出来的数字是：000234

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

单精度浮点数，占用存储空间4字节（byte），允许的理论值是-3.402823466E+38到-1.175494351E-38、0和1.175494351E-38到3.402823466E+38，具体范围还依赖于硬件支持。有符号型和无符号型范围仅正负数的差异，经过测试有符号型也可以插入最大值3.402823466E+38，无符号型当然无法插入负值了。

M为单精度小数的总位数，D为小数点后的小数位数；如果M和D被省略，根据硬件允许的限制来保存值；单精度浮点数精确到大约7位小数位。

注意：还存在FLOAT[(p)]类型的标记，当0<=p<=24时，就是普通的无M和（或）D的float类型；当25<=p<=53时，就是普通的无M和（或）D的double类型--存储空间占用在变化，精度和范围在变化。

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

同义词：REAL

双精度浮点数，占用存储空间8字节（byte），允许的理论值是-1.7976931348623157E+308到-2.2250738585072014E-308、0和2.2250738585072014E-308到 1.7976931348623157E+308，具体范围还依赖于硬件支持。

省略M和D参数时，插入的小数将按实际精度和标度保存（未超过double类型的极限精度和标度范围），当指定M和D后，整数部分的长度和小数部分的长度之和不得大于M；当插入的小数位数超过D限制时，采用小数位四舍五入规则截断。

---

float、double两种浮点数中当插入的值中的小数长度大于D值时，采用四舍五入规则存储，所以浮点数存储的是一个不精确的数据，若需要精确的存储有限位数的小数可以使用DECIMAl（NUMERIC）类型。

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

同义词：DEC、NUMERIC、FIXED

DEC[(M[,D])] [UNSIGNED] [ZEROFILL]、NUMERIC[(M[,D])] [UNSIGNED] [ZEROFILL]、FIXED[(M[,D])] [UNSIGNED] [ZEROFILL]

定点数的精确小数，MySQL中float、double存储的可能只是一个近似值，而那些有确切小数位的精确小数，则可以使用decimal类型存储。M值是小数位数的总数，例如1.1415这个精确的小数中M值为5，D为4；M值最大65，D值最大30；当省略M值时M默认为10，当省略D值时D默认为0；所有DECIMAL运算（加减乘除）中采用65位精度执行。

BIT[(M)]

存储比特位的字段类型，其值只能是由0和1构成的二进制数；

比特位Bit是计算机中最小的单位，以0或1来表示比特位的值；8bit = 1byte、1byte就是1B、1KB = 1024B
比特位字段插入值时，其值采用b'value'形式书写，其中value为二进制数，单引号和前导符b必须，这样避免十进制数在插入时mysql处理导致混淆。BIT[(M)]中，M默认为1，范围1至64

数字类型CREATE语句举例

---下述示例中关键词应该使用大写
create table if not exists num_type_test(
	id int(11) not null auto_increment,
	tiny_id tinyint(1) unsigned not null,
	small_id smallint(2) null,
	medium_id mediumint unsigned not null default 1,
	big_id bigint(20) unsigned not null,
	float_num float(11,5) not null,
	double_num double(20,6) unsigned not null,
	decimal_num decimal(20,2) not null default '0.00',
	primary key(`id`)
)engine=innodb default character set utf8;

2、字符串类型

CHAR(M) [BINARY| ASCII | UNICODE]

固定长度的短字符串，M为保存字符的长度，范围为：0至255，当保存的长度没有达到M值指定的长度时，在右侧补充空格到M长度后存储，这里的固定长度是相对于存储方式来说，实际读取出来的内容右侧是否有空格依据MySQL模式PAD_CHAR_TO_FULL_LENGTH是否激活来确定，默认没有激活读取出来的右侧是不带空格的----存在这样一种情况，插入的字符串右侧有空格，读取出来时无论PAD_CHAR_TO_FULL_LENGTH模式是否激活，右侧的空格将丢失（或空格数目变化）。

BINARY属性是指定列字符集的二元校对规则的简写，用于排序和比较基于数值的字符值；

指定ASCII属性，它分配latin1字符集；

指定UNICODE属性，它分配ucs2字符集；上述3种属性很少使用。

VARCHAR(M) [BINARY]

varchar类型是一个变长类型字段，M的取值范围为0到x个字符，其中x字符转为字节后，最大的x字节限制为：65535，这里的x与存储的字符类型有关，例如表character设定为utf8，那么这里的M最大值为：21845 - 2 [-1]，（即65535/3 -2 [-1]，一个utf8字符占3个字节），-2和可能的-1是因为varchar字段存储时会使用1至2字节的存储位存储变长字段实际的长度即长度标示位、和可能的1个字节的NULL标示位，其中长度标示位具体占用存储空间依据声明的M值范围来确定，当M值小于等于255时长度标示位占用1字节，当M值大于255时长度标示位占用2字节，当该字段可为null时就需要额外的1个字节来存储NULL标示位；实际申明M的最大取值依据存储编码、是否可为NULL不同而不同。去除长度标示位和NULL标示位，最大的有效的字节长度为：65532字节。

varchar(M)中的M取值范围为0至65535比较笼统，这里面一定要区分开：字符长度和字节长度。建表时指定的varchar(M)中的M是指字符长度，比如指定M为128，那么这个字段最大可存储字符长度为128的字符串，无论多字节的中文还是单字节的英文。

BINARY(M) 和 VARBINARY(M)

BINARY类型类似于CHAR类型，但保存的是二进制字节字符串而不是字符字符串。也即binary类型保存的是字节字符串。M的长度与CHAR一致，只不过这里M限定的是字节字符串的最大长度，即0至255；填充到固定长度BINARY类型右侧使用0x00（0byte），0x00 < space

VARBINARY类型类似于VARCHAR类型，与BINARY和CHAR的区别一致，VARBINARY保存的也是二进制字节字符串（而不是非二进制的字符字符串）。M的长度与VARCHAR一致，只不过这里的M限定的是二进制字节字符串长度。

BLOB类型：TINYBLOB、BLOB[(M)]、MEDIUMBLOB、LONGBLOB

BLOB类型是一种用于存储二进制大对象的字段类型，例如用于存储图片文件本身的二进制数据。BLOB类型的值被当做一种二进制字符串（byte string），没有字符集、校队集，排序压缩算法直接依赖于存储的二进制字节字符串表示的数字值本身。

BLOB[(M)]可以给出最大的存储字节容量M，指定M值后MySQL将分配最小的、足以容纳M字节长的值的BLOB类型。BLOB类型不能设置默认值。

TINYBLOB最大存储空间255B、BLOB最大存储空间65k、MEDIUMBLOB最大存储空间16M、LONGBLOB最大存储空间4G；从理解角度来看：可以将四种BLOB类型当做存储空间容量足够大的VARBINARY类型来理解。

TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT

存储字符字符串类型的文本字符类型。

TINYTEXT最大长度为255(2^8-1)个字符；TEXT最大长度为65535(2^16-1)个字符；MEDIUMTEXT最大长度为16777215(2^24-1)个字符；LONGTEXT最大长度为4294967295(2^32-1)个字符。

TEXT类型具有字符集、校队集设定属性。具体原型如下：

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]

如果TEXT类型字段被指定BINARY属性，那么该列将被指定为二进制的排序集。

ENUM(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name]

枚举类型，其值是只能有一个值的字符串，其值从待选值列'value1'，'value2'，...，NULL中或特殊错误值（空字符，empty string）中选出，ENUM待选值在内部使用整数表示。ENUM列理论上最多可以有65535个不同的待选值（实际上可能低于3000个待选值）。枚举类型默认值的问题：当没有显式指定枚举类型的默认值时，当该列定义可为null时，null值作为默认值，当该列定义不可为null时，待选值序列的第一个值作为默认值。

枚举类型的待选值其内部是带有从1开始的索引的，特殊错误值的索引为0，指定可为NULL的列中NULL值索引为NULL；

关于枚举类型待选值内部索引对应关系，假设有如下枚举类型字段声明（没有指定NOT NULL）：

 ENUM('Mercury', 'Venus', 'Earth')

其内部索引对应关系图为：

索引值与待选值的对应关系之所以列出来，是因为在enum列中使用sum、avg等数字数学函数时，参与运算的是其索引值。enum列排序时也是使用其待选列值的索引值来排序的，所以定义枚举类型列值越靠后，在该列参与排序时其值越大。另外存在0对应特殊错误值的索引关系，所以可以通过enum_column = 0的条件来查询该列插入值出现异常的记录（即插入的值不包含在待选值序列中），也可以通过索引数字直接去查询记录，比方说上图中，可以通过enum_colum=1查询该列值为'Mercury'的记录。

特殊的：当定义的待选值列是数字类型的（数字类型的字符），那么在插入或查询枚举类型值时会引起“误解”。当插入或查询时使用纯数字值，则当做索引来处理；若插入或查询时使用字符串数字（即带单引号的数字），则优先核对待选序列中是否存在该数字类型的字符，有则使用该序列值，没有则当做数字索引去处理。

由定义结构可以看出：枚举类型是可以指定编码字符集和校队集字符集的。

SET(value1,value2,value3,...) [CHARACTER SET charset_name] [COLLATE collation_name]

SET集合类型用的较少，详细的参考文档为：https://dev.mysql.com/doc/refman/5.6/en/set.html

集合类型的字符串对象，可有0个或多个字符串对象的集合类型。每个值必须来自值'value1'，'value2'，... SET列最多可以有64个成员，SET值在内部用整数表示。创建set集合类型列时，若指定了空值的待选序列值，内部实现中将删除这个空值待选值。

如下：

SET('one', 'two') NOT NULL
##那么可以插入的值有：
''
'one'
'two'
'one,two'

在检索集合列时，其内部将会把待选序列当做（处理成）小写字母；同理集合类型也是可以指定编码字符集和校队字符集的。

3、日期类型

DATE、DATETIME[(fsp)]、TIMESTAMP[(fsp)]

DATE、DATETIME、TIMESTAMP三者是相关联的。

DATE类型：年月日的日期类型，YYYY-MM-DD格式，范围为1000-01-01到9999-12-31，date类型字段插入值时可以省略value的引号。

DATETIME类型：包含年月日时分秒的日期时间类型，YYYY-MM-DD HH:MM:SS格式，范围为1000-01-01 00:00:00到9999-12-31 23:59:59

TIMESTAMP类型：包含年月日时分秒的日期时间类型，与UTC关联；YYYY-MM-DD HH:MM:SS格式，范围为： UTC标准年月日时分秒日期时间中的1970-01-01 00:00:01到2038-01-19 03:14:07

从MySQL5.6.4开始，DATETIME和TIMESTAMP两种类型可以在秒级别数据之后附带6个小数，用以记录比秒级别更精确的时间日期时间点。即YYYY-MM-DD HH:MM:SS[.fraction]；DATETIME这种形式的范围为：1000-01-01 00:00:00.000000到9999-12-31 23:59:59.999999；TIMESTAMP这种形式的范围为：1970-01-01 00:00:01.000000到2038-01-19 03:14:07.999999

从MySQL5.6.5开始，TIMESTAMP和DATETIME两种类型都可以设定为自动初始化当前时间和（或）记录有更新时自动更新该字段值为当前时间，在MySQL5.6.5之前仅TIMESTAMP类型支持，且一个表中最多仅能指定一个具有当前时间作为默认值和（或）记录更新时字段内容自动更新为当前时间的字段。

参考文档：https://dev.mysql.com/doc/refman/5.6/en/timestamp-initialization.html

完整的定义描述为：

##DATETIME类型
dts datetime(fsp) NOT NULL DEFAULT CURRENT_TIMESTAMP(fsp) ON UPDATE CURRENT_TIMESTAMP(fsp)
##TIMESTAMP类型
tst datetime(fsp)  NOT NULL DEFAULT CURRENT_TIMESTAMP(fsp) ON UPDATE CURRENT_TIMESTAMP(fsp)

当定义为当前时间为默认值，则插入null值会自动写入当前时间。依据sql模式的设置不同，定义TIMESTAMP类型时不指定默认值、更新时是否自动更新会依据不同情况不同处理，例如第一个TIMESTAMP字段不显式指定默认值和更新时动作，MySQL会按默认的处理方式自动为该字段添加当前时间为默认值和记录更新时自动更新为当前时间，具体处理情况请参考上述英文文档。

fsp取值范围为0至6，表示秒级别数据之后的小数点位数精确度，默认fsp为0（这与标准的SQL存在差异，标准的SQL中默认的fsp为6）；这当中又有小数点截断的规则：四舍五入。

##例如：
DATETIME(3)，
##则插入的值类似如下格式：
2016-4-22 13:31:58.001

TIME[(fsp)]

时分秒格式的时间；格式HH:MM:SS，取值范围：-838:59:59到838:59:59，实际的格式为[H]HH:MM:SS；fsp参数取值范围0至6，默认为0，表示秒级单位的小数位数长度。

YEAR

4自然数字构成的年份类型，存储空间占用1字节（byte）的年份类型，YYYY类型，存在year(2)和year(4)，但mysql5.6.6之后year(2)已处于移除状态；YEAR(4)取值范围：1901到2155

插入时数字（或字符串）的1-99之间，则1至69转换为2001至2069；70至99则转换为1970至1999，这种规则也适用于date、datatime和timestamp类型；插入0或00的数字（或字符串），内部存储并显示为0000

日期类型示例

create table timer_test(
	ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	ts1 timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	dt datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	date date null,
	time time not null default 0,
	year year null	
)engine=innodb default character set utf8;