mysql 子查询中regexp_substr函数得到的结果，在外层查询为空字符串

Question

请教一个问题，子查询中使用regexp_substr函数来截取字符串，直接执行没有问题，但是在外面套一个select，查询结果是空字符串，这是为什么？
mysql是docker版8.0.13
sql代码如下：

select str_1,str_2 from(
select regexp_substr('IJHY01000102,IJHY01000103,IJHY01000104', '[^,]+', 1, 1) as str_1, regexp_substr('II1,II2,II3', '[^,]+', 1, 1) as str_2 from dual) t

分别执行结果如下：

Answer 1

这个问题出现的原因是因为子查询中引用的是dual表，而在MySQL中，dual表是一个虚拟表，用来返回一个空集合。因此，在子查询中，regexp_substr函数的结果为空字符串，导致最终的查询结果也是空字符串。

解决这个问题的方法是，不要使用dual表作为子查询的来源，而直接使用一个真实的表，或者使用VALUES子句来在子查询中创建一个虚拟表。具体可以参考下面的两个示例：

使用真实的表：

CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  str1 VARCHAR(100),
  str2 VARCHAR(100),
  PRIMARY KEY (id)
);

INSERT INTO mytable (str1, str2) VALUES ('IJHY01000102,IJHY01000103,IJHY01000104', 'II1,II2,II3');

SELECT str_1, str_2
FROM (SELECT regexp_substr(str1, '[^,]+', 1, 1) as str_1,
             regexp_substr(str2, '[^,]+', 1, 1) as str_2
      FROM mytable) t;

使用VALUES子句创建虚拟表：

SELECT str_1, str_2
FROM (SELECT regexp_substr(val1, '[^,]+', 1, 1) as str_1,
             regexp_substr(val2, '[^,]+', 1, 1) as str_2
      FROM (VALUES ('IJHY01000102,IJHY01000103,IJHY01000104', 'II1,II2,II3')) t(val1, val2)) t2;