mysql 建表字段长度_mysql表操作之建表时的列参数

CREATE TABLE可以创建出给定名称的数据表，使用该语句必须拥有CREATE权限。默认情况下，表会被创建到默认的数据库中，并使用InnoDb存储引擎。如果表存在或没有默认数据库或默认数据库不存在，此语句会报错。

MySQL对于表的个数没有限制，但是部署服务的文件系统可能对可展示表的个数有一定限制，特殊的存储引擎也可能有特殊规定，InnoDb限定上线为40亿个表。CREATE TABLE语句可以在创建表时指定表的所有参数。下面是几个建表语句概念介绍。

1 表名与IF NOT EXISTS

表名可以指定为db_name.tbl_name用于在指定的数据库db_name中创建数据表tbl_name。该方式会忽略默认数据库，即使默认数据库是存在的。如下图所示：

如果要使用反引号，需要将数据库名和表名分割开，如果不分隔开，会默认为一个整体的表名。如下演示了默认数据库为test，需要在test1中创建test1表时反引号的正确方法以及错误方法：

IF NOT EXISTS会避免如果需要创建的表已经存在那么重复创建会报错的情况，但是无法确认已经存在的表和需要创建的表的表结构是相同的。

2 临时表

可以使用TEMPORARY关键字来创建一个临时表，临时表仅仅会存在与当前会话中，在会话结束后会like删除。

3 表的克隆与复制

LIKE 可以使用CREATE TABLE ... LIKE来根据一个已有表的定义创建一个空表，该表会完全复制已有表的结构定义与参数设置，如要在test库创建一个与test1库中test1表完全相同表结构的名为test的空表，如下：

使用AS + 查询语句会将查询语句的查询结果形成一个新的数据表，如将test中id>10的数据存放到test_as表中，如下：

4 列类型与参数的使用

4.1 数据类型

数据类型对应表中类的数据类型。11数据类型中详细介绍了如何使用数据类型的语法。需要注意以下几点：

1> 一些参数不是适用于所有类型的，AUTO_INCREMENT只适用于整形和浮点型数据，DEFAULT不可用于BLOB、TEXT、GEOMETRY和JSON类型；

2> 字符数据类型(CHAR、VARCHAR、TEXT、ENUM、SET及其他)可以指列字符集。CHARSET与CHARACTER SET等效，校验字符集可以使用COLLATE参数指定。10字符集、校验、解码中有详细介绍。MySQL5.7对于字符列的长度指字符个数，对于BINARY和VARBINARY列指字节数。创建一个拥有name列的表test，name列使用utf8字符集，并使用utf8_bin进行校验，如下所示：

3> 对于CHAR、VARCHAR、BIANRY和VARBINARY类型的列创建索引时可以使用最左长度 col_name(length)语法来制定一个索引的长度。BLOB和TEXT列设置索引时必须指定索引的长度。索引长度对于字符类型列来说指字符个数，对于二进制字符指字节数。只有InnoDb和MyIsam引擎才支持BLOB和TEXT列创建索引。对于一个非唯一索引列，如果严格的SQL模式被设置时会直接报错，如果没有社会严格的SQL模式，索引被键减少到列的最大长度并且会产生警告；对于唯一索引列而言，因为缩减索引长度可能会导致非唯一向的插入，这样就打破了唯一索引原则，所以会直接报错。

4> JSON类型的数据列是不能被设置索引的，如果非要如此可以从JSON列中提取相应来生成列进行索引达到该效果。

4.2 NULL 与 NOT NULL

如果没有指定空与非空，则默认为空。在MySQL5.7中只有InnoDB、MyIsam和Memory存储引擎才能支持索引列中含有NULL值，其他情况下必须把索引列指定为NOT NULL。

4.3 DEFAULT

DEFAULT用于设置列的默认值。如果SQL模式中设置了NO_ZERO_DATE或NO_ZERO_IN_DATE，对于日期列的默认值包含了0值，那么CREATE TABLE会报错；如果没有设置该模式，则会产生警告。例如如果开启了NO_ZERO_IN_DATE，在建表时日期默认列设置成2010-00-00时会报错，如下：

4.4 auto_increment

整型或浮点型数据类型的列可以添加AUTO_INCREMENT自增参数。当向表中插入数据时赋值为0或NULL时，这些列的值会被设置成符合自增规则的数字。默认的规则为value+1，value代表当前表中自增列的最大值。AUTO_INCREMENT的起始值为1，但是可以设置为0。在插入数据行使用自增规则生成数据后，可以通过函数LAST_INSERT_ID()或MYSQL_INSERT_ID()获取生成的值。如果sql模式设置了NO_AUTH_VALUE_ON_ZERO，可以在自增列存放0值。

每个表中只允许存在1个自增的列，并且该列上必须有索引，而且不能这是该列的默认值。如果自增列中只含有正数值性能会很好，插入一个负数会被认为成插入了一个极大的正数，这样做的目的是确保从正数到负数转换不会出现溢出问题并且不会意外的造成包含0的自增列。对于MyIsam引擎，可以在联合索引中将自增列指定为索引的普通列。

为了是MySQL在某些ODBC应用中更强大，可以使用SELECT * FROM tbl_name WHERE auto_col IS NULL来获取AUTO_INCREMENT。使用该方法时，sql_auto_is_null变量必须不能设置成0。

4.5 COMMENT

COMMENT可以想表列添加1024字符长度的备注，可以使用SHOW CREATE TABLE tbl_name或SHOW FULL COLUMNS IN tbl_name语句查看这些信息。

4.6 COLUMN_FORMAT

NDB集群中使用较多。在NDB集群中可以使用COLUMN_FORMAT为个别列指定列格式，可以允许的列格式有FIXED、DYNAMIC和DEFAULT。FIXED用于固定宽度存储，DYNAMIC可以变宽存储，DEFAULT通过数据类型决定使用变宽存储或定宽存储。在NDB集群7.5.4后，DNB表默认列格式使用FIXED。NDB的最大固定宽度FIXED的值为8188字节。MySQL5.7中会忽略该参数。

4.7 STORAGE

对于NDB，可以通过STORAGE子句指定该列是否保存在磁盘或内存中，STORAGE DISK会将列保存在磁盘上，STORAGE MEMORY子句则会保存在内存中。NDB的默认存储为STORAGE MEMORY。对于NDB之外其他而言，该语句不适用。CREATE TABLE语句必须包含TABLESPACE子句，如下所示：

4.8 GENERATED ALWAYS

用于指定生成列，生成列是可以设置索引的。InnoDb支持虚拟生成列上使用普通索引。