mysql数据库设计开发规范

1．设计

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

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

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

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

  5. 如果应用使用的是长连接，应用必须具有自动重连的机制。但请避免每执行一个SQL去检查一次DB可用性。

  6. 如果应用使用的是长连接，应用应该具有连接的TIMEOUT检查机制，及时回收长时间没有使用的连接。TIMEOUT时间一般建议为20min。

  7. 我们所有的MySQL数据库除历史原因外，都必须采用UTF8编码。

  8. Mysql 对DDL支持很差，表结构推荐设计为Key-Value结构。如果是关系型结构的数据库，请尽量预留一些字段，如value1 ,value2 ,value3。

2．命名

  a) 命名应使用富有意义的英文词汇，多个单词组成的，中间以下划线分割。

  b) 命名只能使用英文字母，数字和下划线。

  c) 命名避免使用Mysql的保留字（详见附录A）和系统关键字。

  d) 命名长度以不超过15个字符为宜(避免超过20)。

  e) 命名全部采用小写，并且名称前后不能加引号。

? 数据库对象设计规范

1. 表

设计

  a) 在设计时尽量包含两个日期字段:gmt_created(创建日期),gmt_modified(修改日期)且非空, 对表的记录进行更新的时候，必须包含对gmt_modified字段的更新。

  b) 必须要有主键，主键尽量用自增字段类型，推荐类型为INT或者BIGINT类型。

  c) 需要多表join的字段，数据类型保持绝对一致。

  d) Mysql的表尽量设置成KV（Key-Value）结构，这样便于扩展和维护。

  e) 当表的字段数非常多时，可以将表分成两张表，一张作为条件查询表，一张作为详细内容表（主要是为了性能考虑）。

  f) 当字段的类型为枚举型或布尔型时，建议使用char(1)类型。

  g) 同一表中，所有varchar字段的长度加起来，不能大于65535.如果有这样的需求，请使用TEXT/LONGTEXT类型。

  h) 由于MYSQL表DDL维护成本很高，所以在适当的时候，可以有一定的字段容余。比如：Value1,Value2,Value3这样的字段。

   i) 每张表定义的ID字段，如果涉及数据量比较大，请尽量使用BigInt（补充）

   j) 对于日期类型Datetime和Timestamp的使用，GMT_Created和GMT_Modified建议明确类型（补充）

命名

  a) 同一个模块的表尽可能使用相同的前缀，表名尽可能表达含义，例如： CRM_SAL_FUND_ITEM。

  b) 字段命名应尽可能使用表达实际含义的英文单词或缩写，如，公司ID，不要使用：corporation_id, 而用：corp_id 即可。

  c) 布尔值类型的字段命名为is+描述。如member表上表示是否为enabled的会员的字段命名为IsEnabled。

常用字段类型

字段注释

a) 标准字段注释由一组"@"开头的标签+空格+文本组成。

以MD_USER表的部分字段为例：

b) 注释标签说明

2. 索引

设计

  a) Bitmap索引通常不适合我们的环境。

  b) 索引根据实际SQL，由DBA创建。

  c) 不要创建带约束的索引，所有的约束效果都通过显示创建约束然后再using index一个已经创建好的普通索引来实现。

命名

  a) <table_name><column_name>_ind,各部分以下划线（）分割。

  b) 多单词组成的column name，取前几个单词首字母，加末单词组成column_name。如：sample表member_id上的索引：sample_mid_ind。

3. 约束

  设计
    a) 主键最好是无意义的，，统一由Auto-Increment字段生成整型数据，不建议使用组合主键。

    b) 若要达到唯一性限制的效果，不要创建unique index，必须显式创建普通索引和约束（pk或uk），即先创建一个以约束名命名的普通索引，然后创建一个约束，用using index ...指定索引。

    c) 当删除约束的时候，为了确保不影响到index，最好加上keep index参数。

    d) 主键的内容不能被修改。

    e) 外键约束一般不在数据库上创建，只表达一个逻辑的概念，由程序控制。

    f) 当万不得已必须使用外健的话，必须在外健列创建INDEX。

  命名

    a) 主键约束： _pk结尾，<table_name>_pk；

    b) unique约束：uk结尾，<table_name><column_name>_uk；

    c) check约束： _ck 结尾，<table_name>_<column_name>_ck；

    d) 外键约束： _fk 结尾，以pri连接本表与主表，<table_name>pri<table_name>_fk；

4. 触发器

  命名
    a) <table_name>_A(After)B(Before)I(Insert)U(Update)D(Delete)_trg。

    b) 若是用于同步的触发器以sync作为前缀：sync_<table_name>_trg。

