mysql及相关函数等

 函数：

1、with rollup及coalesce的用法

with rollup的话是在group by的基础上再进行所有数据的统计。

coalesce（a，b）：如果a为null，那么就选择b，如果a不为为null，就选择a

举个例子，比如有个需求，需要按照统计某个时间段的数据，最后数据还要进行统计，下面sql如下。

由于统计总数据为1242，但是前面未标明是什么信息，因此，使用coalesce函数。

2、union、union all 记录联合

可能会碰到这样的需求，需要查询出2张表共同拥有的数据进行展示，这时需要用到union关键字，union all的话是多张表查询出来的不会进行去重，而union则会进行去重。

3、批量更新

普通的更新语句格式为：update 表名 set 属性值='xxxx' ，如 update table_user set name='哈哈哈'

而批量更新的话mysql并没有提供语法，但是我们可以用case when来进行操作。

需求：

当code=2001，id=1时，将result设置为90，state设置为70；

当code=2001，id=2时，将result设置为80，state设置为120；

sql语句：

UPDATE table_user 
SET result= 
CASE 
WHEN(code=2001 and id=1)
 THEN 90
WHEN(code=2001 and id=2)
 THEN 70
END,
state= 
CASE
WHEN(code=2001 and id=1)
 THEN 80
WHEN(code=2001 and id=2)
 THEN 120
END
WHERE (code=2001 and id=1) OR (code=2001 and id=2)　　

*** case when的话可以有2个写法，一个是case 条件 when，另外一个是case when，第一个的话比如sex在数据库存储的话是1、2这种类型，那么case sex when '1' then '男' 这种就能很好的表达。

在mybatis.xml的写法如下：

<update id="updateBatch" parameterType="list">
 UPDATE table_user
<trim perfix="SET" suffixOverrides=",">
  <trim prefix="result =CASE" suffix="END,">
     <foreach collection="list" item="item" index="index" >
        <if test ="item.result!=null || item.result!=''">
           WHEN code=#{item.code} AND id=#{item.id}
            THEN #{item.result}
        </if> 
     </foreach>
  </trim>
  <trim prefix="state=CASE" suffix="END,">
     <foreach collection="list" item="item" index="index">
        <if test="item.state!=null || item.state!=''">
        WHEN code=#{item.code} AND id=#{item.id}
        THEN #{item.state}
        </if>
     </foreach>
  </trim>
</trim>
<WHERE>
  <foreach collection="list" item="item" index="index" separator="or">
   code=#{item.code} AND id=#{item.id}
</WHERE>
</update>     

　　最后的where语句里面的foreach的sql和when一样，需要注意的是，separator是or

 Mapper传入的参数为实体类的List类型，如void updateBatch(List<User> userList)

 

 mysql的每种存储引擎对每个表至少支持16个索引