字符串拆分，一行变多行

【问题】

I’d like to extract certain snippets from a MySQL column which are bounded in some unique way. Here is an example:

I have the following table (TableA):

id | column_a
—+———
1 | There is a user [u?u=990] and another [u?u=5458855].
2 | And here is just one user [u?u=9390].
3 | And here is nothing.

And I want to get this:

id | result_a
—+———
1 | 990
1 | 5458855
2 | 9390

At the moment I have this query:

SELECT id,SUBSTRING_INDEX(SUBSTRING_INDEX(column_a, ‘[u?u=’, -1), ‘]’, 1) AS result_a FROM TableA

But that gives me:

id | result_a
—+———
1 | 5458855
2 | 9390

别人给出答案，但不一定正确：

SELECT id, SUBSTRING_INDEX(
SUBSTRING_INDEX(column_a, ‘[u?u=’, -1 - n.v)
, ‘]’, 1) AS result_a
FROM TableA JOIN (
SELECT b6.v | b5.v | b4.v | b3.v | b2.v | b1.v | b0.v AS v
FROM (
SELECT 0 v UNION ALL SELECT 1<<0
) b0 JOIN (
SELECT 0 v UNION ALL SELECT 1<<1
) b1 JOIN (
SELECT 0 v UNION ALL SELECT 1<<2
) b2 JOIN (
SELECT 0 v UNION ALL SELECT 1<<3
) b3 JOIN (
SELECT 0 v UNION ALL SELECT 1<<4
) b4 JOIN (
SELECT 0 v UNION ALL SELECT 1<<5
) b5 JOIN (
SELECT 0 v UNION ALL SELECT 1<<6
) b6
) n
WHERE RIGHT(column_a, 5+CHAR_LENGTH(
SUBSTRING_INDEX(column_a, ‘[u?u=’, -1 - n.v)
))REGEXP ‘^\\[u\?u=.*\\]’

【回答】
既要处理字符串拆分，又要将一条变多条记录，在 SQL 中计算很麻烦，这种情况用 SPL 就很简单，脚本如下：

	A
1	$select id,column_a from TableA
2	=A1.run(column_a=column_a.split@b("u?u=").to(2,).(parse@n (~)))
3	=A2.news(column_a; A2.id:id, ~:result_a)

A1：读取 TableA 表

A2：从 column_a 列中拆出子串集合

A3：生成新序表

例子程序：

文件解析后逆分组扩展 
oracle 如何把单行记录拆分为多行  
一条记录到多条记录的转置怎么写  
将数据库中的一列字符拆分成多行 
逆分组计算  
拆字符串成多记录  
字符串拆分一行变多行