mysql数据库开发规范

日常开发中注意的数据库开发规范：

1.命名规范：

1.	要求数据对象命名尽量采用标准的翻译，能准确地表达该表的中文含义，做到见名知意，不允许随意缩写
2.	要求数据对象命名由字母、数字和下划线（“_”）组成
3.	要求数据对象命名不使用SQL保留字
4.	数据对象名称超过30个字符，采用保留数据对象名称原单词前2个音节的
5.	缩写规则，eg:IDENTITY缩写为ID,禁止随意缩写数据对象名称
6.	要求数据库表的命名是名词的复数形式，限制在30个字符内，当全名超过30字符时，可用缩写来减少名称长度
7.	要求数据库表的字段名为大写字母有意义的单词，或单词的缩写，
若字段由几个单词组成，则单词间用下划线(“_”)分割
字段名称限制在30个字符内。当字段名超过30字符时，可用缩写来减少字段名的长度"
8.	要求索引命名规则为：IX_<tabname>_<colname>
索引名限制在30个字符内。当索引名超过30字符时，可用缩写来减少索引名的长度"
9.	要求主键命名规则为：PK_<tabname>_<colname>
主键名称限制在30个字符内。当主键名超过30字符时，可用缩写来减少索引名的长度
10.	要求唯一索引命名规则为：UX_<tabname>_<colname>
唯一索引名称限制在30个字符内。当索引名超过30字符时，可用缩写来减少索引名的长度
11.	临时库、表名必须以tmp为前缀，并以日期为后缀
12.	备份库、表必须以bak为前缀，并以日期为后缀

2.数据结构设计规范：

1.	拆分访问频率低的字段，分离冷热数据
2.	用DECIMAL代替FLOAT和DOUBLE存储精确浮点数
3.	越简单越好：将字符转化为数字、使用TINYINT来代替ENUM类型
4.	新增字段加在表最后，允许为空，无默认值，避免重建表和初始化数据
5.	强制索引关键字禁用
6.	禁止在数据库中存储明文密码，把密码加密后存储
7.	用好数值类型字段

mysql常用字段类型：

MYSQL常用字段类型说明
数值型	类型	大小	范围（有符号）	范围（无符号）
	TINYINT	1 字节	(-128，127)	(0，255)
	SMALLINT	2 字节	(-32 768，32 767)	(0，65 535)
	MEDIUMINT	3 字节	(-8 388 608，8 388 607)	(0，16 777 215)
	INT或INTEGER	4 字节	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)
	BIGINT	8 字节	(-9 233 372 036 854 775 808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)
	FLOAT	4 字节	(-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)
	DOUBLE	8 字节	(1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)
	DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值
字符型	CHAR	0-255字节	定长字符串
	VARCHAR	0-255字节	变长字符串
	TINYBLOB	0-255字节	不超过 255 个字符的二进制字符串
	TINYTEXT	0-255字节	短文本字符串
	BLOB	0-65 535字节	二进制形式的长文本数据
	TEXT	0-65 535字节	长文本数据
	MEDIUMBLOB	0-16 777 215字节	二进制形式的中等长度文本数据
	MEDIUMTEXT	0-16 777 215字节	中等长度文本数据
	LOGNGBLOB	0-4 294 967 295字节	二进制形式的极大文本数据
	LONGTEXT	0-4 294 967 295字节	极大文本数据
	BINARY(M)	0-M个字节的定长字符串	包含二进制字符串，不包含非二进制字符串
	VARBINARY(M)	0-M个字节的变长字符串	包含二进制字符串，不包含非二进制字符串
	ENUM	1--255个成员，1个字节存储	忽略大小写，只允许从值集合中选取单个值，不能一次取多个值
		256--65535个成员，2个字节存储
	SET	1--8个成员，1个字节存储	一次可以选取多个成员，但多个成员不可重复取多次
		9--16个成员，2个字节存储
		17--24个成员，3个字节存储
		25--32个成员，4个字节存储
		33--64个成员，8个字节存储

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

3.索引设计规范：

1.	使用有索引的字段order by，避免排序
2.	添加索引，必须先检查是否已存在冗余索引
3.	识别度低的字段，不建议创建索引
4.	索引的数量要控制，尽量使用单列索引覆盖查询
5.	索引禁忌
(1) 不在低基数列上建立索引，例如“性别”
(2) 不在索引列进行数学运算和函数运算
6.	新建的唯一索引必须不能和主键重复

