关于MySQL 建表的一些建议

MySQL作为关系型数据库，被用来存储持久化的数据，避免不了需要创建表。如果没有利用ORM(对象关系模型)来自动创建表，则需要开发者通过图形界面（Navicat）或者手写MySQL语句。

由于在生产环境下，我们对MySQL数据库的操作通常是通过命令行进行操作，因此，建议建表的时候也手写MySQL语句（不建议用图形界面建表）。

1、添加注释的格式

在编写MySQL语句时，我们通常会被要求加上注释，推荐的注释格式为：

-------------------------------------------------
-- 创建表 tb_example 创建示例表
-------------------------------------------------
create table tb_example(
   ….
)…

理由：这种注释格式MySQL解析器也会认为是注释的，然后，会正确执行你的create table相关语句。我们通常将要更改的sql语句整理到一个1.0.0.sql文件中，然后，你只需要登录MySQL客户端，输入：source /文件目录/1.0.0.sql。因此，这样的注释可以达到一举两得的目的。

2、指定表的引擎和缺省的编码格式以及该表的说明

示例语句如下：

create table tb_example(
	….
)engine=innodb default charset=utf8 comment ‘这是一张示例表’;

理由：上述显示的指定使用的引擎为innodb，在5.6之前默认的引擎是MyISAM，而现在主流推荐性能较好的是innodb，具体参考：高性能MySQL 这本书。上述也指定了缺省字符集为utf8，还有不要漏掉使用comment来注释下这张表的用途，便于其他开发人员了解这张表的意图。

3、一个经典的建表语句示例并分析

示例语句如下：

create table tb_example(
	id int unsigned not null auto_increment comment '主键id',
	app_id int unsigned not null default '0' comment '应用id',
	app_name varchar(255) not null default '' comment '应用名',
	click_cnt int unsigned not null default '0' comment '点击数',
	disp_cnt int unsigned not null default '0' comment '展示数',
	status tinyint unsigned not null default '0' comment '状态： 1-启用；0-禁用',
	click_rate float unsigned not null default '0' comment '点击率=点击数/展示数',
	note text not null default '' comment '备注',
	date varchar(255) not null default '统计日期',
	mtime int unsigned not null comment '修改时间',
	ctime int unsigned not null comment '创建时间',
	primary key ("id"),
	unique key unique_date_appid ("date","app_id"),
	key idx_date ("date"),
	key idx_ctime ("ctime")
)engine=innodb default charset=utf8 comment '这是一张示例表';

需要注意的点如下：

1）所有的字段要加commet注释

由于我们创建的表也可能被其他人所用，因此加上comment注释，其他人或者我们自己可以通过命令show create tabletb_example来查看表的结果信息。

2）、int 符号确定

如果确定整形为非负数，就将int设置为无符号型的，即int unsigned，可以多一半的值范围，又能避免插入负数。int设置为无符号的场景在很多场合都使用。

3）、不定长varchar的长度设置

对于不定长字符串varchar如果不确定字符串长度（且知道字符串长度小于255）可以设置为vachar(255)，此时，存储空间只比正常的多一个字节（与设置varcahr(10)额外存储的空间是一样的），又能够最大限度的利用varchar的特定。注意：超过255则用于存储该长度的空间会多于一个字节。具体参考：高性能MySQL。

4）、有限状态的类型设置

对于表示状态数值的数据类型建议设置为tinyint unsigned（只占用一个字节的空间） 可以表示0到255的范围。注意：无需用int，占用四个字节的空间。

5）字段创建时间ctime和修改时间mtime

每个表中尽量加上字段创建时间ctime和字段修改时间mtime，便于后期排查问题，知道该条记录是何时插入，何时修改。

6）、日期时间设置为int时的查询

这里的ctime表示创建时间，用的是unix时间戳来存储，但是不能设置缺省值unix_timestamp()，我们在实际查询的时候，可以使用from_unixtime(ctime)来将unix时间戳转为date日期格式。

示例如下：

insert into tb_example(app_id, app_name, click_cnt, disp_cnt, status, click_rate, note, date, mtime, ctime) values (1, 'timApp', 12, 6, 1, 0.50, '这是备注', '20170815', 1502796713, 1502796713);
select *, FROM_UNIXTIME(mtime), FROM_UNIXTIME(ctime) from tb_example;

效果如下：

7）所有的字段尽量设置为not null。

8）尽可能的设置default的值

比如：app_name 中通过设置default ‘’，click_cnt设置default ‘0’。

9）将有可能要进行查询的字段设置为索引

比如：key idx_date(“date”)，这里因为可能会查询一段时间内的数据，因此添加字段“date”的索引。注意：一般索引的命名规则是idx_字段名

10）设置唯一索引

根据业务确定哪些字段或者字段组合的值是唯一的，则将该字段或字段组合设置为唯一索引。

比如：unique keyunique_date_appid(“date”, “app_id”) 这里因为根据业务我们只要对于具体的某一天具体的某个应用的记录一定只能有一条，因此，设置联合唯一索引可以防止改天对应的该应用的数据重复插入。注意：唯一索引的命名规则为：unique_字段1_字段2。

一个小知识点：

由于我们添加了唯一索引unique_date_appid，如果插入重复的date 和 app_id组合，则会报联合索引重复错误，比如，再次执行：

insert into tb_example(app_id, app_name, click_cnt, disp_cnt, status, click_rate, note, date, mtime, ctime) values (1, 'timApp', 12, 6, 1, 0.50, '这是备注', '20170815', 1502796713, 1502796713);

提示：

但是，我们在很多场景时，不希望执行SQL语句报错，而是提示重复插入，此时，可以利用insert ignore into语句，将上述SQL语句改为：

insert ignore into tb_example(app_id, app_name, click_cnt, disp_cnt, status, click_rate, note, date, mtime, ctime) values (1, 'timApp', 12, 6, 1, 0.50, '这是备注', '20170815', 1502796713, 1502796713);

提示：

我们可以通过返回结果result进行判断，如果result为0，则表示重复插入。