[转]mysql update case when和where之间的注意事项
原文地址:http://www.cnblogs.com/rwxwsblog/p/4512061.html
在日常开发中由于业务逻辑较为复杂,常常需要用到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这条尾巴。
相关文章
- Mysql加锁过程详解(7)-初步理解MySQL的gap锁
- mysql 设置 row格式binlog_为什么要把MySQL的binlog格式修改为row
- mysql高性能分页语句_如何优化Mysql千万级快速分页
- PHP - AJAX 与 MySQL
- 【MySQL高级】Mysql并发参数调整及常用SQL技巧
- 关于Mysql 查询所有表的实时记录用于对比2个MySQL 库的数据是否异步
- MySQL zabbix
- Mysql错误check the manual that corresponds to your MySQL server version for the right syntax
- 【MySQL进阶-06】深入理解mysql的内核查询成本计算
- 【MySQL进阶-03】深入理解mysql的索引分类,覆盖索引,覆盖索引失效,回表,MRR
- 怎么设置MySQL就能让别人访问本机的数据库了?
- linux mysql 执行报错
- MySQL 5.7 安装使用教程
- JDBC 连接 MySQL 异常:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key Retrieval is not allowed
- 使用navicat连接mysql连接错误:Lost connection to Mysql server at 'waiting for initial communication packet'
- 转发 可设置skip_name_resolve参数 会出现 ERROR 2005 (HY000): Unknown MySQL server host _mysql ...
- MySQL运维之---mysqldump备份、select...into outfile、mysql -e 等工具的使用
- ubuntu16.04x64环境下 tar方式 安装mysql-5.7.21 试水过程记录
- MySQL性能优化必备25条
- MySQL存储过程详解 mysql 存储过程
- mysql分组取最大(最小、最新、前N条)条记录
- Mac-修改MySQL-interactive_timeout/wait_timeout
- Amoeba for MySQL 非常好用的mysql集群软件
- MYSQL 两日期之间的工作日(除去周六日,不考虑节假日)
- mysql窗口函数、Mysql分析函数
- 修改mysql密码
- mysql 变量set
- 由浅入深探究mysql索引结构原理、性能分析与优化
- MySQL的备份与还原,非常规备份,全量备份,增量备份
- MySQL-存储过程和函数