MySQL骚操作：语句直接替换，都不用写脚本

用户10002156

发布于 2023-09-12 13:55:24

4070

文章被收录于专栏：生活处处有BUG生活处处有BUG

背景

这是一个比较少见的需求，批量删除数据表中email_list的一个邮箱。本来以为是需要用程序写个脚本，后面发现，直接用sql就可以了。

Demo

建表

CREATE TABLE mail_list (
  code varchar(255),
  email TEXT
);

插入演示数据

INSERT INTO mail_list (code, email) VALUES 
('1100', 'kklin@qq.com,qjia@qq.com,hchong@qq.com,srh@qq.com'),
('4100', 'jgli@qq.com,kklin@qq.com,ylzhang@qq.com,ehyang@qq.com'),
('4301', '32173899@qq.com,kklin@qq.com,hurong9210@163.com,fychen@qq.com,luntang@qq.com,ql@qq.com,ymtu@qq.com'),
('4400', 'fychen@qq.com,kklin@qq.com,luntang@qq.com,ql@qq.com'),
('4401', 'cwang@qq.com'),
('4402', 'ltdu@qq.com,fychen@qq.com,kklin@qq.com,luntang@qq.com,ql@qq.com'),
('4403', 'gzks123@126.com,kklin@qq.com,fychen@qq.com,luntang@qq.com,ql@qq.com,jzhou@qq.com'),
('6101', 'jiaxy@deepoo.com.cn,fut@deepoo.com.cn,kklin@qq.com,jgli@qq.com'),
('4407', 'jiaxy@deepoo.com.cn,fut@deepoo.com.cn,kklin@qq.com,fychen@qq.com,luntang@qq.com,ql@qq.com'),
('5001', '515778049@qq.com,qhuang@qq.com,kklin@qq.com'),
('5101', '48141695@qq.com,kklin@qq.com'),
('3100', 'kklin@qq.com,lfjin@qq.com,yqzhang@qq.com'),
('3101', 'wjzhang@qq.com,kklin@qq.com,jjshen@qq.com,lfjin@qq.com'),
('3200', '7650829@qq.com,kklin@qq.com,3559558253@qq.com'),
('3701', 'Zhanenhu@163.com,kklin@qq.com'),
('3102', 'shuzongrong123@163.com,kklin@qq.com,jjshen@qq.com,lfjin@qq.com,jjshen@qq.com');

替换

SET SQL_SAFE_UPDATES = 0;
UPDATE mail_list
SET email = REPLACE(email, 'kklin@qq.com,', '')
WHERE 
    (email LIKE '%kklin@qq.com,%')
    AND code NOT IN ('4100', '广州');

SET SQL_SAFE_UPDATES = 0;
UPDATE mail_list
SET email = REPLACE(email, ',kklin@qq.com', '')
WHERE 
    (email LIKE '%,kklin@qq.com')
    AND code NOT IN ('4100', '广州');

验证

select * from mail_list ;

提交生成

没有排除条件的

SET SQL_SAFE_UPDATES = 0;
UPDATE mail_list
SET mail = REPLACE(mail, 'hchong@visionmedicals.com', '')
WHERE mail LIKE '%hchong@visionmedicals.com%';

有排除条件的

SET SQL_SAFE_UPDATES = 0;
UPDATE mail_list
SET mail = REPLACE(mail, 'jli@visionmedicals.com', '')
WHERE 
    mail LIKE '%jli@visionmedicals.com%'
    AND code NOT IN ("4301","3102");

最后清除连续的两个逗号

SET SQL_SAFE_UPDATES = 0;
UPDATE mail_list SET mail = TRIM(BOTH ',' FROM mail);
UPDATE mail_list SET mail = REPLACE(mail, ',,', ',') WHERE mail LIKE '%,,%';

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-09-11 23:28，如有侵权请联系 cloudcommunity@tencent.com 删除

com