MySQL-on duplicate key upate/case when语法使用

• on duplicate key update

该语法使用场景用于插入数据时候根据主键判断数据插入/更新，其主要使用语法如下：

insert into stu(id,name) values('1','小5') 
on duplicate key update name = values(name);

在上述sql语句中，插入一条id = '1'，name = '小5'的一条数据，若是表stu中已经存在id = '1'的数据，则执行update之后的语句，即将name = '5'值更新至id = '1'那条数据中；反之，则将id = ' 1',name = '小5'的新数据插入到表stu中。

拓展：解决数据重复插入的问题，还可以引用用replace into语法，replace into是将Delete、Insert两个操作合二为一。在使用replace into的时候，对应的表必须有唯一主键或者唯一索引。当数据重复时，是先执行删操作，之后再执行insert操作，此时执行replace into操作显示受影响的行为2。

replace into stu(id,name) values('1','小5')

• case when

case when的语法就是用来进行if判断的

select id,name,
(case id 
when '1' then 'X'
when '2' then 'Y'
else 'Z'
END) as other
FROM stu;
 

在上述sql脚本中，大致意思是根据id来决定other返回什么值，当id = '1'时，执行then 'X'的分支 ，即此时返回other 为X的值。

额外补充

• REPLACE

MySQL的REPACE函数可以用来实现将替换的效果。

比如：

select id,phone,REPLACE(phone,'888','xxx') as code from stu where id = '1'

执行结果： 

 sql脚本的大致逻辑为查询出id = '1'的信息，并且将xxx替换phone中的888作为code值返回，若是888字符串在phone中多次出现，则会替换多次。

REPLACE(str,from_str,to_str)可以应用于将电话号码部分数字隐藏的需求中，如下：

select id,phone,REPLACE(phone,SUBSTR(phone,4,4),'XXXX') as code from stu where id = '1';

执行结果如下： 

 SUBSTR(str,startIndex,index) 字符串截取函数

str：要截取字符的源字符串

startIndex：开始截取的位置

index：截取的长度

• ifnull

select id,ifnull(sex,'3')as sex  from stu

执行结果：

 当sex为空的时候，返回3，不为空的时候，返回sex自身

• if

select a.id,a.sex,if(age >= 18,'成年人','未成年人') as describe from stu

如上sql脚本中，当select结果集中age 大于等于18，则describe输出成年人值，反之输出未成年人值，即if else的功能，适用于特定场景下。