比CRUD多一点儿（三）：UPDATE、DELETE语句

摘要：语句最基础的用法不再赘述。而这样删掉整张表的语句是不会清空自增值的。

这是MySQL系列笔记的第一部分，本系列笔记希望能按照笔者自己学习MySQL技术的经历来记录，避免纯粹按照内容一块一块总结，也就是不同于一般按内容分配章节的书籍的结构，会有一个平滑的阅读曲线。内容比较丰富的技术点会按照专题在多个学习笔记中逐渐深入。

这部分的标题叫比CRUD多一丁点儿，比起最基础的w3c的SQL教程之外，只多一点的扩展，满足应付从纯粹阅读入门资料到可以上手完成一个简单的工作的需求。

第三篇的主要内容继续是基础SQL语句UPDATE和DELETE，这两个相对比较简单，在运维和处理线上数据问题时候可能比较常用，在开发过程中基本上都只会使用最基础的操作。

最基础的用法不再赘述。说一下当使用DELETE删除的数据需要用一个条件关联来查找而不止是简单的where条件时候，写法是这样的：

DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

注意如果用了alias，删除语句也必须用alias不能再用表名：

DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2
WHERE a1.id=a2.id;   -- 正确
DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2
WHERE a1.id=a2.id;   -- 错误

一定要提醒的是，DELETE和UPDATE一定记得加限定条件，否则很容易就可能酿成一桩惨案，因为数据库需要保证MVCC（Multiversion concurrency control，是事务的基础），这类更新操作的开销很大甚至需要锁表。笔者自己在线上数据库做这些操作包括SELECT时候手已经都已经形成了下意识，一定会先写下where条件，Ctrl-A再开始写update,select。

相对刚才说的DELETE操作很慢，如果写单元测试和自己用的开发库需要快速的清空一个表时候要怎么做呢？答案是使用TRUNCATE。

注意使用TRUNCATE时候如果表有设置自增列，比如自增的主键，是会被清空到初始值的，也就是TRUNCATE之后。而DELETE FROM t;这样删掉整张表的DELETE语句是不会清空自增值的。另外如果有外键关联，那么就需要使用set foreign_key_checks来去掉外键检查：

set foreign_key_checks = 0;
truncate Account;
set foreign_key_checks = 1;

这个特性还引起一个笔者开发时候碰到的很有意思的异常：就是当这样清空了某张表t的主键后，与其有关联关系的表a没有清空，随后在t中新插入数据，自增ID重新从1开始增加。结果a中的一些旧数据结果就跟t中的新数据关联上了。哈哈哈，当时着实是觉得是不是闹鬼了，查了挺久才发现。

最基础的用法不再赘述。看下面例子：

UPDATE t1 SET yourname = realname + ".avi";

设置的值其实是一个表达式，其实可以这样用别的列表，也可以同时更新两个表，也就是用上JOIN：

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

其实和在select中直接select两个表不用写join一样，这其实也是join，只是是inner join的一种简写。那么更新还可以更新自身列：

UPDATE t1 SET counting = counting + 1 where id = 2;

列值这样自增是实际开发中一个很常用的技巧，用在计数统计时候非常方便，而且也免得需要自己去面对先读后写在并发冲突时候引起的值覆盖问题。然后还可以加order by和limit来做只更新符合条件的前几个：

UPDATE t SET counting = counting + 1 ORDER BY id DESC LIMIT 10;

同样上面这个自增自身的例子，有些特殊的情况很有意思，即当要更新的主键自增id。那么如果不加order by直接更新的话：

UPDATE t SET id = id + 1;   -- 这是错误的

会报id冲突的错误，无法执行。增加了order by就可以顺利执行了：

UPDATE t SET id = id + 1 ORDER BY id DESC;

一定要提醒的是，DELETE和UPDATE一定记得加限定条件，否则很容易就可能酿成一桩惨案，因为数据库需要保证MVCC（Multiversion concurrency control，是事务的基础），这类更新操作的开销很大甚至需要锁表。笔者自己在线上数据库做这些操作包括SELECT时候手已经都已经形成了下意识，一定会先写下where条件，Ctrl-A再开始写update,select。