DB 设计规范汇总

目录

库表设计原则

1 库表类型选择规范

2 表结构设计规范

3 字段设计规范

4 索引规范

5 SQL 规范

6 高危 SQL 规范

命名规范

1 库命名规范

2 表命名规范

3 字段命名规范

4 索引命名规范

---

库表设计原则

1 库表类型选择规范

库表类型	分库分表说明	库表范例
单库单表	数据量不大，日新增记录数少于1W，历史数据无需定时归档	db.t_table
单库单表	数据量不大，本身和分库分表在一起，放在第一个库	db_0.t_table
单库多表	数据量中等，后续不存在拆库的操作，只是要将数据分布到多表中	db.t_table_0 ~ db.t_table_n
单库多表	数据量中等，后续不存在拆库的操作，只是要将数据分布到多表中 ,本身和分库分表在一起，放在第一个库	db_0.t_table_0 ~ db_x.t_table_n
多库多表	数据量大，后续可能会做垂直拆分，分库分表数量建议找DBA做进一步评估	db_0.t_table_0 ~ db_n.t_table_n
年表	数据按照年存放，无需定期归档或定期清理	db.t_table_yyyy or db_0.t_table_yyyy
月表	数据按照月存放，无需定期归档或定期清理	db.t_table_yyyymm or db_0.t_table_yyyymm
天表	数据按照天存放，无需定期归档或定期清理	db.t_table_yyyymmdd or db_0.t_table_yyyymmdd

2 表结构设计规范

1. 必须明确申明主键；
2. 必须明确申明存储引擎，统一使用InnoDB引擎；
3. 必须明确申明使用的字符集，统一使用utf8mb4；
4. 必须给每个表添加表的 COMMENT；
5. 每一个表都加上 create_time 和  modify_time 两个字段，数据类型为 datetime；后续的所有更新都必须更新 modify_time 字段；modify_time 字段上应创建索引；
6. 对于已经存在数据的库表，字段类型调整只允许变长，不允许变短；
7. 库表添加字段，新增列必须加在表的最后面；
8. 禁止对字段进行删除和重命名；
9. 一张表字段数不能超过100个，控制在 50 个字段以内；如果字段超过 50 个，可考虑将字段按冷热程度分表；
10. 禁止使用分区表、存储过程、自定义函数、触发器、视图；

3 字段设计规范

1. 必须给每个字段添加 COMMENT；
2. 字段设置为 NOT NULL，并设置默认值；
3. 选择最合适的数据类型，能用数字类型不用varchar类型；时间字段能用 date/datetime 类型不用 varchar 类型；
4. 尽量避免 text/lob 类型；如需使用，可以把这些字段和其他字段分离，放在单独的表中，通过主键与主表一一对应。；
5. 字段长度定义遵循最小化原则，够用就行，比如能用 tinyint 就不要用 int，能用 varchar(32) 就不要用 varchar(256)，不能贪图方便定义很大的长度；
6. 主键字段越小越好，并且主键值插入后，最好不要再有修改的需求，建议设置为自增的 int 或者自增的 bigint 字段；

4 索引规范

1. 为经常需要排序、分组和联合操作的字段创建索引；
2. 对较长的字符数据类型的字段建索引，优先考虑前缀索引；
3. 低选择性的列不加索引；
4. 索引会降低DML的性能，不是越多越好，只创建需要的索引，避免冗余索引；
5. 在建表时，应在开发测试阶段充分考虑需要添加什么索引，尽量避免上线后添加索引；
6. 创建组合索引/主键时，常用的字段放在前面；选择性高的字段放在前面；
7. 有事没事多explain，看看索引使用是否合理；

5 SQL 规范

1. 禁止 select *，查询的时候必须要指明读取的字段；
2. update 和 select 操作每条语句都必须带 where 条件；
3. SQL 语句中的参数与数据库字段的数据类型应严格一致，比如字段类型为字符型，则参数应该加引号；
4. 不能在列段做运算或者做函数转换；
5. 随机排序不允许用 order by rand()；
6. 不允许使用rand、uuid等不确定函数；
7. 不使用子查询；
8. 数据类型要正确，小心隐式转换；
9. insert 语句必须写清楚值与字段的对应关系，不允许使用 insert into table_name values() 方式；
10. 如果 SQL 出现多个表，则引用字段时，必须字段指定所属表；
11. 每个 SQL 都要有详细的注释；
12. 禁止使用 % 在最前面匹配的方式；
13. SQL 中 where子句 in 或者 or 的值不能一次性输入超过 500 个；
14. 事务中如果需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的语句放后面；
15. 禁止操作 MySQL 系统库，例如:mysql,sys,information_schema, performance_schema；
16. 对同一个表的多次 alter 操作必须写成一条 SQL 合并为一次操作；

6 高危 SQL 规范

1. 禁止业务直接执行 drop、truncate 表的操作；
2. 禁止业务直接执行 drop index、drop column 的操作；
3. 禁止业务直接执行 drop database 的操作；
4. 禁止业务直接将列的长度从大改小；
5. 禁止业务直接执行没有 where 条件的 select、update 和 delete 的 SQL；
6. 禁止业务直接执行修改列的字段类型；
7. 禁止业务使用 flush tables,flush table xxx with read lock,lock table xxx 等类型的SQL；
8. 禁止业务直接使用 insert into xxx select * from xxxx 类型的 SQL；
9. 禁止不通过 DB 管控平台执行线上 SQL；
10. 禁止业务在 alter 修改或者添加字段的时候使用 after 和 before 关键字；
11. 禁止业务修改列名；
12. 禁止在数据库中存储明文密码后者不加盐的密码；

---

命名规范

1 库命名规范

