mysql 批量删除 in 中可以用子查询吗 mysql 批量删除数据

转载

mob64ca13f4c367 2024-08-23 22:55:21

目录
批量删除
批量添加
递归查询
批量更新
Mysql中批量替换某个字段的部分数据
批量为某一列的值添加后缀
递归详解-2021-07-28补充
函数定义
变量和循环
使用
批量删除

int deleteByAlbumIds(int[] ids);
  <delete id="deleteByAlbumIds" parameterType="int[]">
    delete from betern_customer_album
    where id in
        <foreach collection="array" item="ids" index="no" open="(" separator="," close=")">
            #{ids}
        </foreach>
  </delete>

int deleteByContractId(List<BeternCustomerClause> list);
  <delete id="deleteByContractId" parameterType="java.util.List">
    delete from betern_customer_clause
    where contract_id in
        <foreach collection="list" item="list" index="no" open="(" separator="," close=")">
            #{list.contract_id}
        </foreach>
  </delete>

批量添加
递归查询
DELIMITER $$ -- 定好结束符为"$$"
DELIMITER ; -- 最后又定义为";"
告诉mysql解释器，该段命令是否已经结束了，默认情况下，delimiter是分号;，命令以分号结束，但有时候，不希望MySQL这么做。在为可能输入较多的语句，且语句中可以包含有分号
DECLARE sTempList VARCHAR(1000); --定义局部变量,作用在对应的begin和end之间
SET
Cast(字段名 as 转换的类型 )
CONCAT -- 连接字符串
WHILE sTempChd <> 0 DO -- while 条件 do，-- iterate 循环名，相当于continue;-- leave 循环名，相当于break;
GROUP_CONCAT
-- select id, group_concat(price) from goods group by id;
以id分组，把id相同的数据的价格在一行打印出来
INTO 变量名 -- 将查询得到的值赋给对应位置的变量
FIND_IN_SET(str,strlist) -- str 要查询的字符串，strlist 字段名参数以”,”分隔如 (1,2,6,8,10,22) ，返回值为str在集合的位置，没有返回0

DELIMITER $$
DROP FUNCTION IF EXISTS `getUnitChildList`$$
CREATE FUNCTION `getUnitChildList`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
 DECLARE sChildList VARCHAR(1000);
 DECLARE sChildTemp VARCHAR(1000);
 SET sChildTemp =CAST(rootId AS CHAR);
 WHILE sChildTemp <> 0 DO
	 IF (sChildList IS NOT NULL) THEN
			SET sChildList = CONCAT(sChildList,',',sChildTemp);
	 ELSE
			SET sChildList = CONCAT(sChildTemp);
	 END IF;
	 SELECT GROUP_CONCAT(id) INTO sChildTemp FROM betern_dynamic_comment WHERE FIND_IN_SET(parent_comment_id,sChildTemp)>0;
 END WHILE;
 RETURN sChildList;
END$$

DELIMITER ;


SELECT
*
FROM
betern_dynamic_comment
WHERE FIND_IN_SET(id, getUnitChildList(317));

select getUnitChildList(317);--所有id


--向上递归父节点
DELIMITER $$
DROP FUNCTION IF EXISTS `getUnitChildListUp`$$
CREATE FUNCTION `getUnitChildListUp`(rootId INT) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN
 DECLARE sTempList VARCHAR(1000) default rootId;
 DECLARE sTempChd VARCHAR(1000) default '';
	 WHILE rootId <> 0 DO
			SET sTempChd =(SELECT parent_comment_id FROM betern_dynamic_comment WHERE id = rootId); 
			
			IF (sTempList IS NOT NULL) THEN
				SET sTempList = CONCAT(sTempList,',',sTempChd);
				SET rootId = sTempChd;
				
			ELSE
				SET sTempList = sTempChd;
				SET rootId = sTempChd;
			END IF;
	 END WHILE;
 RETURN sTempList;
END$$
DELIMITER ;

select getUnitChildListUp('327');

SELECT
*
FROM
betern_dynamic_comment
WHERE FIND_IN_SET(id, getUnitChildListUp(327));

批量更新

//sql
update mydata_table 
set status = 
case
    when id = #{item.id} then #{item.status}//此处应该是<foreach>展开值
    ...
