【MySql】MySQL数据库设计初步规范

MEI

1 表设计规范
1.1关于表设计

a) 表名、列名必须有注释。

b) 命名应使用富有意义的英文词汇或者缩写，多个单词组成的，全部大写，以"_"分隔开来，只能使用英文字母，数字和下划线，不留空格。比如USER_DETALL，不允许使用关键字TYPE或者STATUS等来作为字段名。

c) 命名长度不超过15个字符为宜（避免超过20），应该反映数据集所在的业务范围，或者业务功能，如POWER_USER(用户中心)等。

d) 当字段类型为枚举或者布尔型时，使用 CHAR(1)（或者CHAR(2)）类型，填写默认值，状态字段的默认值不能为null一般是设置成0或者-1，状态字段的描述写成 comment '团购券状态：1.已购买；2.已使用；3.退款中；4已退款'。

e) 在设计时候尽量包含日期字段：CREATE_DATE(创建日期)，UPDATE_DATE(更新日期)等。mysql中对日期约定一种录入方式，如'2014-12-31 00:00:00.0'

f) 默认值，数字类型默认值为0，字符串默认值为’’，日期默认值为’1900-01-01 00:00:00.0’。

g) 主键字段ID用bigint，如果create语句中有AUTO_INCREMENT=6653864标示，请去掉。

h) 日期字段的默认值也不能为null，一般设置成1970-12-31 00:00:00.0。

i) 手机字段，邮箱字段等会被检索的字段不允许为null，默认值为空串’’。数字类型字段不允许为null默认值为0。

j) 默认字符编码为utf8，默认存储引擎为INNODB

PS：每个表必须有主键字段，必须有录入日期字段并且值不允许为NULL。

1.2 索引设计

1) 普通索引，以IDX_开头连接字段名字。

2) 值范围重复的比例少的，建立索引字段，比如 CREATE_DATE(录入时间)字段；值范围重复比率的字段不需要建立索引比如IS_RETURN(是否已退款)字段。

3) 主键字段不需要建立unique key，主键字段也不需要单独建索引。

4) WHERE条件后面的经常查询的字段，需要加索引，比如ORDER_GOODS 表的ORDER_SN(商品号)等。

5) 范围字段不需要加索引，比如SHOP_MALL表的IS_DEL字段等。

6) 建立索引的字段，要求不能有null值，否则会影响索引的效率。

1.3表结构范例

建表语句范例：

CREATE TABLE `SHOP_GAY` (

`ID` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '店铺ID',

`SHOP_NAME` VARCHAR(50) DEFAULT '' COMMENT '店铺名称',

`LEGAL_PERSON_MOBILE` VARCHAR(11) DEFAULT NULL COMMENT '法人移动电话',

`SCORE` BIGINT(20) DEFAULT 0 COMMENT '积分',

......

`MANAGER_NAME` VARCHAR(20) DEFAULT '' COMMENT '店长姓名',

`BRIEF` VARCHAR(500) DEFAULT '' COMMENT '店铺简介',

`HAS_WAREHOUSE` CHAR(1) DEFAULT '0' COMMENT '是否有仓库, 0:没有; 1：有 ',

`DESCRIPTION_FIT` DECIMAL(3,1) DEFAULT 0 COMMENT '描述相符--通过对所有订单商品的评价计算平均值取一位小数得到',

`BACKGROUND` VARCHAR(200) DEFAULT '' COMMENT '店铺标题图片',

`CREATED_DATE` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',

`UPDATED_DATE` DATETIME DEFAULT '1970-12-31 00:00:00.0' COMMENT '更新时间',

PRIMARY KEY (`ID`),

KEY IDX_MOB(LEGAL_PERSON_MOBILE),

KEY IDX_CRETIME(CREATED_DATE),

KEY IDX_UPTIME(UPDATED_DATE)

) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT='GAY店铺'

添加字段范例：

ALTER TABLE AUTH_MALL ADD COLUMN SHORT_NAME VARCHAR(20) DEFAULT '' COMMENT '广场名简写' AFTER FULL_NAME;

修改表字段范例：

ALTER TABLE GATEWAY_PAYMENT_ORDER MODIFY COLUMN STAT varchar(2) DEFAULT '0'

comment '交易状态 0:待支付/退款,1:等待第三方渠道回调,2:支付/退款成功,3:支付/退款失败,4:支付/退款确认成功,5:支付/退款确认失败,6;交易关闭,7:待收款(如果是此状态-需要确认收款账号是否正常),8:支付/退款确认成功-不可再进行其他操作,9:验签失败,10:同步确认/买家已付款-等待卖家发货WAIT_SELLER_SEND_GOODS,11:同步确认/卖家已发货等待买家确认WAIT_BUYER_CONFIRM_GOODS' AFTER DESCRIPTION;

2 SQL编写
2.1，尽量使用单表查询，避免多表JOIN。JOIN的后续ON条件不能用OR判断，比如SELECT A.C1,B.C2 FROM A,B ON(A.ID=B.PID OR B.TAG=A.TAR_GET); OR性能非常低，PS：我们线上一些打开缓慢的功能模块都是因为这个OR的写法导致的。

2.2，写到应用程序的SQL语句，禁止一切DDL操作，例如：create,drop,alter,grant,remove；如有特殊需要，请与dba协商同意方可使用。

2.3，写SQL的时候一定要给每个字段指定表名做前缀。比如select ub.id,ub.name from user_business ub where ub.create_date > '';在iBatis的SQLMap文件中绑定变量使用"#var_name"表示，替代变量使用"$var_name$"；所有需要动态order by条件的查询，在使用替代变量过程中，需要将可能传入的内容以枚举写死在代码中，禁止接受外部传入内容。

2.4，如果需要事务的支持，在使用了innodb的情况下，在数据库连接时候，先关闭自动提交，如: set auto_commit=0; 在写java代码的时候，在有事务处理的情况下，执行insert、delete、update后，commit；在exception代码块，必须写上rollback操作。

2.5，不要写select *类似的代码，需要制定的字段名。

2.6，mysql的日期和字符是相同的，所以不需要像oracle那样做另外的转换，比如：

select e.username from employee e where e.birthday>='1998-12-31 11:30:45'。

2.7，避免在where字句中对字段施加函数，如果是业务要求的除外，但需要在编写时候咨询DBA。比如DATE_FORMAT(p.PAYMENT_DATE, '%Y-%m-%d') >= DATE_FORMAT('2014-10-01', '%Y-%m-%d')，就需要改正掉。

2.8，避免多余的排序，使用group by的时候，默认会进行排序，当你不需要排序的时候，可以使用order by null；

2.9，当表连接时候，用于连接的两个表的字段如果数据类型不一致，则必须在一边加上类型转换的函数。杜绝mysql做隐式类型转换的情况。

2.10，禁止在应用程序里面执行对数据库的批量更新sql操作，如果需要请发email由dba判断在合适的时间段里面在IDC的库上手动人工执行。

3 基本原则

PS：表结构变更须由库表所属团队发起。

1，所有在测试环境执行的表结构变更的SQL必须经过DBA review。

2，不允许物理删除，不得使用存储过程、触发器以及视图，特殊情况和业务场景申请DBA

PS：所有的规范都是适应自己的业务场景的，欢迎大家提出更好的建议，自己也会根据业务的发展，不断总结扩展最契合的数据库规范。