编写本规范的目的是为了统一数据库命名风格,提高SQL的可读性、可重用性,确保在开发成员或开发团队之间的工作可以顺利交接,不必花很大的力气便能理解已编写的数据库对象;遵守某些规范也能提升性能,提高程序的稳定性。
本规范分成数据库结构设计规范和SQL编写规范,选定数据库产品为Mysql,某些条款和MYSQL特性有关,如果是别的数据库产品仅作参考。
数据库设计规范在新项目设计时要严格遵守,老项目可以参考遵守。SQL编写规范新老项目都要严格遵守。
本规范自生效之日起,对以后新编写的和修改的SQL和数据库对象有约束力。
开发工程师,系统架构师,DBA
【规则1-1】 MYSQL版本选定5.5.9社区版,64位版本。可选存储引擎为:InnoDB,SPHINX。如有特殊业务需要别的存储引擎需要通知DBA确认。
说明:维持开发环境/测试环境/生产环境版本一致。
说明:如果业务不需要主键,那么加上一个无意义的自增字段做主键。主键三要素:值域范围够用且存储长度越短、值的唯一性和易比较性、数值的有序性增加。
【规则1-5】 不允许在数据库里定义数据约束/字段主外键关系,由程序维护
反例:
KEY `dage_id` (`dage_id`),
CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
constraint pk_order_id primary key (order_id),
constraint chk_quantity check (quantity>=10) ,
【规则1-6】 主外键关系的字段,数据类型/长度必须完全一致。
正例:
CREATE TABLE T_OM_AREA(
FID BIGINT NOT NULL COMMENT '主键ID',
FSTATUS INT NOT NULL DEFAULT 0 COMMENT '状态',
FNAME VARCHAR(2000) NOT NULL DEFAULT '' COMMENT '区域名称',
PRIMARY KEY (`FID`)
) ENGINE=INNODB COMMENT '区域信息表';
反例:
CREATE TABLE T_OM_AREA(
FID SMALLINT UNSIGNED NOT NULL COMMENT '主键ID',
FSTATUS TINYINT UNSIGNED COMMENT '状态',
FNAME VARCHAR(64) DEFAULT NULL COMMENT '区域名称',
PRIMARY KEY (`FID`)
) ENGINE=INNODB COMMENT '区域信息表';
【规则1-8】 不允许在数据库里存图片/邮件正文/文章/等大数据。必须使用TEXT,BLOB数据类型时,把大字段拆分到单独的表
正例:
CREATE TABLE T_OM_T1(
FID SMALLINT UNSIGNED NOT NULL COMMENT '主键ID',
FSTATUS TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态',
FTYPE TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型/0:建议/1:强制',
FNAME VARCHAR(64) NOT NULL DEFAULT '' COMMENT '区域名称',
PRIMARY KEY (`FID`)
) ENGINE=INNODB COMMENT '区域信息表1';
CREATE TABLE T_OM_T2(
FID SMALLINT UNSIGNED NOT NULL COMMENT '主键ID',
F_T1_ID SMALLINT UNSIGNED NOT NULL COMMENT 'T1表的关联ID',
FDATA TEXT NOT NULL DEFAULT '' COMMENT '邮件正文'
PRIMARY KEY (`FID`)
) ENGINE=INNODB COMMENT '区域信息表2';
反例:
FID SMALLINT UNSIGNED NOT NULL COMMENT '主键ID',
FSTATUS TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态',
FTYPE TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型/0:建议/1:强制',
FNAME VARCHAR(64) NOT NULL DEFAULT '' COMMENT '区域名称',
FDATA TEXT NOT NULL DEFAULT '' COMMENT '邮件正文'
PRIMARY KEY (`FID`)
) ENGINE=INNODB COMMENT '区域信息表1';
正例:
CREATE TABLE T_OM_AREA(
FID SMALLINT UNSIGNED NOT NULL COMMENT '主键ID',
FSTATUS TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态',
FTYPE TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型/0:建议/1:强制',
FNAME VARCHAR(64) NOT NULL DEFAULT '' COMMENT '区域名称',
PRIMARY KEY (`FID`)
) ENGINE=INNODB COMMENT '区域信息表';
反例:
CREATE TABLE T_OM_AREA(
FID BIGINT NOT NULL COMMENT '主键ID',
FSTATUS INT NOT NULL DEFAULT 0 COMMENT '状态',
FTYPE INT NOT NULL DEFAULT 0 COMMENT '类型/0:建议/1:强制',
FNAME VARCHAR(2000) NOT NULL DEFAULT '' COMMENT '区域名称',
PRIMARY KEY (`FID`)
) ENGINE=INNODB COMMENT '区域信息表';
【规则1-10】 不允许在表定义时显式指定 字段/表 的字符集以及字符集校验规则。
说明:数据库字符集默认为utf8,字符集默认校验规则为utf8_general_ci,在DB服务器部署标准里已经做了定义。
正例:
CREATE TABLE `T_PUSH_VERSION_CLEARDATA` (
`FID` INT(11) UNSIGNED NOT NULL COMMENT '主键ID',
`FPUSH_APPID` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '应用ID',
`FMIN_VERSION` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '最小支持版本',
`FTYPE` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型/0:建议/1:强制',
PRIMARY KEY (`FID`)
) ENGINE=INNODB ;
反例:
CREATE TABLE `T_PUSH_VERSION_CLEARDATA` (
`FID` INT(11) UNSIGNED NOT NULL COMMENT '主键ID',
`FPUSH_APPID` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '应用ID',
`FMIN_VERSION` VARCHAR(64) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '最小支持版本',
`FTYPE` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型/0:建议/1:强制',
PRIMARY KEY (`FID`)
) ENGINE=INNODB DEFAULT CHARSET=UTF8 COLLATE utf8_bin;
【规则1-11】 表/字段 必须有COMMNET标示字段含义,表含义。
正例:
CREATE TABLE `T_PUSH_VERSION_CLEARDATA` (
`FID` INT(11) UNSIGNED NOT NULL COMMENT '主键ID',
`FPUSH_APPID` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '应用ID',
`FMIN_VERSION` VARCHAR(64) NOT NULL DEFAULT '' COMMENT '最小支持版本',
`FTYPE` TINYINT(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型/0:建议/1:强制',
PRIMARY KEY (`FID`)
) ENGINE=INNODB COMMENT '推送版本表';
反例:
CREATE TABLE `T_PUSH_VERSION_CLEARDATA` (
`FID` INT(11) UNSIGNED NOT NULL ,
`FPUSH_APPID` BIGINT(20) UNSIGNED NOT NULL DEFAULT 0 ,
`FMIN_VERSION` VARCHAR(64) NOT NULL COMMENT DEFAULT '' ,
`FTYPE` TINYINT(4) UNSIGNED NOT NULL COMMENT DEFAULT 0 ,
PRIMARY KEY (`FID`)
) ENGINE=INNODB;
【规则1-12】 表结构定义不允许指定索引类型以及索引排序规则
说明:INNODB引擎支持显式定义HASH索引类型,但都以BTREE存储,索引支持显式定义索引排序ASC,DESC,但都以升序存储
正例:
CREATE TABLE T_OM_AREA(
FID SMALLINT UNSIGNED NOT NULL COMMENT '主键ID',
FSTATUS TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态',
FTYPE TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型/0:建议/1:强制',
FNAME VARCHAR(64) NOT NULL DEFAULT '' COMMENT '区域名称',
PRIMARY KEY (`FID`),
KEY IDX_STATUS(FSTATUS) ,
KEY IDX_NAME(FNAME)
) ENGINE=INNODB COMMENT '区域信息表';
反例:
CREATE TABLE T_OM_AREA(
FID SMALLINT UNSIGNED NOT NULL COMMENT '主键ID',
FSTATUS TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态',
FTYPE TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '类型/0:建议/1:强制',
FNAME VARCHAR(64) NOT NULL DEFAULT '' COMMENT '区域名称',
PRIMARY KEY (`FID`),
KEY IDX_STATUS USING HASH (FSTATUS DESC ) ,
KEY IDX_NAME(FNAME) USING BTREE
) ENGINE=INNODB COMMENT '区域信息表';
【规则1-13】 不允许使用VARCHAR存储日期时间。不要使用VARCHAR存储IP地址
正例:
CREATE TABLE T_OM_MONITOR(
FID INT UNSIGNED NOT NULL COMMENT '主键ID',
FIP INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '监控的IP地址',
FDATE DATETIME NOT NULL COMMENT '监控时间',
PRIMARY KEY (`FID`),
) ENGINE=INNODB COMMENT '监控信息表';
反例:
CREATE TABLE T_OM_MONITOR(
FID INT UNSIGNED NOT NULL COMMENT '主键ID',
FIP VARCHAR(32) NOT NULL DEFAULT '' COMMENT '监控的IP地址',
FDATE VARCHAR(32) NOT NULL COMMENT '监控时间',
PRIMARY KEY (`FID`),
) ENGINE=INNODB COMMENT '监控信息表';
【规则1-14】 必须使用DECIMAL 数据类型存储金额/价格信息
说明:PHP项目组对象命名可以是小写字母,JAVA组统一为大写字母命名。
反例:
`size` INT(10) NOT NULL DEFAULT '0' COMMENT '文件大小,以B为单位',
`FUNCTION` VARCHAR(256) NOT NULL COMMENT '函数名称',
`KEY` TINYINT(4) NOT NULL COMMENT '关键字',
正例:
CREATE DATABASE MEIZU_MUSIC
正例:
CREATE TABLE T_OM_SONG....
#说明: T:Table,OM:Online_Music,SONG:歌曲表
反例:
CREATE TABLE db_magiclog_s1....
【规则2-3】 字段对象命名,格式:F+字段名,多个单词之间用下划线分隔,但单词+ID时例外,不需要加下划线
正例:
`FID` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`FNUMBER` char(16) NOT NULL COMMENT '编码',
`FUSERID` bigint(20) unsigned NOT NULL COMMENT '购买者ID',
`FUSER_NAME` varchar(40) DEFAULT NULL,
`FPHONE_SN` varchar(30) NOT NULL COMMENT '手机SN',
【规则2-4】 普通索引对象命名,格式:IDX[_字段1,字段2]. 唯一索引对象命名,格式:UDX[_字段1,字段2].
正例:
UNIQUE KEY `UDX_FNUMBER` (`FNUMBER`),
KEY `IDX_FUSERID` (`FUSERID`),
KEY `IDX_FFINISHEDTIME` (`FFINISHEDTIME`),
KEY `IDX_FYEAR_PERIOD` (`FYEAR_PERIOD`),
【规则2-5】 存储过程命名,格式:P_模块_存储过程名([参数1,参数2])
正例:
CREATE PROCEDURE `P_APP_STATISTICS`(IN p_date VARCHAR(100))
【规则2-6】 自定义函数命名,格式:F_模块_函数名([参数1,参数2])
正例:
CREATE PROCEDURE `F_OM_GETIDS`(IN SONG_NAME VARCHAR(100)) RETURNS int(11)
正例:
CREATE VIEW V_OM_GETIDS AS SELECT
说明:KEY(A,B,C)=KEY(A)+KEY(A,B)+KEY(A,B,C)
正例:
KEY `IDX_VERSION_APPID` (`FAPPID`),
KEY `IDX_VERSION_COMID` (`FCOMPANYID`),
反例:
KEY `IDX_VERSION_APPID` (`FAPPID`),
UNIQUE KEY `UDX_VERSION_APPID` (`FAPPID`),
KEY `IDX_VERSION_APPID_COMID` (`FAPPID`,`FCOMPANYID`),
反例:
KEY `IDX_FSEX` (`FSEX`),
说明:长度超过20的字符串字段,根据数据分布情况计算出合理的前缀长度,建前缀索引
正例:
FTITLE VARCHAR(256) NOT NULL DEFAULT '' COMMENT '文章标题',
KEY IDX_FTITLE(FTITLE(12))
反例:
FTITLE VARCHAR(256) NOT NULL DEFAULT '' COMMENT '文章标题',
KEY IDX_FTITLE(FTITLE)
正例:
ALTER TABLE T_APP_VERSION ADD COLUMN FSECURITY SMALLINT(5) NOT NULL DEFAULT 0 COMMENT '安全扫描结果';
反例:
ALTER TABLE T_APP_VERSION ADD COLUMN FSECURITY SMALLINT(5) NOT NULL DEFAULT 0 COMMENT '安全扫描结果' AFTER FSIZE;
说明:
临时表名称:TMP_TBNAME_过期时间 如:TMP_MUISIC_20131129
备份表名称:BAK_TBNAME_过期时间 如:BAK_MUISIC_20131229
临时文件目录: /data/tmp
DBA会对线上库的临时文件和备份文件做清理,命名规则必须按规范走。