MySQL数据库编程开发规范

数据库元素命名规范

长度约定：字段名，表名，视图名称等长度不能超过25个字符

表命名规范

• 使用有意义的英文单词来命名
• 表名使用小写英文字母表示
• 以字母开头，其余部分可以是字母、数字的任意组合
• 使用"_"来分割单词，如account_history
• 表名以一个模块名的缩写作为前缀，如 sys_user
• 子表名与父表名有相应的联系，如父表account, 字表account_extra_info
• 使用单数词，如user表，不要使用users
• 禁止使用数据库关键字作为表名

视图命名规范

规则："view_表A_表B"
其中"view_"作为前缀，以区别于表名，"表A、表B"表示组成视图的表名，如果表过多或其中若干个表名过长，则可以适当简化视图名
例如：view_ticket是从表ticket、ticket_source、ticket_price_policy三个表中提取数据而形成的一个关于票的完整信息。

字段命名规范

• 使用有意义的英文单词来命名
• 使用单数词
• 禁止使用数据库关键字作为字段名
• 避免字段名和表名重复
• 字段名用小写英文字母表示
• 使用"_"来分割单词

• 使用后缀来表示列的数据类型或特定含义，具体内容见下表

  后缀

  数据类型（MySQL）

  描述

  例子

  id

  bigint(20)

  通常用来做主键名称的后缀，这种主键没有实际意义，仅仅给出每一条记录的一个标识，该字段通常基于一个SEQUENCE。

  id

  *_no

  varchar(20)

  通常用来做序列号、流水号之类的后缀，有实际的意思，通常根据某些算法生成。

  serial_no

  *_time

  datetime/timestamp

  时间和日期类型

  created_time

  *is_

  tinyint(1)

  用来表示"Yes" or "No"，其值用 "1"、"0"表示

  is_released

  *_price

  biginit(20)

  金额或货币的表示，单位分

  ticket_price

  *_name

  varchar

  表示对象名称

  user_name

  *_age

  tinyint(3)

  表示年龄

  user_age

  *_remark

  varchar

  注释、备注等

   

  *_desc

  varchar

  对象的描述信息

  user_desc

• 字段保留名称

  字段名

  字段类型

  字段说明

  created_time

  timestamp

  记录创建日期，使用数据库时间

  updated_time

  timestamp

  记录最后更新日期，使用数据库时间

  is_deleted

  tinyint (2)

  逻辑删除标志

• 字符型。固定长度的字串类型采用char，长度不固定的字串类型采用varchar。避免在长度不固定的情况下采用char类型。如果在数据迁移等出现以上情况，则必须使用trim()函数截去字串后的空格。

• 数字型。数字型字段尽量采用int类型。

• 日期和时间。时间范围在1970/1/1—2038/1/9之外，使用datetime，其他一律使用timestamp

• 大字段。如无特别需要，避免使用大字段(blob，clob，image等)。

• 布尔类型。统一采用tinyint(1)表示，0代表false, 1代表 true

国际化信息表示

应用需要支持多国语言文字。所有数据库表中需要显示用的字段，都需要以 "_zh", "_en","_de"结尾作为字段名，以区分不同Locale的字段信息。

索引

普通索引

前缀为idx_。索引名称应是 前缀+表名+构成的字段名。如果复合索引的构成字段较多，则只包含第一个字段，并添加序号。表名可以去掉前缀。 

主键索引

前缀为idx_pk_。索引名称应是 前缀+表名+构成的主键字段名，在创建表时候用using index指定主键索引属性。 

唯一索引

前缀为idx_uk_。索引名称应是 前缀+表名+构成的字段名。 

外键索引

前缀为idx_fk_。索引名称应是 前缀+表名+构成的外键字段名。 

函数索引

前缀为idx_func_。索引名称应是 前缀+表名+构成的特征表达字符。 

蔟索引

前缀为idx_clu_。索引名称应是 前缀+表名+构成的簇字段。

存储过程

前缀为proc_ 。按业务操作命名存储过程

触发器

前缀为trig_ 。触发器名应是 前缀 + 表名 + 触发器名。

函数

前缀为func_ 。按业务操作命名函数

事件

前缀为event _ 。按event_功能_执行时间命名

设计规范

范式

考虑到实现的方便性可以有适当的数据冗余，但基本上要达到3NF。
如非确实必要，避免一个字段中存储多个标志的做法。如11101表示5个标志的一种取值。这往往是增加复杂度，降低性能的地方。

