mysql数据库建表的基本规范

一、数据库设计
一般都使用 INNODB 存储引擎，除非读写比率 < 1%, 才考虑使用 MYISAM 存储引擎；其 他存储引擎请在 DBA 的建议下使用。

Stored procedure (包括存储过程，函数，触发器) 对于 MYSQL 来说还不是很成熟， 没有完善的出错记录处理，不建议使用。

UUID ()，USER () 这样的 MySQL INSIDE 函数对于复制来说是很危险的，会导致主备数据不一致，所以请不要使用。如果一定要使用 UUID 作为主键，让应用程序来产生。

不要使用外键约束，如果数据存在外键关系，请在程序层面实现。
采用 UTF8 编码。

二、数据库对象

 

 

1、创建表的时候必须指定主键，并且主键建立后最好不要再有数据修改的需求

mysql从5.5版本开始默认使用innodb引擎，innodb表是聚簇索引表，也就是说数据通过主键聚集（ 主键下存储该行的数据，索引指向主键值），如下图所示：

正是由于这种解构，如果后续对主键对应的值进行修改，就会导致索引节点的频繁分裂，性能会下降非常厉害。因此推荐开发的同事们使用和业务没有任何关联的自增id来做主键（切记不要使用uuid来做主键），此外也可以考虑使用其他的方式来生产自增的ID，比如使用Twitter的snowflake算法或者zk的DistributedAtomicLong来间接实现。使用自增主键而不是uuid做主键的优点主要有如下几点：

a、占用的数据量更小

b、数据顺序递增，不会导致索引节点的频繁分裂

c、数字类型比字符类型效率更高

正确招式：使用自增id作为主键

2、创建表选择字段的时候，在符合业务需求的情况下尽量小，数据类型尽量简单

数据类型尽量简单很好理解，比如，使用数字类型要比使用字符类型效率更高，因为不涉及到校对规则和字符集。而字段尽量小，说的是在满足业务需求的情况下，尽可能小。这么做的好处是：更小的字段类型占用更少的内存，占用更少的磁盘空间，占用更少的磁盘IO，以及占用更少的带宽。举个例子，如果一个varchar(50)的字段，不管你存储了几个字符，在查询的时候仍然需要申请50 byte的内存

3、创建表的时候显式申明存储引擎

这个没什么说的，使用什么引擎就申明什么引擎，防止被默认。有句话说得好：如果你不选择，那你就被选择，被选择的结果不一定是你自己想要的。命运还是要掌握的自己手里。

正确招式：显式申明ENGINE=xxxx；

4、创建表的时候显式申明字符集

如果不不想被乱码困扰，就老老实实的申明字符集，还是那句话：如果你不选择，那你就被选择，被选择的结果不一定是你自己想要的。命运还是要掌握的自己手里。

正确的招式：显式申明DEFAULT CHARSET=xxxx；

5、创建表的时候对经常要查询的列添加索引或者组合索引

索引直接影响后面的查询性能，尤其是数据量越大的时候，影响越明显。作为一个从事DBA生涯超过5年的DBA，遇到过无数次由于没有添加索引，导致的线上故障，请各位开发和DBA的同事谨记，一定要对经常要查询的列添加索引或者组合索引，防止线上事故的发生。

正确招式：建表的时候就添加对应INDEX

6、创建表的时候对字段和表添加COMMENT

这个主要是方便后续的维护，之前在小公司做DBA的时候，接手数据库时由于没有任何的COMMENT，导致完全不知道对应的表和字段是做什么用的，不得不经常麻烦开发的同事解释。这个小技巧用一个经典的广告词来解释就是：你好，我也好。

正确招式：对列和表都添加COMMENT做详细说明；

7、创建表的时候不要添加drop操作

有的开发同事在创建表之前喜欢添加, DROP TABLE IF EXISTS TABLEXXXX; 然后再来个CREATE  TABLE，建议不要这么干，因为我之前遇到过由此带来的线上故障。将线上正在使用的表drop掉了。建议大家创建表的时候修改为：CREATE TABLE IF NOT EXISTS TABLEXXX .......；

正确招式：CREATE TABLE IF NOT EXISTS TABLEXXX .......；

8、创建表的时候，字段尽量不要为NULL

解决办法就是设置字段为NOT NULL，并设置字段的默认值。字段尽量不要为NULL的原因如下：

a、NULL需要占用额外的空间存储；

b、进行比较的时候会更复杂，还会导致你select (column)的时候不准确

c、含有NULL值的列，会对SQL优化产生影响，尤其是组合索引中

具体NULL会带来的问题大家可以查阅：https://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html

正确招式：NOT NULL DEFAULT 'xxxxx'；

附上1个机器简单的标准SQL 范例，大家创建表的时候可以参照下面的范例SQL来创建，范例如下：

CREATE DATABASE IF NOT EXISTS `dev_ops_db`;

CREATE TABLE  IF NOT EXISTS `dev_ops_db`.`monitor_table_holiday` (

`id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',

`holiday_date` date NOT NULL default '1999-01-01' COMMENT '节假日日期',

`holiday_name` varchar(36) NOT NULL default '' COMMENT '节假日名称',

PRIMARY KEY (`id`),

KEY `holiday_date` (`holiday_date`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='节假日数据表';

 

 

-- 数据库建表语句的规范小结

建表语句的规范：
1.字段的设计
    A. 数据类型尽量用数字类型，数字类型的比字符类型的要快很多。
　　B. 数据类型尽量小，这里的尽量小是指在满足可以预见的未来需求的前提下的,但是有不能太小,
   上次监控系统里面的表 mon_tair_stat_detail_2012_1 的data_size 和 use_size定义的是int(15) 实际上最大长度是int(11)  只有bigint才有(15).
   所以在建表的时候一定要预估这个字段最大的长度到底是多少。
　　C. 尽量不要允许NULL，除非必要，可以用NOT NULL+DEFAULT 的值来代替。
　　D. 少用TEXT和IMAGE，二进制字段的读写是比较慢的，而且，读取的方法也不多，大部分情况下最好不用。

2.字段名字的定义
  A. 对字段名字的命名也要遵循一个规范。比如说时间: 目前我们定义时间的时候 都是create_time, modify_time,以后建表的时候,都按照这个标准来,就不要写成createtime这样的。
  B. 布尔类型的值均以 is、has、exist 或者 can开头。
  C. 记录行的状态都用status, 逻辑删除的标识用is_deleted。
  D. 新建的表与之前的表的字段有相似或者相同的字段，字段的名称和类型也必须相同，切记不能随意的重新命名，例如:token_md5 就不要命名成tokenmd5了。
  E. 每个字段的COMMENT必须写清楚，枚举类型必须写清楚每个值到底是什么意思。
  F. 表名 字段名字统一用小写。
  G.索引名称统一用字段名称，多个字段的索引加_ 比如 (uid,type)  索引名就是 uid_type。
  H.字段名字不要用驼峰命名法 比如create_time 不要写成CreateTime.
  I.字段命名关键字统一用下划线"_"分割，一般采用简写，关键字要准确，不能有歧义。
  J.字段的排列顺序：自增列、int类型的、varchar类型、时间类型、状态类型（status,is_deleted）。基本原则是：主要内容在前，次要内容在后，不为空的在前，可以空的在后面。
  K.时间类型尽量用 timestamp 4个字节,而不用datetime 8个字节。只用表示日期的字段用date类型。
  L.时间类型自动修改，比如modify_time 在记录被修改的时候回被自动更新成当前的时间： `modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP；
  而create_time在记录被修改的时候则不必自动更新成当前的时间：`create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'