一个程序员在工作中难免会与数据库打交道，有的是写在程序中的；有的是需要直接在生产环境中执行一些修改数据的sql；有的是修改表结构 等等。这样的情况每一次都提心吊胆，生怕执行错误，无法恢复。久而久之，也算是总结了一点书写的经验，现记录一下。
0. 修改或删除重要数据前，要先备份，先备份，先备份
1. 写完查询 SQL 先 查看执行计划
日常开发写SQL的时候，尽量养成这个好习惯：写完SQL后，分析一下，尤其注意走不走索引。
-- 分析方式
-- 1. explain
explain select userid,name,age from user where userid =10086 or age =18;
-- 2. desc
desc select userid,name,age from user where userid =10086 or age =18;
-- 3. desc format=json
desc format=json select userid,name,age from user where userid =10086 or age =18;
-- 4. desc analyze
desc analyze select userid,name,age from user where userid =10086 or age =18;
2. 操作 delete 或者 update 语句，加 limit
注意：尤其是范围删除
- 降低写错 SQL 的代价
- SQL 效率可能会提高，提前命中，避免全表扫描
- 避免长事务
- 数据量大的情况下也不会占满CPU
delete from user where age > 30 limit 200;
3. 设计表，所有的字段都应该加相应的注释
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`name` varchar(255) DEFAULT NULL COMMENT '账户名',
`balance` int(11) DEFAULT NULL COMMENT '余额',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
4. SQL 书写，关键字大小写保持一致，使用缩进
SELECT stu.name, sum(stu.score)
FROM Student stu
WHERE stu.classNo = '1班'
GROUP BY stu.name
5. INSERT 语句标明对应的字段名称
-- 提倡
insert into Student values ('666','小孩子真可爱','100');
-- 不提倡
insert into Student(student_id,name,score) values ('666','小孩子真可爱','100');
6. 设计数据库表结构，加三个字段 主键（id）、create_time、update_time
- 主键一般都要加上的，没有主键的表是没有灵魂的
- 创建时间和更新时间的话，建议加上，详细审计、跟踪记录，都是有用的。
- update_time 字段建议加上 ON UPDATE ，也就是在更新数据库记录的时候直接保留更新时间
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
`name` varchar(255) DEFAULT NULL COMMENT '账户名',
`balance` int(11) DEFAULT NULL COMMENT '余额',
`create_time` datetime NOT NULL COMMENT '创建时间',
`update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
PRIMARY KEY (`id`),
KEY `idx_name` (`name`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1570068 DEFAULT CHARSET=utf8 ROW_FORMAT=REDUNDANT COMMENT='账户表';
7. 写完SQL语句，检查where,order by,group by后面的列，多表关联的列是否已加索引，优先考虑组合索引
8. where后面的字段，留意其数据类型的隐式转换
当一个属性类型为字符串
// userid 是varchar字符串类型
// 错误
select * from user where userid = 123;
// 正确
select * from user where userid = '123';
因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为浮点数再做比较，最后导致索引失效
9. 尽量把所有列定义为NOT NULL
- 「NOT NULL列更节省空间」，NULL列需要一个额外字节作为判断是否为 NULL 的标志位。
- 「NULL列需要注意空指针问题」，NULL列在计算和比较的时候，需要注意空指针问题。
10. 尽量使用varchar代替 char
11. SQL命令行修改数据，养成begin + commit 事务的习惯
12. 索引命名要规范，主键索引名为 pk_ 字段名；唯一索引名为 uk _字段名 ；普通索引名则为 idx _字段名
13. 如果修改/更新数据过多，考虑批量进行。
- 大批量操作会会造成主从延迟。
- 大批量操作会产生大事务，阻塞。
- 大批量操作，数据量过大，会把cpu打满。
// 错误
delete from account limit 100000;
// 正例
for each(200次)
{
delete from account limit 500;
}
14. 尽量不要返回 group by 后不存在的列（主键除外）
15. 建表/排序 需要注意排序规则
在 mysql 中 创建数据库、表 的时候有一个 COLLATE 参数可以设置。
CREATE TABLE `order` (
`id` bigint NOT NULL AUTO_INCREMENT,
`code` varchar(20) COLLATE utf8mb4_bin NOT NULL,
`name` varchar(30) COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `un_code` (`code`),
KEY `un_code_name` (`code`,`name`) USING BTREE,
KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
它的作用是用来排序的。字符排序规则跟字符集有关，比如：字符集如果是 utf8mb4，则字符排序规则也是以：utf8mb4_ 开头的，常用的有：utf8mb4_general_ci、utf8mb4_bin、utf8mb4_0900_ai_ci（mysql8之后新增的，并且默认）等。
- utf8mb4_general_ci 排序规则，对字母的大小写不敏感。说得更直白一点，就是不区分大小写。
- utf8mb4_bin 排序规则，对字符大小写敏感，也就是区分大小写。
- UTF8MB4_0900_AI_CI 这是一个基于AI（Accent Insensitive）规则的排序规则，对于重音符号不敏感，也同样对字母的大小写不敏感。例如，字母"a"和"á"将被视为相等。
‒ UTF8MB4_0900_BIN 对字符大小写敏感
utf8mb4_0900_ai_ci 是在MySQL 8.0.0版本后引入的一种排序规则。它基于Unicode Collation Algorithm，支持对多种语言进行排序，并考虑了不同语言的特殊规则和排序顺序。
utf8mb4_general_ci 则是MySQL较早版本中使用的排序规则。它对字符进行简单的二进制比较，不考虑各种语言的排序规则和特殊情况。相比于utf8mb4_0900_ai_ci，utf8mb4_general_ci 能够提供更快的排序性能，但在某些特殊情况下可能会导致排序结果不符合用户的期望。
-- 插入三条 名字区分大小写的数据
INSERT INTO no_up_down_test (name, age, create_time) VALUES ('SUXINGKANG', 27, DEFAULT);
INSERT INTO no_up_down_test (name, age, create_time) VALUES ('suxingkang', 28, DEFAULT);
INSERT INTO no_up_down_test (name, age, create_time) VALUES ('SuXingKang', 18, DEFAULT);
select * from no_up_down_test nudt where nudt.name = 'suxingkang';
-- utf8mb4_general_ci、UTF8MB4_0900_AI_CI 排序规则下 三条数据都可以查询到
-- UTF8MB4_0900_BIN、utf8mb4_bin 只能查到一个
注意：我们目前系统使用数据库版本为 mysql 8.0.22，但是 以 sys_ 开头的表结构中，排序规则设置为 utf8mb4_general_ci。