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

 

数据库设计规范：

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.php.cn）！