mysql update case when和where之间的注意事项

 

　　在日常开发中由于业务逻辑较为复杂，常常需要用到UPDATE和CASE...WHEN...THEN...ELSE...END一起做一些复杂的更新。有时候因为对这几个字句理解得不透彻会带来很大的困扰。因此对UPDATE和CASE WHEN结构的特性做进一步的测试。

　　CASE WHEN的两种写法:
　　　　Type 1: CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END
　　　　Type 2: CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END

　　结论:
　　　　在第一个方案的返回结果中,value=compare-value.而第二个方案的返回结果是第一种情况的真实结果.
　　　　如果没有匹配的结果值,则返回结果为ELSE后的结果,如果没有ELSE 部分,则返回值为NULL,如果字段为NOT NULL则会根据不同数据类型返回不同的值（字符串类型时返回空字符串，数值类型时返回0，其它类型未做测试）.

注意：如果CASE...WHEN...THEN...END没有WHERE字句的话会将相应表的记录遍历一遍。

创建测试表

CREATE TABLE `goods` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL DEFAULT '',
  `type` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

测试数据

?

1 2 3 4 5 6 7 8

id　name type 1    1    1 2    2    1 3    3    2 4    4    2 5    5    3 6    6    3 7    7    4

• 没有WHERE字句时会遍历所有记录

UPDATE `goods` SET `type` = (
    CASE `name` WHEN 1 THEN 999  
    WHEN 2 THEN 1000  
    WHEN 3 THEN 1024  
    END)

　　执行结果

?

1 2 3 4

(7 row(s) affected, 4 warning(s)) Execution Time : 00:00:00:000 Transfer Time  : 00:00:00:047 Total Time     : 00:00:00:047

　　type为字符串时返回空字符串，执行后的数据

?

1 2 3 4 5 6 7

1   1   999 2   2   1000 3   3   1024 4   4   5   5   6   6   7   7

　　type为整形时返回0，执行后的数据

?

1 2 3 4 5 6 7

1   1   999 2   2   1000 3   3   1024 4   4   0 5   5   0 6   6   0 7   7   0

• 添加where字句

UPDATE `goods` SET `type` = (
    CASE `name` WHEN 1 THEN 999  
    WHEN 2 THEN 1000  
    WHEN 3 THEN 1024  
    END)
WHERE ID in(1, 2, 3);

　　执行结果

?

1 2 3 4

(3 row(s) affected) Execution Time : 00:00:00:000 Transfer Time  : 00:00:00:016 Total Time     : 00:00:00:016

　　执行后的数据

?

1 2 3 4 5 6 7

1   1   999 2   2   1000 3   3   1024 4   4   4 5   5   5 6   6   6 7   7   7

　　由此可见，做UPDATE时WHERE字句是多么的重要。做UPDATE更新时请时刻记住WHERE这条尾巴。