1. 数据库名称全部使用小写；
2. 名称只允许使用 a-z、0-9 及下划线的组合，不允许出现其他字符；
3. 使用有意义的名称，能做到观其名知其意，比如：order_db；
4. 年份固定格式为 YYYY 四位；月份固定为 MM 两位；
5. 如果按百库十表方案分库，则名称为标准库名+下划线+编号后缀，其中编号长度固定为2位，取值范围从00到99，如order_db_00……order_db_99；
6. 如果按照月份分库，则库名为标准库名+下划线+年月编号，其中年月格式为YYYYMM，固定6位长度，如order_db_201901……order_db_201912；

2 表命名规范

1、表名以 t_ 开头，全部使用小写；
2、名称只允许使用 a-z、0-9 及下划线的组合，不允许出现其他字符；
3、使用有意义的名称，能做到观其名知其意，比如：t_order_list；
4、如果按百库十表方案分库，则名称为表名+下划线+编号后缀，其中编号长度固定为1位，取值范围从 0 到 9，如 t_order_0…….t_order_9；
5、如果按照月份分库，则库名为标准表名+下划线+年月编号，其中年月格式为 YYYYMM，固定 6 位长度，如 t_order_list_201901……t_order_list_201012；
6、对于日志表设置为月表；

3 字段命名规范

1、字段全部使用小写；
2、字段名称只允许使用a-z、0-9及下划线的组合，不允许出现其他字符；
3、使用有意义的名称，能做到观其名知其意，比如：create_time；
4、禁止使用数据库关键字作为字段名，关键字列表如下：

ADD (add)	ALL (all)	ALTER (alter)	ANALYZE (analyze)	AND (and)
AS (as)	ASC (asc)	ASENSITIVE (asensitive)	BEFORE (before)	BETWEEN (between)
BIGINT	BINARY	BLOB	BOTH	BY
CALL	CASCADE	CASE	CHANGE	CHAR
CHARACTER	CHECK	COLLATE	COLUMN	CONDITION
CONNECTION	CONSTRAINT	CONTINUE	CONVERT	CREATE
CROSS	CURRENT_DATE	CURRENT_TIME	CURRENT_TIMESTAMP	CURRENT_USER
CURSOR	DATABASE	DATABASES	DAY_HOUR	DAY_MICROSECOND
DAY_MINUTE	DAY_SECOND	DEC	DECIMAL	DECLARE
DEFAULT	DELAYED	DELETE	DESC	DESCRIBE
DETERMINISTIC	DISTINCT	DISTINCTROW	DIV	DOUBLE
DROP	DUAL	EACH	ELSE	ELSEIF
ENCLOSED	ESCAPED	EXISTS	EXIT	EXPLAIN
FALSE	FETCH	FLOAT	FLOAT4	FLOAT8
FOR	FORCE	FOREIGN	FROM	FULLTEXT
GOTO	GRANT	GROUP	HAVING	HIGH_PRIORITY
HOUR_MICROSECOND	HOUR_MINUTE	HOUR_SECOND	IF	IGNORE
IN	INDEX	INFILE	INNER	INOUT
INSENSITIVE	INSERT	INT	INT1	INT2
INT3	INT4	INT8	INTEGER	INTERVAL
INTO	IS	ITERATE	JOIN	KEY
KEYS	KILL	LABEL	LEADING	LEAVE
LEFT	LIKE	LIMIT	LINEAR	LINES
LOAD	LOCALTIME	LOCALTIMESTAMP	LOCK	LONG
LONGBLOB	LONGTEXT	LOOP	LOW_PRIORITY	MATCH
MEDIUMBLOB	MEDIUMINT	MEDIUMTEXT	MIDDLEINT	MINUTE_MICROSECOND
MINUTE_SECOND	MOD	MODIFIES	NATURAL	NOT
NO_WRITE_TO_BINLOG	NULL	NUMERIC	ON	OPTIMIZE
OPTION	OPTIONALLY	OR	ORDER	OUT
OUTER	OUTFILE	PRECISION	PRIMARY	PROCEDURE
PURGE	RAID0	RANGE	READ	READS
REAL	REFERENCES	REGEXP	RELEASE	RENAME
REPEAT	REPLACE	REQUIRE	RESTRICT	RETURN
REVOKE	RIGHT	RLIKE	SCHEMA	SCHEMAS
SECOND_MICROSECOND	SELECT	SENSITIVE	SEPARATOR	SET
SHOW	SMALLINT	SPATIAL	SPECIFIC	SQL
SQLEXCEPTION	SQLSTATE	SQLWARNING	SQL_BIG_RESULT	SQL_CALC_FOUND_ROWS
SQL_SMALL_RESULT	SSL	STARTING	STRAIGHT_JOIN	TABLE
TERMINATED	THEN	TINYBLOB	TINYINT	TINYTEXT
TO	TRAILING	TRIGGER	TRUE	UNDO
UNION	UNIQUE	UNLOCK	UNSIGNED	UPDATE
USAGE	USE	USING	UTC_DATE	UTC_TIME
UTC_TIMESTAMP	VALUES	VARBINARY	VARCHAR	VARCHARACTER
VARYING	WHEN	WHERE	WHILE	WITH
WRITE	X509	XOR	YEAR_MONTH	ZEROFILL

4 索引命名规范

1. 索引名称全部使用小写；
2. 字段名称只允许使用a-z、0-9 及下划线的组合，不允许出现其他字符；
3. 唯一索引使用uniq开头，后面加上字段的简写，比如给 username 列添加唯一索引，索引名称为：uniq_username
4. 一般索引使用 idx 开头，后面加上字段的简写，比如给 create_time 和 username列添加索引，索引名称为：idx_createtime_username，也可以简单写成 idx_ctime_uname；