概要

回顾以前写的项目，发现在规范的时候，还是可以做点骚操作的。 假使以后还有新的项目用到了 MySQL ，那么肯定是要实践一番的。 为了准备，创建测试数据表（建表语句中默认使用utf8mb4以及utf8mb4_unicode_ci，感兴趣的读者可以自行搜索这两个配置）：

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `no` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '编号',
  `name` varchar(30) NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

插入冲突时更新数据

SQL执行插入时，可能因为种种原因插入失败，比如UNIQUE索引冲突导致插入失败。比如某个不晓得DBA插入了一条错误的学生记录("3", "小明")，悲剧的是小明的编号是1。常规做法就是判断当前的 数据库 记录中是否存在小明的记录，如果有则更新其对应其编号，否则就插入小明的记录。当然存在更好的做法：

INSERT INTO student(`no`, `name`) VALUES (3, "xiaoming");
INSERT INTO student(`no`, `name`) VALUES (1, "xiaoming"), (2,"xiaohong")
ON DUPLICATE KEY UPDATE `no` = VALUES(`no`);

那就是使用ON DUPLICATE KEY UPDATE，这是mysql独特的语法（语句后面可以放置多个更新条件，每个条件使用逗号隔开即可）。需要注意，这里的VALUES(no)是将冲突的no数值更新为用户插入数据中的no，这样每条冲突的数据就可以动态的设置新的数值。

忽略批量插入失败中的错误

批量插入比单条数据挨个插入，普遍会提高性能以及减少总的网络开销。但是，假如批量插入的数据中心存在一个臭虫，在默认的情况下，这就会导致批量插入失败（没有一条数据插入成功）。当然，我们可以选择忽略， MongoDB 都能够做到的事情，MySQL自然是可以做到。

INSERT INTO student(`no`, `name`) VALUES (1, "xiaoming");
INSERT IGNORE INTO student(`no`, `name`) VALUES (1, "xiaoming"), (2,"xiaohong"),(3, "xiaowang");

只需要在批量插入的语句中，插入IGNORE，那么某几条数据的插入失败就会被忽略掉，正确的数据依然可以插入库中。但是，我建议这个功能谨慎使用，使用mysql数据库本身就是看中数据的正确性，没必要为了批量插入的性能而自动放弃数据的正确性，如果真心觉得这个数据不重要，那么为什么不将此数据存入NoSQL中呢，MongoDB就是不错的选择。

IGNORE还有些副作用，感兴趣的可以自行查询。

使用JOIN替换子查询

MySQL的子查询优化不是太好，它的运行有点反我们的直觉（我们写的代码终究会在某些时候和我们的直觉相悖，这大概就是优化产生的根源之一吧）。其中最糟糕的一类是WHERE子句中包含IN的子查询语句（详情可见《高性能MySQL》一书的6.5章节,标题名字起得就很nice，为MySQL查询优化器的局限性）。概括下就是在部分情况下，在部分情况下MySQL可能会在挨个执行外部记录时执行子查询，如果外部记录数量较大，那么性能就会堪忧。

SELECT * FROM student WHERE no > (SELECT no FROM student WHERE `name`='xiaoming');
SELECT s.* FROM student s JOIN (SELECT no FROM student WHERE `name`='xiaoming') t ON s.no > t.no;

看上述代码，可以知道使用JOIN还是比较容易替换子循环，代码虽然会稍显晦涩，但是也许可以避免在并发量大的某个晚上你被叫起来检讨自己的错误。MySQL一直在优化子查询，在部分条件下子查询可能会比JOIN具有更高的效率，因此在有时间进行验证的情况下选择最佳的SQL语句。

JOIN中的WHERE和AND坑

为了更好的说明坑，我这里需要创建一个新的表，并在原来的学生表中添加字段:

CREATE TABLE `class` (
  `id` smallint(6) unsigned NOT NULL AUTO_INCREMENT,
  `no` int(10) unsigned NOT NULL COMMENT '编号',
  `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  UNIQUE KEY `unq_no` (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;