数据库内核月报 - 2015 / 04

MySQL · 答疑释惑 · UPDATE交换列单表和多表的区别

背景描述

之前我们遇到一个咨询,客户说:
1. 同一个表,col1=a,col2=b,做 update,set col1=col2,col2=col1,这时候两个都是b
2. 不同表,A表 col1=a,B表 col2=b,做 update,就能进行交换
为什么不同表就能交换呢?

问题实验

一张表的测试

root@localhost : test 12:36:09> select * from upt;
+------+------+  
| c1   | c2   |  
+------+------+  
| a    | b    |  
+------+------+  
1 row in set (0.03 sec)  
root@localhost : test 12:36:20> update upt set c1=c2,c2=c1;
Query OK, 1 row affected (2 hours 47 min 59.80 sec)
Rows matched: 1  Changed: 1  Warnings: 0
root@localhost : test 03:24:32> select * from upt;
+------+------+  
| c1   | c2   |  
+------+------+  
| b    | b    |  
+------+------+  
1 row in set (0.00 sec)  

两张表的测试

root@localhost : test 02:45:13> select * from upt1;
+------+------+------+  
| c1   | c2   | id   |  
+------+------+------+  
| a    | b    |    1 |  
| c    | d    |    2 |  
+------+------+------+  
2 rows in set (0.00 sec)  
root@localhost : test 02:45:18> select * from upt2;
+------+------+------+  
| c1   | c2   | id   |  
+------+------+------+  
| e    | f    |    1 |  
| g    | h    |    2 |  
+------+------+------+  
2 rows in set (0.00 sec)  
root@localhost : test 02:47:50> update upt1, upt2 set upt1.c1=upt2.c1, upt2.c1=upt1.c1 where upt1.id=upt2.id;
Query OK, 4 rows affected (0.04 sec)  
Rows matched: 4  Changed: 4  Warnings: 0  
root@localhost : test 02:48:25> select * from upt1;
+------+------+------+  
| c1   | c2   | id   |  
+------+------+------+  
| e    | b    |    1 |  
| g    | d    |    2 |  
+------+------+------+  
2 rows in set (0.00 sec)  
root@localhost : test 02:48:35> select * from upt2;
+------+------+------+  
| c1   | c2   | id   |  
+------+------+------+  
| a    | f    |    1 |  
| c    | h    |    2 |  
+------+------+------+  
2 rows in set (0.01 sec)  

问题分析

一张表的情况

UPDATE并没有把c1和c2列的值做交换,而是用c2列的值覆盖了c1列的值。而如果c1和c2来自不同的表,则会交换值,原因何在呢?

单张表的UPDATE函数入口为 mysql_uptate(),函数有两个参数 List<Item> &fields,List<Item> &values 分别表示要修改的列,和它们的目标值。

在上面例子中SET子句等号的左边,依次出现的是c1和c2,所以在fields数组中,顺序是field(c1)->field(c2),在SET子句等号的右边,依次出现的是c2和c1,所以在values数组中,顺序是value(c2)->value(c1)。

对于单表UPDATE,MySQL调用了read_record()来读取values,所以会得到 value(c2).str_value=’b’->value(c1).str_value=’a’。然后在fill_record()中,根据fields的顺序依次调用value->save_in_field()来把values填入fields。

因此value(c2)会被首先赋值给field(c1),因此field(c1).str_value=’b’,然后value(c1).str_value此时已经成为了’b’,因此value(c1)复制给filed(c2)依然还是’b’。

我们用三个列来验证我们的分析

root@localhost : test 03:54:55> select * from upt;
+------+------+------+  
| c1   | c2   | c3   |  
+------+------+------+  
| a    | b    | c    |  
+------+------+------+  
1 row in set (0.01 sec)  
root@localhost : test 03:55:05> update upt set c1=c2, c2=c3, c3=c1;
Query OK, 1 row affected (0.00 sec)  
Rows matched: 1  Changed: 1  Warnings: 0  
root@localhost : test 03:55:45> select * from upt;
+------+------+------+  
| c1   | c2   | c3   |  
+------+------+------+  
| b    | c    | b    |  
+------+------+------+  
1 row in set (0.00 sec)  

可见,c1被赋值为c2的时候,c2还是’b’,c2被赋值为c3的时候,c3还是’c’。但是当c3被赋值为c1的时候,c1之前已经被赋值为’b’,所以c3也就成了’b’。

两张表的分析

对于不同表的UPDATE,MySQL调用的是mysql_multi_update(),定义一个multi_update类来处理,最终在 multi_update::do_updates() 中进行修改。

这里有什么不同的呢?

通过调研 multi_update::do_updates() 函数发现,multi_update类中的copy_field数组暂存了要更新的列值

for ( ; *field ; field++)  
{  
Item_field *item= (Item_field* ) field_it++;  
(copy_field_ptr++)->set(item->field, *field, 0);  
}  

然后从原表中读取一行记录,并存到table->record[1],

tbl->file->ha_rnd_pos(tbl->record[0], (uchar *) tmp_table->field[field_num]->ptr)))  
...  
store_record(table,record[1]);  

接着再把暂存的列值拷贝回table->record[0],

for (copy_field_ptr=copy_field;  
     copy_field_ptr != copy_field_end;  
     copy_field_ptr++)  
  (*copy_field_ptr->do_copy)(copy_field_ptr);    

最后调用ha_update_row这个API更新这行数据,

local_error= table->file->ha_update_row(table->record[1], table->record[0]);

这样就不会因为列值被修改,而导致后续利用列值更新其他列的时候值变化了,这就是UPDATE多表和单表逻辑中区别的关键。