分表原则

对于数据量比较大的表，根据表数据的属性进行分区，以得到较好的性能。如按字段值范围进行范围分区；或对某个字段采用hash分区。

完整性设计原则

• 主键约束。采用自增id作为主键，类型为bigint(20)，字段或者组合字段必须满足唯一性要求的，必须指定唯一索引。 

• 外键关联。尽量避免使用外键。 

• NULL值。对于字段能否null，应该在sql建表脚本中明确指明，不应使用缺省。除非在具体业务中确实可以为null，否则一律不允许为null 

• Check条件。对于字段有检查性约束，要求指定check规则。 

• 触发器。尽量避免使用触发器。

• 注释。表、字段等应该有中文名称注释，以及需要说明的内容。 

索引设计

对于查询中需要作为查询条件的字段，可以考虑建立索引。最终根据性能的需要决定是否建立索引。最常用的SQL查询应用大量数据来分析执行计划。

• 对于复合索引，索引字段顺序比较关键，把查询频率比较高的字段排在索引组合的最前面。
• 合理创建复合索引，避免冗余和重复索引，(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c)。

• 索引中的字段数建议不超过5个。

• 单张表的索引数量控制在5个以内。

视图设计

视图是虚拟的数据库表，在使用时要遵循以下原则：

• 从一个或多个库表中查询部分数据项；
• 为简化查询，将复杂的检索或字查询通过视图实现；
• 提高数据的安全性，只将需要查看的数据信息显示给权限有限的人员；
• 视图中如果嵌套使用视图，级数不得超过3级；
• 由于视图中只能固定条件或没有条件，所以对于数据量较大或随时间的推移逐渐增多的库表，不宜使用视图；可以采用实体化视图代替。
• 除特殊需要，避免类似Select * from [TableName] 而没有检索条件的视图；
• 视图中尽量避免出现数据排序的SQL语句。

使用规范

综合

• Charset 为 utf8；Collation 为 utf8_general_ci。

• 没有特殊原因的情况下，使用 InnoDB 引擎。
• 数据访问层一律使用DAO方式，不得在程序使用直接的DML语句访问数据库
• 不推荐使用外键、触发器、存储过程
• 尽量避免多表关联查询，一个表的查询对应一个DAO方法，牵涉到多表查询的，可以由多个单表查询的DAO方法组成，如一个业务要根据一个用户id查询朋友信息，这个查询可以由2个DAO方法组成，一个方法查询朋友关系（user_id — friend_user_id），另一个根据id查询用户姓名。 
• 查询语句时尽量避免有对全表的扫描操作。
• 涉及到数据库数据的更改(Insert/Delete/Update)必须使用事务进行控制，并且必须有完整事务开始和提交/回滚机制。 

注释及代码规范 

• 数据库表及字段需要加上注释
• 类枚举字段必须在注释里表明所有枚举的文字说明
• 存储过程、触发器等需要加上功能注释、参数说明及业务逻辑说明等。
• 代码要有合理的缩进及换行， 增加可读性。按照DBA提供的模板进行

查询 

• 在表查询中，一律不要使用* 作为查询的字段列表，需要哪些字段必须显式写明 
• 在表查询中，必须有Where条件，除非此表为非增长表 
• 在表查询中，一次最多返回的记录条数不要超过1000条或记录内容不要大于1MB的数据。 
• 在表查询中，作Order By排序时，优先使用主键列，索引列 
• 多表关联查询时，优先使用Where条件，再作表关联，并且需要保证被关联的字段需要有索引。
• SQL语句中用IN代替OR，IN包含的值不应过多,应少于1000个。IN是范围查找,MySQL内部会对IN的列表值进行排序后查找,比OR效率更高
• 使用union all替代union
• 在SQL语句中，禁止使用前缀是%的like
• 能不用NOT IN就不用NOT IN，会把空和NULL给查出来
• WHERE条件中必须使用合适的类型，避免MySQL进行隐式类型转化，比如 select id from 表 where id='1'
• limit分页注意效率。Limit越大,效率越低。可以改写limit，比如：select id from tlimit 10000, 10;  =>  select id from t where id > 10000 limit10;

删除 

• 删除记录时，必须有Where唯一条件 
• 当有主从表时，要先删除从表记录，在删除主表记录

增加

• 批量增加时，使用batchInsert

修改 

• 修改记录时，必须有Where唯一条件