end
where id in (...);

//mybatis --- foreach 
<update id="updateBatch" parameterType="java.util.List">
    update table_name
    set  status=
    <foreach collection="list" item="item" index="index" 
        separator=" " open="case ID" close="end">
        when #{item.id} then #{item.status}
    </foreach>
    where id in
    <foreach collection="list" index="index" item="item" 
        separator="," open="(" close=")">
        #{item.id,jdbcType=BIGINT}
    </foreach>
</update>

//mybatis --- trim + foreach 
<update id="updateBatch" parameterType="java.util.List">
        update mydata_table
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="status =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                     when id=#{item.id} then #{item.status}
                </foreach>
            </trim>
        </trim>
        where id in
        <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
            #{item.id,jdbcType=BIGINT}
        </foreach>
</update>

//最终写法
<update id="updateBatch" parameterType="java.util.List">
    update mydata_table
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="status =case" suffix="end,">
             <foreach collection="list" item="item" index="index">
                 <if test="item.status !=null and item.status != -1">
                     when id=#{item.id} then #{item.status}
                 </if>
                 <if test="item.status == null or item.status == -1">
                     when id=#{item.id} then mydata_table.status//原数据
                 </if>
             </foreach>
        </trim>
    </trim>
    where id in
    <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
        #{item.id,jdbcType=BIGINT}
    </foreach>
</update>
缺点：易错-可以一次批量更新一部分（分页进行更新，比如说一共有1000条数据，一次更新100条）
      效率低-  数据太多不建议用
多字段排序
 select * from table order by id desc,name desc;

Mysql中批量替换某个字段的部分数据
// 图片中的逗号改为分号 UPDATE article SET img = (replace(img, ',', ';'))
批量为某一列的值添加后缀
// 批量为某一列的值添加后缀 UPDATE table SET column = CONCAT(column, "_");
递归详解-2021-07-28补充
函数定义
例如，传入邀请码，查询所有的下级邀请邀请码，首先，创建查询函数：getInviteList，传入参数为inviteCode，VARCHAR类型

DELIMITER $$ -- 改变sql命令结束符为"$$", 默认结束符为";"

DROP FUNCTION IF EXISTS `getInviteList`$$ -- 删除函数getInvite
CREATE FUNCTION `getInviteList`(inviteCode VARCHAR(56)) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN -- 脚本区域，可定义查询和变量

END$$

DELIMITER ; -- 结束符改回";"

变量和循环

DELIMITER $$ -- 改变sql命令结束符为"$$", 默认结束符为";"

DROP FUNCTION IF EXISTS `getInviteList`$$ -- 删除函数getInvite
CREATE FUNCTION `getInviteList`(inviteCode VARCHAR(56)) RETURNS VARCHAR(1000) CHARSET utf8
BEGIN -- 脚本区域，可定义查询和变量
	DECLARE inviteCodeList VARCHAR(1000); -- 定义局部变量 子inviteCode列表,作用在对应的begin和end之间
	DECLARE childTemp VARCHAR(1000); -- 定义局部变量 循环字段,作用在对应的begin和end之间
	SET childTemp = inviteCode; -- 赋值
	
	WHILE childTemp is not null DO -- 定义循环
		IF (inviteCodeList IS NOT NULL) THEN -- 如果子inviteCode列表不为空，拼接
			SET inviteCodeList = CONCAT(inviteCodeList,',',childTemp);
		ELSE -- 为空直接赋值
			SET inviteCodeList = CONCAT(childTemp);
		END IF;
		-- group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
		-- group_concat  可以对将要拼接的字段值去重，排序，指定分隔符。若没有指定，默认以逗号分隔
		-- INTO 变量名 -- 将查询得到的值赋给对应位置的变量
		-- FIND_IN_SET(str,strlist)  str 代表要查询的字符串字段名 ， strlist 是一个以逗号分隔的字符串
		SELECT GROUP_CONCAT(invite_code) INTO childTemp from ums_member where FIND_IN_SET(parent_user_id, childTemp);
	END WHILE; -- 循环结束
RETURN inviteCodeList;		
END$$

DELIMITER ; -- 结束符改回";"