MariaDB与ORACLE数据库的字符串分割

最近关于ORACLE向MariaDB迁移涉及到区别比较多～～～

还是直接上栗子吧。

还是以前的student表：

假如从前台传入一个由sno组成的字符串，比如“1,11,15,16”。

要根据该字符串查出这几条数据。

在ORACLE中可以这样写：

WITH SYSNO AS (SELECT ? SNO FROM DUAL),
SYSNO_ARRAY AS (
    SELECT REGEXP_SUBSTR(SNO,'[^,]+',1,LEVEL) SNO FROM SYSNO CONNECT BY LEVEL<=LENGTH(SNO)-LENGTH(REPLACE(SNO,',',''))+1
)
SELECT * FROM STUDENT T WHERE T.SNO IN (SELECT SNO FROM SYSNO_ARRAY) 

大概就是是这样一个情景。
放到ORACLE中执行一下：

效果似乎不错。

但在MariaDB中要怎么操作呢？
事实上，在10.0.38版本是不支持with as 语法的，但是也还好，with as 实际上就是个子查询，放到查询里面就是了。

• ORACLE     REGEXP_SUBSTR()

但REGEXP_SUBSTR()怎么解决呢？
先来看下ORACLE中的REGEXP_SUBSTR()函数。

根据文档说明，REGEXP_SUBSTR的语法是这样婶的：

REGEXP_SUBSTR(source_char, pattern [, position [, occurrence [, match_parameter ]]])

各参数的意义分别是：

现在，参照官方文档我们看一下上述ORACLE里的sql，我们传入一个值为‘1,11,15,16’的source_string,从第一个字符开始根据正则表达式'[^,]+'按逗号‘,’分割它，并取其第...LEVEL 个匹配项？？？

这个LEVEL 又是个什么东西啊？

LEVEL是ORACLE的关键字，主要用来代表树形结构中的层级编号，它与后面的CONNECT BY组合可以存在树形结构的数据，关于这部分的知识比较复杂，我也不是很明白，以后遇到可以单独再出一片学习文档。在这里，我们可以看一个简单的例子就能明白这样写的作用：

是不是清晰了很多？其实就是生成了一个序列。那看回上述例子，我们就明白了它取了SYSNO_ARRAY的下标，嗯，大概就是这个意思，然后交给REGEXP_SUBSTR（）这样就可以取出每一个匹配的字符串了。

• MariaDB    REGEXP_SUBSTR()

MariaDB也有个类似的REGEXP_SUBSTR()内建函数，但根据MariaDB的官方文档：

该函数不能做到像ORACLE里的REGEXP_SUBSTR()那样可以选择起始位置或选择哪一个匹配的结果,功能比较单一.

• MariaDB    SUBSTRING_INDEX（）

好在MariaDB提供了另外一个内建函数SUBSTRING_INDEX().

可以实现类似的根据字符分割字符串并取其中某一匹配项。
另外，在实现过程中还使用到了自带的mysql数据库中的
help_topic表，它的help_topic_id字段是自增的，当然你也可以使用其他具有自增字段的表。

直接看最终的SQL:

SELECT * FROM student WHERE SNO IN (
SELECT
    SUBSTRING_INDEX(SUBSTRING_INDEX(SNO,',',help_topic_id+1),',',-1) AS SNO
FROM
    mysql.help_topic,(SELECT '1,11,15,16' SNO)SYSNO_ARRAY
WHERE
    help_topic_id < LENGTH(SNO)-LENGTH(REPLACE(SNO,',',''))+1);

分析一下这段sql：

1. 利用help_topic_id < LENGTH(SNO)-LENGTH(REPLACE(SNO,',',''))+1)可以得到最终可以被拆分为多少个字符串，有三个逗号，那结果就是四个字符串。之后就可以利用help_topic_id来遍历第n个匹配的字符串了。
2. SUBSTRING_INDEX(SUBSTRING_INDEX(SNO,',',help_topic_id+1),',',-1) AS SNO。利用内层的SUBSTRING_INDEX() 可以取出第help_topic_id+1（help_topic_id是从0开始）个逗号之前的字符串，也就是说，
help_topic_id = 0，help_topic_id+1=1，SUBSTRING_INDEX(SNO,',',help_topic_id+1) = 1
help_topic_id = 1，help_topic_id+1=2，SUBSTRING_INDEX(SNO,',',help_topic_id+1) = 1,11


之后再利用外层的SUBSTRING_INDEX()取根据逗号分割的-1个匹配项，就可以将所有的SNO取出，通过SNO 在student表中取数据了。

至于(SELECT '1,11,15,16' SNO)SYSNO_ARRAY 就是前面提到的WITH AS 在MariaDB中的写法，而且MariaDB还要求所有的子查询必须要有一个别名，真是奇怪的设计。

最后放到MariaDB中运行一下：

可以，没问题！