mysql特殊用法

批量更新

优势:可以大大减少数据连接次数,提升程序处理速度;
劣势:sql语句较复杂,在修改字段过多时,还可能出现字符串超处理长度问题

UPDATE oa_role_staff 
SET role_id =
CASE
	staff_base_info_id 
	WHEN 1958 THEN 1 
	WHEN 2138 THEN 1 
	WHEN 2148 THEN 1 
END 
WHERE
	role_id = -1 
	AND staff_base_info_id IN ( 1958, 2138, 2148 );

实际应用中,一般结合mybatis中的foreach使用;例如:

<update id="batchUpdateStaffRole" parameterType="map">
    update
      oa_role_staff
    SET role_id =
    <foreach collection="staffInfoList" item="item" index="index"
             separator=" " open="case staff_base_info_id" close="end">
      when #{item.id} then #{targetRoleId}
    </foreach>
    where role_id = #{sourceRoleId} and  staff_base_info_id in
    <foreach collection="staffInfoList" index="index" item="item"
             separator="," open="(" close=")">
      #{item.id}
    </foreach>
  </update>

递归查询(有bug)

现在网上很多介绍递归查询,都是使用存储过程,可是现在很多项目是不推荐存储过程进行编码的,以下介绍不使用存储过程的不数据库递归查询
先上代码:

SELECT
   id 
FROM
   (
SELECT
   t1.id,
IF
   ( find_in_set( parent_id, @pids ) > 0, @pids := concat( @pids, ',', id ), 0 ) AS ischild 
FROM
   ( SELECT id, parent_id FROM oa_department t order by parent_id, id ) t1,
   ( SELECT @pids := 1 ) t2 -- 1为需要的递归的父id
   ) t3 
WHERE
   ischild != 0

过程中使用到的函数:
find_in_set(str,strList):
假如字符串str在由N个子链组成的字符串列表strlist 中，则返回值的范围在 1 到 N 之间。 一个字符串列表就是一个由一些被 ‘,’ 符号分开的子链组成的字符串。如果第一个参数是一个常数字符串，而第二个是type SET列，则FIND_IN_SET() 函数被优化，使用比特计算。 如果str不在strlist 或strlist 为空字符串，则返回值为 0 。如任意一个参数为NULL，则返回值为 NULL。这个函数在第一个参数包含一个逗号(‘,’)时将无法正常运行。(引用:https://www.jianshu.com/p/b2c1ba0ba34f)
"@"的作用:用于标识变量(mysql中变量不用事前申明，在用的时候直接用“@变量名”使用就可以了)
":="的作用:select时的赋值.
IF(expr1,expr2,expr3):类似java的三元表达式: var result = expr1 ? expr2 : expr3(引用:https://www.cnblogs.com/zjdxr-up/p/8383609.html)