当前位置:Gxlcms > mysql > MySQL数据库设计初步规范V1.0

MySQL数据库设计初步规范V1.0

时间:2021-07-01 10:21:17 帮助过:25人阅读

数据库设计规范:

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:所有的规范都是适应自己的业务场景的,欢迎大家提出更好的建议,自己也会根据业务的发展,不断总结扩展最契合的数据库规范。

以上就是MySQL 数据库设计初步规范V1.0的内容,更多相关内容请关注PHP中文网(www.gxlcms.com)!





人气教程排行