4.SQL规范：

1.	禁止select *，列出必需字段名
2.	禁止%前缀的模糊查询，因为会使索引时效
3.	尽量避免count(*)，代价过高
4.	多表关联查询，使用别名引用字段
5.	多表关联查询，先参考测试环境执行计划，检查索引扫描情况，再做判断
6.	多表查询结果关联，如果不需要去重，用union all 代替 union，避免排序
7.	读写分离环境，注意区分查询类型
8.	仅检查存在性，加limit
9.	limit分页注意效率
10.	SQL语句不可以出现隐式转换，比如 select id from 表 where id='1'
11.	IN支持小结果集，EXIST支持大结果集
12.	业务数据不允许物理删除（中间关联表，临时表可删除，但需要记录审计）
13.	增加字段，必须先检查现有表大小，字段个数以及字段类型，再做判断
14.	修改分区表，注意分区键是否包含在主键，且分区范围均匀分布
15.	避免修改字段类型，设计阶段慎重选择
16.	迁移数据前，源数据确认已备份
17.	万级以下数据，导入选择import
18.	检查迁移工具字符集，避免乱码
19.	注意数据库源库和目标库版本是否一致
20.	注意load分隔符限制
21.	注意导出文件格式选择（MYSQL支持SQL/CSV/TXT）
22.	注意校对规则
23.	总量过大的数据更新必须分批量更新

案例：

CREATE TABLE `t_mip_insight_age` (
  `brand_code` varchar(50) NOT NULL COMMENT '品牌编码',
  `category_code` varchar(50) NOT NULL COMMENT '品类编码',
  `data_type` tinyint(1) NOT NULL COMMENT '数据级别：1-品牌数据(品类名称,编码可为空),2-品类数据',
  `start_crowd_type` varchar(50) NOT NULL COMMENT '人群类型:1认知2兴趣3购买4忠诚5流失6未知(aipl、流转不为空，人群包为空)',
  `end_crowd_type` varchar(50) NOT NULL COMMENT '人群类型:1认知2兴趣3购买4忠诚5流失6未知(流转不为空，aipl、人群包为空)',
  `collect_time` date NOT NULL COMMENT '数据采集时间',
  `insight_type` tinyint(1) NOT NULL COMMENT '人群洞察类型:1AIPL人群洞察2流转人群洞察3人群包人群洞察',
  `grail_type` tinyint(1) NOT NULL COMMENT '大盘类型:1行业大盘2苏宁大盘，默认为行业大盘',
  `flow_type` varchar(50) NOT NULL COMMENT '流转人群洞察:1未流转2流转(流转不为空，aipl、人群包为空)',
  `audience_code` varchar(100) NOT NULL COMMENT '人群包人群洞察:人群包编码(人群包不为空，aipl、流转为空)',
  `age_type` tinyint(1) NOT NULL COMMENT '0(18岁以下)1(18-23)2(24-30)3(31-40)4(41-50)5(51-60)6(60以上)',
  `sex_type` tinyint(1) NOT NULL COMMENT '性别类型：0男1女',
  `insight_amount` bigint(20) NOT NULL DEFAULT '0' COMMENT '数量',
  `insight_percent` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '占比',
  `grail_amount` bigint(20) NOT NULL DEFAULT '0' COMMENT '大盘(行业或者苏宁)数量',
  `grail_percent` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '大盘(行业或者苏宁)占比',
  `compare_percent` decimal(10,4) NOT NULL DEFAULT '0.0000' COMMENT '对比大盘:占比-大盘(行业或者苏宁)占比',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`brand_code`,`category_code`,`data_type`,`start_crowd_type`,`end_crowd_type`,`collect_time`,`insight_type`,`grail_type`,`flow_type`,`audience_code`,`age_type`,`sex_type`),
  KEY `IX_insight_age_brand_code` (`brand_code`) USING BTREE,
  KEY `IX_insight_age_category_code` (`category_code`) USING BTREE,
  KEY `IX_insight_age_start_crowd_type` (`start_crowd_type`) USING BTREE,
  KEY `IX_insight_age_end_crowd_type` (`end_crowd_type`) USING BTREE,
  KEY `IX_insight_age_collect_time` (`collect_time`) USING BTREE,
  KEY `IX_insight_age_audience_code` (`audience_code`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='年龄洞察表';