MySQL数据库开发规范1.3

1、平安金融科技 数据库（MySQL ）开发规范作者：简朝阳Last Updated: 25/02/14 19:30:18精选历史修订记录:版本 修订人修订时间修订内容1.01.1李海军2013-03-111.2李海军2013-07-291.3李海军2014-02-25增加部分说明及修改增加连接池使用说明和memory引擎的控制增加了 char类型，修改了 timestamp 的使 用场合。说明本规范包含 平安金融科技 使用MySQL数据库时所需要遵循的所有对象设计（数据库，表，字段），所需要遵循的命名，对象设计，SQL编写等的规范约定。所有内容都为必须严格执行的项目，执行过程中有任何疑问，请联系

2、DBA Team 取得帮助。概述禁止明文传播数据库帐号和密码。禁止开发工程师通过应用帐号登录生产数据库。禁止应用在服务器安装MySQL客户端（可以安装开发包）。禁止开发人员在 SQL中添加 Hint , Hint只能由DBA审核后添加。禁止使用悲观锁定，即读锁 select for update 。禁止在开发代码中使用DDL语句，比如 truncate , alter table等。禁止DML语句的where 条件中包含恒真条件（如：1=1 ）。1. 命名规范总则数据库对象名仅可包含小写英文字母、数字、下划线（_）三类字符，并以英文字母开头。数据库对象命名禁止使用 MySQL 保留字。多个单词

3、之间用下划线（_）分隔。对象名称长度若超过限制，则使用简写 / 缩写命名。1.1. 数据库命名数据库以 "db_" 前缀 + " 站点名 _"前缀及其所服务的应用名称命名。1.2. 表命名所属同一模块的表必须以模块名作为前缀命名。历史数据表在原表基础上增加 "_his" 后缀命名。1.3. 字段命名delete_flag布尔意义的字段以 "_flag" 作为后缀，前接动词。如：表示逻辑删除意义的字段可命名为各表间相同意义的字段（如：作为连接关系的引用字段）使用相同的字段名。1.4. 索引命名唯一索引以uk_tabl

4、ename_columnnames 方式命名普通索引以idx_tablename_columnnames 方式命名组合索引以idx_tablename_column1_column2. 方式命名示例站点名：maymay模块名：order ；数据表：item ；字段组成： order_item_id,add_time,raw_update_time,c1,c2,c3,c4,c5标准数据库名：db_maymay_order；标准数据表名：order_item ；历史数据表名：order_item_his ；索引需求： c1 唯一， c2 和 c3 组合索引： uk_order_item_c1, i

5、dx_order_item_c2_c3字段实际意义：是否已删除；标准字段名： delete_flag ；字段 order.order_id 被 order_item 引用；order_item 表中与之对应的字段命名必须为： order_id2. 对象设计规范总则所有表、字段必须添加能够清楚表示其含义的注释。状态类字段的注释中必须明确列出各状态值的说明。MySQL 数据库中仅可以使用下文提及的数据类型。2.1. 数据类型2.1.1. 数值类型DECIMAL(M,D)当表示定点小数的情况下使用该类型，禁止使用浮点类型，会带来不精确。定点数在 MySQL 内部以字符 串形式存储，比浮点数更精确，适

6、合用来表示货币等精度高的数据。INT 系列所有整数类型字段使用 INT (TINYINT 、 SMALLINT 、 MEDIUMINT 、 INT 、 BIGINT ) ,根据所存放的数据大小选择合适的子类型，且所有INT 类型都不使用长度限制。2.1.2. 字符串类型VARCHAR所有可变长度的字段均使用 VARCHAR 类型，对于有限类别的字段(如性别、状态等)，均建议使用VARCHAR 类型存储能明显表现其意义的字符串。TEXT 系列仅当需存储的字节数可能超过 20000 时，使用 TEXT 系列类型( TEXT 、 MEDIUMTEXT 、 LONGTEXT )。并和原表进行分拆，与原

7、表主键组成新表存储，且每个表只允许有一个TEXT 系列类型字段。CHAR仅当字段确定为定长，且将来不会修改长度时，使用 CHAR 类型。上线以后不允许修改字段类型。谨慎使 用2.1.3. 时间类型DATE只需要精确到天的字段使用 DATE 类型。精确到" 天" 的取当前时期的操作使用CURDATE（） 函数实现。DATETIME需要精确到时间（时、分、秒）的字段使用 DATETIME 类型。精确到"秒" 的取当前时间的操作使用NOW（） 函数实现。取值范围： '1000-01-01' 到'9999-12-31'TIMEST

8、AMP该类型仅允许raw_update_time 字段使用，其它字段不允许使用该类型。取值范围： '1970-01-0100:00:00' 到'2037-01-01 00:00:00'2.2. 表设计必含字段id INT ：主键使用 MySQL 的自增类型raw_add_time DATETIME ：创建日期（大字段拆分表除外），必须使用数据库时间（用 now（） 生成）raw_update_time TIMESTAMP ：修改日期（大字段拆分表除外, 但内容变化必须修改主表的update_time 字段），由数据库自动变更，应用不操作此字段以上 3 个字段都必

9、须是没有任何商业意义的与业务无关的字段，不允许赋予任何商业意义作为表间连接关系的字段，数据类型必须保持严格一致，避免索引无法正常使用。附属表拆分后，附属表关联字段使用主表主键字段，且附属表须有独立主键（大字段拆分的表不需要单独的主键）存在过期概念的表，在其设计之初就必须有过期机制，且有明确的过期时间。过期数据必须迁移至历史表中。不再使用的表，必须通知 DBA 予以更名归档。线上表中若有不再使用的字段，为保证数据完整，禁止删除，而是进行更名归档，名称统一增加"droped_" 前缀2.3.约束使用2.3.1.主键主键不能包含业务含义。主键在任何情况下不允许被更新。2.2.1.

10、 唯一约束除主键外，需存在唯一性约束的，可通过创建以"uk_" 为前缀的唯一索引实现。2.2.2. 外键任何情况不在数据库创建外键约束，外键规则由应用控制。2.2.3. 非空列所有非空列须在建表之初明确标识 "NOT NULL", 上线之后，不允许再变更。2.2.4. 存储过程、触发器、视图、计划任务禁止任何业务逻辑通过封装在数据库中的 procedure/function/trigger 实现。禁止应用程序使用 view 。禁止业务逻辑使用数据库的计划任务。3. SQL 编写规范3.1. 绑定变量与替代变量原则所有 Query 的 Where 条件中的

11、变量，都需要使用绑定变量来实现，此要求并不完全是基于性能的考虑，更多是基于安全方面的考虑。3.2. 数据类型转换原则避免因数据类型转换导致执行计划有误。说明where 条件中的过滤字段如需转换类型，只可转换过滤值，不可转换被过滤字段。表连接操作中，作为连接条件的字段的数据类型严格一致。如果表连接字段数据类型不一致，在 SQL 中用显示用类型转换，具体情况咨询 DBA 。示例正确用法 1 ： select col1, col2 from tbl1, tbl2where tbl1.col3 = tbl2.col4;其中 "tbl1.col3" 与 "tbl2.col4

12、" 数据类型严格一致。正确用法 2 ： select col1 ， col2 from tblwhere gmt_create = str_to_date('20100526 00:00:00','%Y%m%d %H:%i:%s');"count( )”使用除非是明确目的是统计某个字段上值不为空的记录的数目，否者只允许之用 count(*), 而不允许使用count(column_name) 或者 count(1) 。3.3. "select * from”使用原则为避免查询中无用字段参与排序操作而导致的性能降低及潜在的安全隐患。禁

13、止使用 "select * from说明任何情况都要明确列出查询需要返回的字段，禁止使用 select * 返回所有字段。3.4. "insert into tablename values()" 使用原则insert into 语句。为避免增加或删除字段带来的SQL 报错。禁止使用省略字段名的说明任何情况都要明确列出需要写入的字段名称。3.5. 表连接原则规范连接语法以方便SQL 脚本的阅读及提升连接操作性能。说明非外连接查询中，连接表在 "from" 子句中列出，并以逗号分隔；连接条件在 "where" 子句中列出，而不

14、允许使用join on式实现join。外连接查询中，可使用 "left join on"语法；外连接一律使用"left join"表示。可以改写为连接的子查询禁止使用子查询方式，而应改写为连接方式。示例规范select col1, col2 from tbl1, tbl2 where tbl1.col4 = tbl2.col3;select col1, col2 from tbl1 left outer join tbl2 on tbl1.col4 = tbl2.col3;select col1, col2 from tbl1 left outer jo

15、in tbl2 on tbl1.col4 = tbl2.col3;select col1 from tbl1,tbl2 where tbl1.col2 = tbl2.col3;select col1 from tbl1 left join tbl2 on tbl1.col2 = tbl2.col3 where tbl2.col3 is null;非规范：select col1, col2 from tbl1 join tbl2 on tbl1.col4 = tbl2.col3;select col1 from tbl1 where col2 in (select col3 from tbl2);select col1 from tbl1 where col2 not in (select col3 from tbl2);3.6. 分页查询说明分页查询必须带有唯一的排序条件，除非业务逻辑明确要求随机展现数据。对于多表连接的分页语句，如果过滤条件在单个表上，则先分页，后连接。见示例通过 limit 进行分页操作的时候， limit 参数为 start (起始记录数)， page_offset( 每页记录数) 。 示例规范化用法：s