今天我们来讨论一下字符串聚合的反操作,也就是将单个字符串拆分成多行字符串。本文涉及的数据库包括 Oracle、MySQL、SQL Server、PostgreSQL 以及 SQLite。
表值函数
表值函数(Table-Valued Function)是指返回结果是一个表或者集合的函数,也称为行集函数(Set Returning Function)。表值函数可以当作一个数据表在查询中使用,类似于子查询或者视图。
在文章中我们会使用到以下示例表:
Oracle
Oracle 没有提供拆分字符串的表值函数,我们可以创建一个自定义的 PL/SQL 函数来实现这个功能。首先,创建一个集合类型:
str_list 可以看做一个由字符串数据组成的数组或者列表。然后创建一个拆分字符串的函数:
string_split 函数可以将输入的字符串以指定分隔符进行拆分,默认分隔符为逗号。例如:
我们也可以将该函数应用到查询中的字段,例如:
查询通过交叉连接将 class 字段中的数据进行了展开。
想一想,怎么查找剧情类的电影?
MySQL
MySQL 没有提供拆分字符串的表值函数,也不支持自定义函数来实现这个功能。不过,我们可以利用递归通用表表达式来实现字符串的拆分:
WITH RECURSIVE 表示递归通用表表达式,每次递归都返回一个拆分后的子串。将上面的查询应用到 movies 表中可以将电影的类型进行展开:
其他数据库也都实现了通用表表达式,因此也可以使用这种方法进行字符串的拆分。
SQL Server
SQL Server 2016 引入了一个字符串表值函数 STRING_SPLIT,它可以根据指定的分隔符将字符串拆分为子字符串行。例如:
STRING_SPLIT 函数第一个参数是被拆分的字符串,第二个参数是拆分使用的分隔符。函数返回一个单字段的表,字段名为“value” 。如果任何输入参数为 nvarchar 或 nchar 类型,则返回 nvarchar 类型;否则,返回 varchar 类型。返回类型的长度与字符串参数的长度相同。
以下查询使用 CROSS APPLY 将 class 字段进行了展开:
SQL Server 不能像 Oracle 那样直接使用连接查询。
如果想要查找剧情类的电影,可以在子查询中使用 string_split 函数:
PostgreSQL
首先,PostgreSQL 中所有的函数实际上都可以作为表值函数使用。例如:
我们知道,FROM 子句后面就是表,因此 ABS 函数的返回结果可以看做一个一行一列的表。
PostgreSQL 提供了一个拆分字符串的函数 regexp_split_to_table ,可以通过一个 POSIX 正则表达式指定分隔符。例如:
以下查询使用 CROSS JOIN 将 class 字段进行了展开:
想一想,怎么查找剧情类的电影?
SQLite
SQLite 没有提供拆分字符串的表值函数,也不支持自定义函数来实现这个功能。不过,我们可以像 MySQL 一样利用递归通用表表达式来实现字符串的拆分:
WITH RECURSIVE 表示递归通用表表达式,每次递归都返回一个拆分后的子串。将上面的查询应用到 movies 表中可以将电影的类型进行展开: