2017-04-06

MySQL字符串处理一则

摘要：记一次字符串字段数据更新的过程

需求描述

开发提出需修改某业务表中的一个字段的内容，将其中一部分修改掉。
修改前：

[{"extension":"jpg","fileKey":"B39C644EA275D5DF4062F7DCD5442077","smallUrl":"http://dl.test.com/images/320x320_B39C644EA275D5DF4062F7DCD5442077.jpg","bigImgUrl":"http://abc.test.com/r/010005002001001/010005002001001004001/B39C644EA275D5DF4062F7DCD5442077.jpg?t=20160310","type":6,"fileName":"段1.1"}]

期望能修改为：

[{"extension":"jpg","fileKey":"B39C644EA275D5DF4062F7DCD5442077","smallUrl":"http://abc.test.com/r/010005002001001/010005002001001004001/98x98_B39C644EA275D5DF4062F7DCD5442077.jpg?t=20160310","bigImgUrl":"http://abc.test.com/r/010005002001001/010005002001001004001/B39C644EA275D5DF4062F7DCD5442077.jpg?t=20160310","type":6,"fileName":"段1.1"}]

确定思路

1、确定需要修改的具体内容，即”smallUrl”:后面的内容
2、写出要修改后的新内容，即bigImgUrl+分辨率
3、将需要修改的内容replace为新内容
经过讨论业务逻辑后，最终确定通过这三步即可完成。
此处有感：磨刀不误砍材工，动手之前一定要先梳理好业务逻辑。否则可能动手做了一半才发现不对，白白浪费了时间。

编写SQL

需要用到Mysql的几个函数：SUBSTRING_INDEX、CONCAT、REPLACE
SUBSTRING_INDEX(str,delim,count)：用来返回字符串str中在第count个出现的分隔符delim之前或之后的字符串。如果count是一个正数，返回从最后的(从左边开始计数)分隔符到左边所有字符。如果count是负数，返回从最后的(从右边开始计数)分隔符到右边所有字符。
CONCAT()：用来拼接字符串
REPLACE()：用来替换字符串
1、找出需要被修改的部分：

CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(t_content1,'"smallUrl":',-1),'"',2),'"') as "old smallUrl",
先截取smallUrl右边的部分，在此基础上再进一步截取这部分中第二个"左边的部分，输出结果为：
"http://dl.test.com/images/320x320_B39C644EA275D5DF4062F7DCD5442077.jpg"

2、写出修改后的部分：

CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(t_content1,'"bigImgUrl":',-1),'/',6),'/98x98_',SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(t_content1,'"bigImgUrl":',-1),'"',2),'/',-1),'"') as "new smallUrl"
输出结果为：
"http://abc.test.com/r/010005002001001/010005002001001004001/98x98_B39C644EA275D5DF4062F7DCD5442077.jpg?t=20160310"

3、验证是否正确

SELECT t_content1,
REPLACE(t_content1,CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(t_content1,'"smallUrl":',-1),'"',2),'"'),CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(t_content1,'"bigImgUrl":',-1),'/',6),'/98x98_',SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(t_content1,'"bigImgUrl":',-1),'"',2),'/',-1),'"')) as new_t_content1
FROM test_book_class WHERE ccdl_type=5 
AND t_content1 LIKE '%"bigImgUrl":"http://abc.test.com%' 
AND t_content1 LIKE '%"smallUrl":"http://dl.test.com/%' 
LIMIT 10;

比较修改前的内容和修改后的内容，确定被修改的部分是正确的。

执行SQL

注意：先测试库，后生产库。

UPDATE test_book_class SET t_content1=REPLACE(t_content1,CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(t_content1,'"smallUrl":',-1),'"',2),'"'),CONCAT(SUBSTRING_INDEX(SUBSTRING_INDEX(t_content1,'"bigImgUrl":',-1),'/',6),'/98x98_',SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING_INDEX(t_content1,'"bigImgUrl":',-1),'"',2),'/',-1),'"'))
WHERE ccdl_type=5 AND t_content1 LIKE '%"bigImgUrl":"http://abc.test.com%' AND t_content1 LIKE '%"smallUrl":"http://dl.test.com/%';
COMMIT;

本文标题:MySQL字符串处理一则

文章作者:一条大河

发布时间:2017-04-06, 16:20:51

最后更新:2017-04-06, 17:16:24

原始链接:http://riverdba.github.io/2017/04/06/String-handling-of-mysql/

许可协议: "署名-非商用-相同方式共享 4.0" 转载请保留原文链接及作者。