MySQL数据库SQL开发手册目录一、编写目的 (4)(一)简介 (4)(二)范围 (4)(三)术语和定义 (4)二、创建规范 (5)(一)通用 (5)(二)兼容性规约 (5)(三)建表规约 (6)(四)主键规约 (7)(五)索引规约 (7)三、开发规范 (9)(一)通用 (9)(二)Insert (9)(三)Delete (10)(四)Update (10)(五)Select (10)(六)From (11)(七)Where (11)(八)Group by (11)(九)Having (12)(十)Order by (12)(十一)Limit (12)四、优化规范 (13)(一)通用 (13)(二)Insert (14)(三)Update (14)(四)Delete (14)(五)Join (14)(六)or (14)(七)Group by (15)(八)Order by (15)(九)Limit (15)(十)子查询 (16)五、附录 (17)(一) A 保留字 (17)一、编写目的(一) 简介针对关系型数据库MySQL的相关特性,拟定的相关开发过程的规范,其旨在通过该规范的约束和建议,使开发人员可以在他们所编写的代码中保持统一正确的风格,提供代码的可读性以及减少出现错误的几率。
(二) 范围本规范仅适用于环境MySQL数据库以及其对应的SQL脚本语言。
(三) 术语和定义本规范采用以下的术语描述:★强制:必须遵从使用的原则。
★规则:如无特殊情况,应该遵守的原则。
★推荐:建议遵守的原则。
★参考:开发人员可以参考了解的原则。
二、创建规范(一) 通用1. 【强制】表必须有主键2. 【强制】禁用外键3. 【强制】禁用视图4. 【强制】禁用分区表5. 【强制】禁用使用MyISAM引擎6. 【规则】日志类表:a) 不入库,存成文件;b) 入库,明确生命周期,保留业务必须数据,定期清理。
7. 【推荐】库名与应用名称尽量一致,长度控制在15个字符以内。
8. 【参考】合适的字符存储长度,不但节约数据库表空间、节约索引存储,更重要的是提升检索速度。
正例:人的年龄用unsigned tinyint(范围0-255,人的寿命不会超过255 岁);海龟就必须是smallint,但如果是太阳的年龄,就必须是int;如果是所有恒星的年龄都加起来,那么就必须使用bigint。
(二) 兼容性规约1. 【强制】禁用触发器、存储过程、自定义函数、EVENTS2. 【规则】禁止使用hint3. 【强制】禁用数据库保留字,如desc、range、match、delayed 等,详见附录A 保留字4. 【强制】禁止使用字段属性default current_timestamp on updatecurrent_timestamp,该功能直接在程序中实现。
5. 【强制】禁止使用encryption、key_block_size、avg_row_length、max_rows、min_rows等表的属性(三) 建表规约1. 【强制】所有业务表必须使用Innodb引擎2. 【强制】字符集和应用层必须保持一致UTF83. 【强制】必须显式指定engine和charset4. 【强制】表和字段应该有必要的注释5. 【强制】创建表时,必须创建主键6. 【强制】不同表之间的相同字段或者关联字段,字段类型要保持一致7. 【强制】临时表、备份表、历史表要使用后缀tmp、bak、his字段标明,并提供数据生命周期8. 【强制】表达是与否概念的字段,必须使用is_xxx 的方式命名,数据类型是unsigned tinyint( 1 表示是,0 表示否)。
说明:任何字段如果为非负数,必须是unsigned。
9. 【强制】表名、字段名必须使用小写字母或数字,长度限制在15个字符以内;禁止出现数字开头,禁止两个下划线中间只出现数字。
数据库字段名的修改代价很大,因为无法进行预发布,所以字段名称需要慎重考虑。
正例:getter_admin,task_config,level3_name反例:GetterAdmin,taskConfig,level_3_name10. 【强制】小数类型为decimal,禁止使用float 和double。
说明:float 和double 在存储的时候,存在精度损失的问题,很可能在值的比较时,得到不正确的结果。
如果存储的数据范围超过decimal 的范围,建议将数据拆成整数和小数分开存储。
11. 【强制】表必备三字段:id, xxx_create, xxx_modified。
说明:其中id 为主键,类型为unsigned bigint,xxx_create, xxx_modified的类型均为datetime 类型,分别记录该条数据的创建时间、修改时间。
12. 【强制】如果存储的字符串长度几乎相等,使用CHAR 定长字符串类型。
13. 【强制】varchar 是可变长字符串,不预先分配存储空间,长度不要超过255,如果存储长度大于此值,建议不入库直接存成文件14. 【规则】禁止在数据库中使用大字段(blob/text)15. 【规则】单表字段数上限不得超过30个16. 【推荐】表行数不允许超过500W,超过500W时,推荐分表或冷数据下线。
17. 【推荐】使用整数保存ip地址和timestamp时间(四) 主键规约1. 【强制】禁止使用复合索引作为主键2. 【强制】禁止更新主键列3. 【规则】不使用字符串列、UUID、MD5、HASH 作为主键4. 【规则】主键要求:1) 主键字段长度要尽量小2) 主键选择数值型,使用自增或全局ID,并禁止更新3) 全局ID的生成规则:前缀+流水号规则。
前缀:时间戳等有序数字。
比如:snowflake算法把时间戳、工作机器id、序列号组合在一起。
(五) 索引规约1. 【强制】禁止使用外键,一切外键概念必须在应用层解决2. 【强制】禁止一个表上索引超过5个3. 【强制】唯一索引名为uk_字段名;普通索引名则为idx_字段名。
说明:uk_ 即unique key;idx_ 即index 的简称。
4. 【强制】在varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本区分度决定索引长度。
说明:索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为20 的索引,区分度会高达90%以上,可以使用count(distinct left(列名, 索引长度))/count(*)的区分度来确定。
5. 【规则】禁止一个字段上建有多个索引6. 【规则】命名以idx_开通,长度控制在15个字符以内。
7. 【规则】区分度低的字段,避免创建索引8. 【规则】复合索引原则:1) 复合索引中的字段数不超过3个2) 复合索引字段的顺序,区分度大的列放在前9. 【推荐】利用覆盖索引来进行查询操作,来避免回表操作。
说明:如果一本书需要知道第11 章是什么标题,会翻开第11 章对应的那一页吗?目录浏览一下就好,这个目录就是起到覆盖索引的作用。
正例:IDB 能够建立索引的种类:主键索引、唯一索引、普通索引,而覆盖索引是一种查询的一种效果,用explain 的结果,extra 列会出现:using index.三、开发规范(一) 通用1. 【强制】禁止三个表以上的关联2. 【强制】禁止跨库操作(比如:select、update等)3. 【强制】禁止无条件或永真条件的update、delete操作4. 【强制】禁止频繁的commit5. 【强制】禁止SQL语句检索行数超过100万,否则中断该数据库连接6. 【强制】禁止SQL语句返回行数超过1万,否则截取仅返回1万行7. 【强制】禁止DDL语句(truncate、alter、drop等)8. 【规则】使用explain的rows判断扫描行数,key是否使用索引9. 【规则】避免使用子查询、or,将子查询转化为表连接方式,or转化为in10. 【规则】SQL语句书写要规范1) SQL语句统一使用小写,每个词只允许有一个空格2) 避免同一SQL书写格式的不同而导致多次语法分析11. 【强制】用户请求传入的任何参数必须做有效性验证。
说明:忽略参数校验可能导致:page size 过大导致内存溢出恶意order by 导致数据库慢查询正则输入源串拒绝服务ReDOS任意重定向SQL 注入,例如sleep(10)Shell 注入反序列化注入(二) Insert1. 【规则】insert时建议指定字段名,避免字段顺序变动后数据插入错误2. 【规则】当有大量insert操作时,批量进行操作示例:一次插入多行数据insert into `t1`(`id`,`name`) values (`1`,`zhangsan`),(`2`,`lisi`),(`3`,`wang wu`);或一次一行时,使用事务延迟提交start transaction;insert into `t1`(`id`,`name`) values (`1`,`zhangsan`);insert into `t1`(`id`,`name`) values (`2`,`lisi`);insert into `t1`(`id`,`name`) values (`3`,`wangwu`);commit;(三) Delete1. 【强制】禁止delete语句无where条件或有where条件但没使用索引,否则中断该连接2. 【规则】删除大量数据时,使用limit和order by主键进行必要的分批提交(四) Update1. 【强制】禁止update语句无where条件或有where条件但没使用索引,否则中断该连接2. 【规则】更新大量数据时,使用limit和order by主键进行必要的分批提交(五) Select1. 【强制】明确查询的字段,禁止使用select *2. 【强制】禁止使用select …for update3. 【强制】不要使用count(列名)或count(常量)来替代count(*),count(*)就是SQL92定义的标准统计行数的语法,跟数据库无关,跟NULL和非NULL 无关。
说明:count(*)会统计值为NULL 的行,而count(列名)不会统计此列为NULL 值的行。
4. 【强制】count(distinct col)计算该列除NULL之外的不重复数量。
注意count(distinct col1, col2) 如果其中一列全为NULL,那么即使另一列有不同的值,也返回为0。
5. 【强制】当某一列的值全是NULL 时,count(col)的返回结果为0,但sum(col)的返回结果NULL,因此使用sum()时需注意NPE(Null Pointer Exception)问题。