Mysql中如何提取字段中逗号前面的内容 并更新到新的字段中

问题场景

很多时候，我们的数据库中存在一个字段里有很多个内容，例如有下面的表格fancypig

id	name	hobby
1	猪头	逆向,游泳
2	猪猪侠	渗透测试,击剑
3	猪八戒	代码审计,踢足球
4	猪悟能	网络安全,打游戏,陪唐僧取经

fancypig表

那么我们如何提取hobby中第一个逗号前的内容呢，并将其改成下面的表格呢

id	name	sec
1	猪头	逆向
2	猪猪侠	渗透测试
3	猪八戒	代码审计
4	猪悟能	网络安全

fancypig表[最终结果]

解决方案

mysql提取某个字段逗号前内容

我们可以通过两种方式来查询某个字段中，第一个逗号前的内容

• hobby是字段
• fancypig是表名

select substring_index(hobby, ',', 1) from fancypig
select substring_index(hobby, ',', 1) from fancypig
select substring_index(hobby, ',', 1) from fancypig

当然也可以使用

• hobby是字段
• fancypig是表名

select left(hobby, LOCATE(',',hobby)-1) from fancypig
select left(hobby, LOCATE(',',hobby)-1) from fancypig
select left(hobby, LOCATE(',',hobby)-1) from fancypig

上面只完成了查询的功能，如果我们想进行表的更新，请继续看

将提取后的内容更新到新的字段中

由于mysql不支持同时查询、更新一张表（oracle可以支持，手动滑稽）

那我们就要把上面的表格复制1份，做成这样

id	name	hobby	sec
1	猪头	逆向,游泳
2	猪猪侠	渗透测试,击剑
3	猪八戒	代码审计,踢足球
4	猪悟能	网络安全,打游戏,配唐僧取经

fancypig表(p)

id	name	hobby
1	猪头	逆向,游泳
2	猪猪侠	渗透测试,击剑
3	猪八戒	代码审计,踢足球
4	猪悟能	网络安全,打游戏,配唐僧取经

fancypig_copy表(p1)

然后执行下面的命令进行更新

update fancypig p  set p.sec = (SELECT substring_index(p1.hobby, ',', 1) from fancypig_copy p1  where p1.id = p.id);
update fancypig p  set p.sec = (SELECT substring_index(p1.hobby, ',', 1) from fancypig_copy p1  where p1.id = p.id);
update fancypig p  set p.sec = (SELECT substring_index(p1.hobby, ',', 1) from fancypig_copy p1  where p1.id = p.id);

然后就会得到下面的表

id	name	hobby	sec
1	猪头	逆向,游泳	逆向
2	猪猪侠	渗透测试,击剑	渗透测试
3	猪八戒	代码审计,踢足球	代码审计
4	猪悟能	网络安全,打游戏,配唐僧取经	网络安全

fancypig表(p)

你只需把hobby这个字段删掉就可以得到最终结果

id	name	sec
1	猪头	逆向
2	猪猪侠	渗透测试
3	猪八戒	代码审计
4	猪悟能	网络安全

fancypig表(最终结果)

当然，我们本片给大家提供的思路，不仅仅是处理字段中含义,逗号的情况，你还可以操作:冒号，或者其他任何可以作为参考的字符！而且也不仅仅只能处理第一个，处理第二个，第三个都可以！希望思路对大家有帮助！

上面只是举的一个简单例子，通过上面的例子可以更方便理解，我们可以通过上面的例子处理上万的数据，譬如我最近在修改的cve漏洞相关的案例，直接更新了6W多条数据