问题记录:
有用户表的邮箱字段,以逗分号为分隔符邮箱存放了多个邮箱值
如:
select 'zhangsan@outlook.com;zhangsan@qq.com' as email_value from dual;
我们多数时候需要根据逗号把这个值拆成只存放一个邮箱号的值,以用来关联用户表或者其他业务表。
这个时候我们就可以用以下sql来实现:
SELECT SUBSTRING_INDEX( SUBSTRING_INDEX( a.`name`, ';', b.help_topic_id + 1 ), ';',-1 ) as REGEXP_COUNT_COL
FROM
(SELECT 'zhangsan@outlook.com;zhangsan@qq.com' AS NAME FROM DUAL) a
INNER JOIN mysql.help_topic b ON b.help_topic_id < (LENGTH(a.`name`) - LENGTH(REPLACE(a.`name`, ';', '')) + 1)
效果:
说明 :
1、mysql.help_topic表的help_topic_id 字段是从0开始的自增的int类型的值,所以当部分用户访问该表被拒绝的时候,可以自己建一张临时辅助表,id的值建议至少从0到100,保存100列(取决于参数中可能出现多少个分隔符);
2、如果是别的分隔符,把sql中的’;'替换成其他就好
3、sql原理:大概就是利用参数中分隔符出现的次数来重复连接,以多次返回值,每次返回值都利用SUBSTRING_INDEX截取不同位置的值,达到拆分到多行的目的。
把 ‘zhangsan@outlook.com;zhangsan@qq.com’ 当成一个参数,
封装成一个自定义函数function,实现长治久安。