5. 过程、函数

  设计
    a) 如果要在MYSQL里使用存储过程类的技术，请务必和DBA沟通确认。

  命名

    a) 过程以proc_开头，函数以func_开头。

    b) 变量命名约定：本地变量以v_为前缀，参数以p_为前缀，可以带_I(输入)，_O(输出)、_IO(输入输出）表示参数的输入输出类型。

? SQL开发规范

一．编码规范

  1. 使用SQL操作数据库前，必须由use DB_name 开始

  Use Test ;

  Insert into Table_name values ( … ) ;

  Commit;

  2. 如果需要事务的支持，在确认使用了innodb 存储引擎的前提下，在数据库连接时，先关闭自动提交

  比如，设定set auto_commit =0 ;

  3. 写到应用程序里的SQL语句，禁止一切DDL操作

  例： Create table ， Drop table ， Create database ， Drop database ， Alter table ，grant …… 如有特殊需要，必需与DBA协商同意方可使用。

  4. 获取当前时间请使用now()，不要用sysdate()来代替

  这对复制来说是很危险的，会导致主从数据不一致的情况； 因为sysdate,取的是系统主机时间，在BINLOG会原文传输， 当在应用时会与主库产生差异。

  5. 写SQL的时候一定要给每个字段指定表名做前缀

  比如: select a.id,a.name from test a; 好处是 一来带来性能的提升， 二来可以避免一些错误的发生。

  6. 在 iBatis 的 SqlMap 文件中绑定变量使用 “#var_name#”表示,替代变量使用“$var_name$”

  所有需要动态Order By 条件的Query，在使用替代变量过程中，需要将可能传入的内容以枚举类写死在代码中，禁止接受任何外部传入内容。

  7. 请不要写select * 这样的代码，指定需要的字段名

  8. Mysql 对日期（datetime）允许“不严格”语法

  任何标点符都可以用做日期部分或时间部分之间的间割符。例如， ‘98-12-31 11:30:45‘、‘98.12.31 11+30+45‘、‘98/12/31 11*30*45‘和‘98 (12)31 11^30^45‘是等价的。] 我们自己约定一种写法，与Oracle相通： ‘2009-12-31 11:30:45‘

  9. Mysql 的日期与字符是相同的，所以不需要做另外的转换

  例：Select e.username from employee e where e.birthday >=’1998-12-31 11:30:45’

  10. 避免多余的排序。使用GROUP BY 时，默认会进行排序，当你不需要排序时，可以使用order by null

  Select product,count(*) cnt from crm_sale_detail group by product order by null;

  11. 避免在where子句中对字段施加函数

     a) 通常，不允许在字段上添加函数或者表达式，这样将导致索引失效，如：

       错误的写法： select * from iw_account_log where substr(username,1,5)=’abcde’

       正确的写法： select * from iw_account_log where username like ’abcde%’

     b) 如果是业务要求的除外，但需要在编写时咨询DBA

     c) 特别注意，当表连接时，用于连接的两个表的字段如果数据类型不一致，则必须在一边加上类型转换的函数

  12. 严格要求使用正确类型的变量，杜绝Mysql做隐式类型转换的情况

  13. 全模糊查询无法使用INDEX，应当尽可能避免

      比如：select * from table where name like ‘%jacky%‘;

  14. 表连接规范

     a) 所有非外连接SQL（即INNER JOIN），请把关联表统一写到FROM字句中，关联条件与过滤条件统一写到WHERE字句中

     b) 出于代码的可读性原因，所有外连接SQL语句中，请一律使用LEFT JOIN，禁用RIGHT JOIN

     c) 另外，请注意LEFT JOIN字句中，右边位置表的条件书写位置不同的影响：

     SELECT A.rolename,A.gmt_create,B.nickname FROM gl_role A LEFT JOIN gl_roledetail B ON A.ID=B.roleid AND B.roleID=2;

     ----------------+------+

     | rolename | gmt_create | nickname |

    +------{}--------------+

    | 163.com | 0000-00-00 00:00:00 | test2 |

    | sina.com | 0000-00-00 00:00:00 | NULL |

    | hotmail.com | 0000-00-00 00:00:00 | NULL |

    | 126.com | 2009-08-20 18:20:18 | NULL |

    ----------------+------+

  15. 表连接分页查询的使用

    a) 常规分页语句写法(start：起始记录数，page_offset：每页记录数)：SELECT ID,username FROM gl_user WHERE username like ‘%@163.com‘ ORDER BY M.gmt_create LIMIT start, page_offset;

    b) 多表 Join 的分页语句，如果过滤条件在单个表上，需要先分页，再 Join：

        低性能写法： SELECT M.username,P.rolename FROM gl_user M INNER JOIN gl_role P ON M.ID=P.userid WHERE username like ‘%@163.com‘ ORDER BY M.gmt_create LIMIT start, page_offset;

        高性能写法： SELECT M.username,P.rolename FROM (SELECT ID,username FROM gl_user WHERE username like ‘%@163.com‘ ORDER BY M.gmt_create LIMIT start, page_offset)M,gl_role P WHERE M.ID=P.userid;

       这样写的前提是关联的表之间记录一一对应，否则可能会返回的记录数目少于或多余page_offset的值。

  16. "join"、"in"、"not in"、"exsits"和"not exists"的使用

     a) 比较IN,EXISTS,JOIN

          按效率从好到差排序:

             字段上有索引: EXISTS, IN, JOIN

             字段上没有索引: JOIN, EXISTS ,IN

      b) Anti-Joins: NOT IN ,NOT EXISTS, LEFT JOIN

          按效率从好到差排序:

               字段上有索引: LEFT JOIN, NOT EXISTS, NOT IN

               字段上没有索引: NOT IN, NOT EXISTS, LEFT JOIN

  17. 其它编写规范

     a) 对表的记录进行更新的时候，必须包含对gmt_modified字段的更新;

     b) 不允许在where后添加1=1这样的无用条件，where可以写在prepend属性里，如：

        错误的写法： select count from BD_CONTRACT t where 1=1 <dynamic> ...... </dynamic>

        正确的写法： select count from BD_CONTRACT t <dynamic prepend="where"> ...... </dynamic>

     c) 对大表进行查询时，在SQLMAP中需要加上对空条件的判断语句，具体可在遇到时咨询DBA，如：

     性能上不保险的写法：
     select count from iw_user usr

     <dynamic prepend="where">

     <isNotEmpty prepend="AND" property="userId"> usr.iw_user_id = #userId:varchar# </isNotEmpty>

     <isNotEmpty prepend="AND" property="email"> usr.email = #email:varchar# </isNotEmpty>

     <isNotEmpty prepend="AND" property="certType"> usr.cert_type = #certType:varchar# </isNotEmpty>

     <isNotEmpty prepend="AND" property="certNo"> usr.cert_no = #certNo:varchar# </isNotEmpty>

     </dynamic>

      性能上较保险的写法（防止那些能保证查询性能的关键条件都为空）：

      select count from iw_user usr

      <dynamic prepend="where">

      <isNotEmpty prepend="AND" property="userId"> usr.iw_user_id = #userId:varchar# </isNotEmpty>

      <isNotEmpty prepend="AND" property="email"> usr.email = #email:varchar# </isNotEmpty>

      <isNotEmpty prepend="AND" property="certType"> usr.cert_type = #certType:varchar# </isNotEmpty>

      <isNotEmpty prepend="AND" property="certNo"> usr.cert_no = #certNo:varchar# </isNotEmpty>

      <isEmpty property="userId"><isEmpty property="email"> <isEmpty property="certNo"> query not allowed </isEmpty>

      </isEmpty> </isEmpty>

      </dynamic>

     另外，对查询表单的查询控制建议使用web层进行控制而不是客户端脚本（JAVASCRIPT/VBSCRIPT）

     d) 聚合函数常见问题

        1) 不要使用count(1)代替count（*）

        2) count(column_name)计算该列不为NULL的记录条数

        3) count(distinct column_name)计算该列不为NULL的不重复值数量

        4) count()函数不会返回NULL，但sum()函数可能返回NULL，可以使用ifnull(sum(qty),0)来避免返回NULL

        e) NULL的使用

            1) 理解NULL的含义，是"不确定"，而不是"空"

            2) 查询时，使用is null或者is not null

            3) 更新时，使用等于号，如：update tablename set column_name = null

