mysql批量更新及拆分_mysql 批量拆表

        前阵子有个脚本执行特别慢，根据一张表中查询的数据来更新另一张表数据。每晚18:00开始执行，之前一般大概执行到21、22点左右，最近有几次竟然直到凌晨才跑完，这就有点接受不了了。捋了一下脚本，是每查出一条就去更新一条，每次更新时都得连库、操作、然后关闭。就想着能不能一次更新多条呢，也就是写个批量更新语句。
        假设表结构如下：

CREATE TABLE `test_book` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `book_name` varchar(32) NOT NULL default '' COMMENT '书名',
  `uid` int unsigned NOT NULL DEFAULT 0 COMMENT '作者id',
  `u_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `c_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8

        如果想根据id批量更新book_name字段，可以如下书写：

UPDATE test_book SET
    book_name = CASE id
        WHEN 1 THEN '三国演义'
        WHEN 2 THEN '红楼梦'
        WHEN 3 THEN '水浒传'
    END
WHERE id IN (1,2,3)

        这样一条语句即可更新三条记录，对id=1的书名更新为三国演义，id=2的书名更新为红楼梦，以此类推。如果是同时更新多个字段，譬如书名和作者id：

UPDATE test_book SET
    book_name = CASE id
        WHEN 1 THEN '三国演义'
        WHEN 2 THEN '红楼梦'
        WHEN 3 THEN '水浒传'
    END,
    uid = CASE id
        WHEN 1 THEN 4
        WHEN 2 THEN 5
        WHEN 3 THEN 6
    END
WHERE id IN (1,2,3)

脚本改成批量更新后，运行时间瞬间成为原来的10%，十几分钟顶多半小时就执行完了。

然而随着批量更新的数据量越来越大，又报了另一个问题导致脚本中断了。

Communication link failure: 1153 Got a packet bigger than 'max_allowed_packet' bytes

查看mysql该参数配置：

mysql> select @@max_allowed_packet;
+----------------------+
| @@max_allowed_packet |
+----------------------+
|             16777216 |
+----------------------+
1 row in set (0.01 sec)

也就是16M。解决方法有两种，要么联系dba扩大该参数值；要么拆分数据。我这里是两种方法都采用了，一方面联系dba将该参数值调整为32M，一方面在代码层面做了修改，一次只更新20000条。

$total = count($res);
echo '共'. $total. "条数据\n";
$k = 0;
while ($k < $total) {
     $temp = array_slice($res, $k, 20000);
     $success = $model->helper($temp);
     echo '成功修改'. $success. "\n";
     $k += 20000;
}

至此，批量更新彻底解决。