数据库设计规范:
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:所有的规范都是适应自己的业务场景的,欢迎大家提出更好的建议,自己也会根据业务的发展,不断总结扩展最契合的数据库规范。
----------------------------------------------------------------------------------------------------------------
原博客地址: http://blog.itpub.net/26230597/viewspace-1312184/
原作者:黄杉 (mchdba)
----------------------------------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26230597/viewspace-1312184/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26230597/viewspace-1312184/