二．格式规范

    1．注释说明

         a) 本注释说明主要用于Mysql Client程序及其它SQL文件，其它可作参考；

         b) SQL接受的注释有三种：

          -- 这儿是注释(注意，第2个破折号后面至少跟一个空格符)

              /* 这儿是注释 */

             # 这儿是注释

         c) 下面的例子显示了3种风格的注释：

          mysql> SELECT 1+1; # This comment continues to the end of line

          mysql> SELECT 1+1; -- This comment continues to the end of line

          mysql> SELECT 1 /* this is an in-line comment */ + 1;

          mysql> SELECT 1+ /* this is a multiple-line comment */

  2．缩进

      低级别语句在高级别语句后的，一般缩进4个空格：

       DECLARE

            v_MemberId     VARCHAR(32),

       BEGINSELECT

          admin_member_id INTO v_MemberId

       FROM

          company WHERE id = 10;

      SELECT v_MemberId ;

       END;

      同一语句不同部分的缩进，如果为sub statement，则通常为2个空格，如果与上一句某部分有密切联系的，则缩至与其对齐：

BEGIN FOR v_TmpRec IN (SELECT login_id, gmt_created, -- here indented as column above satus FROM member -- sub statement WHERE site = ‘china‘ AND country=‘cn‘ ) LOOP NULL; END LOOP; END;

3．断行

    a) 一行最长不能超过80字符

    b) 同一语句不同字句之间

    c) 逗号以后空格

    d) 其他分割符前空格

    SELECT concat(offer_name,‘,‘, offer_count as offer_category, id) FROM category WHERE super_category_id_1 = 0;

? 附录：Mysql保留字

? Mysql名词解释

  a) PK：Primary Key，主键

  b) UK：Unique Key，唯一性索引

  c) FK：Foreign Key，外键