数据库元素命名规范
长度约定:字段名,表名,视图名称等长度不能超过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唯一条件