达梦数据库(oracle)使用 REGEXP_SUBSTR函数实现根据逗号将一行数据拆分成多行数据

喜欢ヅ旅行 2023-10-01 19:42 20阅读 0赞

首先简单介绍下 REGEXP\_SUBSTR函数,函数格式如下：  
function REGEXP\_SUBSTR(String, pattern, position, occurrence, modifier)  
\_\_srcstr ：需要进行正则处理的字符串

\_\_pattern ：进行匹配的正则表达式

\_\_position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

\_\_occurrence ：标识第几个匹配组，默认为1

\_\_modifier ：模式（'i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’。可以省略）  
另一般REGEXP\_SUBSTR函数都会与CONNECT BY配合使用

## 业务场景 ##

现有新闻图片库NEWS\_TYPE表,字段如下  
![在这里插入图片描述][e94d77ea08484540855d7f56a9e2797f.png]  
如图所示,每一条数据都对应着一个或多个ATTACH\_ID,这样如果我们想要与ATTACH表进行联合查询就会很麻烦,使用REGEXP\_SUBSTR能够解决这个问题.

SELECT DISTINCT 
    ID, REGEXP_SUBSTR(ATTACH_ID, '[^,]+', 1, LEVEL) AS FILE_ONE
    FROM NEWS_TYPE
    CONNECT BY LEVEL <= LENGTH(ATTACH_ID) - LENGTH(regexp_replace(ATTACH_ID, ',', '')) + 1 
    ORDER BY ID

查询结果如下:  
![在这里插入图片描述][f765b7afbeb440d69fd35313557add1b.png]

--------------------

好了下面问题来了,如果数据量小的话,这样来操作是没问题的,一旦数据量上来了,并且再加上多表联查,查询的速度就会非常慢(非常非常慢!),所以就有了下面的内容,优化.  
经过多方查询资料,新的sql查询速度确实快到飞起,但实现原理一直都不清楚,等我查到以后再更新,先上新sql

SELECT ID, 
    REGEXP_SUBSTR(ATTACH_ID, '[^,]+', 1, l) AS FILE_ONE
    FROM NEWS_TYPE,(SELECT LEVEL l FROM DUAL CONNECT BY LEVEL<=100) b
    WHERE l <= LENGTH(ATTACH_ID) - LENGTH(regexp_replace(ATTACH_ID, ',', '')) + 1

[e94d77ea08484540855d7f56a9e2797f.png]: https://img-blog.csdnimg.cn/e94d77ea08484540855d7f56a9e2797f.png
[f765b7afbeb440d69fd35313557add1b.png]: https://img-blog.csdnimg.cn/f765b7afbeb440d69fd35313557add1b.png