目录
场景说明
mysql解决
excel解决
vscode插件解决
notepad++解决
扩展解决
正则解决
自动录制宏解决
场景说明
平常在开发中有时会遇到一些字符串的规整或者格式化的操作,这点在操作数据库时经常常见,但是有的时候却有这种需求,就是我们的修改条件是某个查询条件的字查询,此时mysql的update的where的子查询的书写有时会比较麻烦 博主最近就遇到这种比较恶心的情况 特此记录下
举例说明;
此时遇到的场景就是,将这条查询语句查询出来的数据的某一列数据调整为其他的值,这里比如将clue_name的值全部改成 "暗武逢天",我们可以先调整这条sql只查询id主键列,然后使用update语句时直接where id in这种写法,看看会有什么问题
书写update语句
可以看到虽然修改成功了,但是子查询语句书写确实非常的繁琐,其实完全可以换个思路,直接将我们前面的查询语句查询出来的id列拼接出来成 where id in ('值1','值2','值3'....) 的这种形式,而括号里面就是单引号加逗号分割的形式进行的,这样的话update语句的where 条件就只会是一个in条件
mysql解决
其实mysql自身就有一个可以将多行进行拼接的方法可以使用
MySQL中，聚合函数GROUP_CONCAT()用指定分隔符连接字符串。
通常使用
多行变一列：【语法：group_concat(列名或与其他字符拼接 Separator ‘分隔符’)】
扩展一下：多行变多列（不使用该聚合函数）
举例使用:
这里在写的时候注意要使用 \ 对 单引号进行转义 可以看到拼接的结果,我这里直接将结果贴在下面
'4059','5146','5287','6135','6480','6983','7988','8264','8628','8919','8920','8965','9310','9326','10369','10426','10429','10434','10435','10438','10439','10463','10657','10844','11071','11084','11128','11171','11173','11227','11329','11424','11541','12047','12303','12415','12432','12507','12545','12598','12673','12693','12720','12849','13169','13186','13643','13790','13987','13992','14129','14155','14274','14515','14516','14561','14674','14697','14713','14955','15125','15134','15169','15348','15398','15448','15470','15490','15505','15605','15618','15634','15675','15696','15717','15755','15789','15822','15977','15986','16031','16134','16211','16237','16276','16277','16325','16328','16332','16389','16405','16520','16590','16807','16861','16930','16974','16986','16987','17077','17117','17125','17184','17299','17305','17335','17352','17388','17391','17449','17494','17518','17582','17655','17656','17741','17931','17990','17991','18012','18206','18296','18310','18363','18396','18397','18398','18399','18401','18403
可以看到,将之前id列进行了单引号加逗号的拼接 ,我们在使用的时候只需要注意结尾可能会多个逗号或者少个单引号,手动加下就可以了,然后可以直接将这些拼接好的字符串直接放在update语句的in语句里还是比较方便的
但是这个函数也是有一些问题的,就是mysql是有默认的长度限制的,可以很明显看到前面贫瘠的字符串长度根本达不到之前我写sql的数量
我这条sql查询出的id列是2190行,那么拼接的字符串也应该有2190左右个逗号或者2190*2左右个的单引号数量才对 ,我们这里只计数逗号的数量试试看
只有129个逗号,相当于只拼接了129左右的个id行 那是因为mysql默认的 group_concat函数长度限制是1024个字符
改变方法（两种）：
1、修改MySQL的配置文件：
group_concat_max_len = 10240000，需要重启数据库才能生效
2、也可以使用sql语句设置：
SET GLOBAL group_concat_max_len=10240000;
SET SESSION group_concat_max_len=10240000;
执行后对新建的连接生效，但如果数据库重启，参数会恢复1024（如果mysql配置文件没改）。所以在不方便重启数据库而使用这种方法的情况下，最好记得修改数据库配置文件，以免重启数据库设置好的参数失效。
可以看到这种方式虽然可以解决update的in 子查询语句的繁琐,但是在拼接的行数比较多时还需要修改聚合函数的一些配置 在行数比较少的情况下还是推荐使用的
excel解决
也可以使用excel来解决列的拼接问题,并且excel不用像mysql本身一样担心长度的限制
首先我们先将查询到的id列进行导出或者复制到一个excel中
可以看到长度也是比较多的
函数书写
="'"&A1&"',"
书写后直接回车
此时可以看到C1值已经成为我们想要的单引号加逗号分割的形式
而其他列就不需要再一个一个书写了,直接拖动C1单元格的右下角,将其拖动到该excel的最后一行需要拼接的位置即可 动图展示:
然后选中该列进行复制到文本中再次计算逗号数量
可以看到复制后还是比较规整的换行排列了,计数也是2190个逗号,符合预期,也没有长度限制,可以直接将这拼接好的放到update的in语句中,注意删掉最后的多余逗号
这种方法可以很方便地进行我们需要的拼接,但是也有一个问题就是我们需要手动拖动第一行写好的函数格式到最后一行,当拼接的行数上万或者几十万几百万时这也是比较可怕的一个方法,当然这种情况还是比较少见的
vscode插件解决
在vscode中也有一些非常方便的第三方插件可以帮助我们解决这些问题
这里介绍一个博主常用的插件
输入vscode快捷键ctrl+shit+p,召唤出命令面板
输入符号后回车
可以看到字符都以双引号包裹且自动逗号分割,最神奇的是最后一个还没有多余出来的逗号 ,太amazing了
再以单引号为例
非常nice,进行逗号计数
可以看到没了最后的逗号,非常nice 非常推荐使用该插件,当然该操作也可以配置相应的快捷键来进行快捷操作
notepad++解决
也可以直接使用常用的文本编辑器nodepad++解决
扩展解决
可以发现每行开头都加了 一个左边的单引号
那么现在还差的就是右边的单引号和逗号
直接使用ctrl+f的替换功能
转换后的效果
需要注意的是这里转换的最后和excel以及mysql不同,不是多了单引号或逗号,这里是少个单引号,也需要手动加下,而且也没有长度限制
正则解决
也可以使用notepad++的正则来解决
替换后效果
也是左边单引号有了,还差右边单引号和逗号
再次正则替换,这次替换每行结尾
替换后效果
可以看到正则也可以规整解决
自动录制宏解决
还可以使用notepad++的录制宏解决,但是这个录制宏可以和前面两个结合,简单来说就是将前面两个的操作录制下来成为一个模板,以后再用时可以直接套用
准备数据
点击录制宏
将光标放在第一行的首位字符前，Ctrl+F 组合键调出弹框，选择“替换”，查找模式选择“正则表达式 ”，输入如下图内容后，点击“全部替换”
此时不关闭弹框。将光标放在第一行的末位字符后，输入如下图内容后，点击“全部替换”
输入如下图内容后，点击“全部替换”按钮去掉字符串末尾的换行符（如果需要）
关闭弹框。按住Ctrl+end键，定位到末尾字符，删掉最后的逗号
点击宏，点击“停止录制按钮”。再点击“保存录制宏”按钮，之后就可以在这里点击保存过的宏来处理数据了。单引号是一样的操作方法，只需要修改标点符号。
直接完成,非常nice
