mysql - 从以某些字符为界的 MySQL 列中提取多个文本实例

Question

我想从 MySQL 列中提取某些片段，这些片段以某种独特的方式界定。这是一个例子:

我有下表 (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.

我想得到这个:

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

目前我有这个问题:

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

但这给了我:

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

我怎样才能改进这个查询？

谢谢!

Answer 1

出现此问题是因为您的架构违反了 1NF 。

规范化模式将包含另一个表(看起来很像您要获得的结果，可能带有一个额外的“位置”列，指示从原始源中的位置预示的值)，以及 column_a 中的值在您现有的表格中将不会包含您之后的数字，而是某种占位符。

当然，这就是(从某种意义上说)您要构建的……

SQL 确实不太适合从单个源行中提取多个结果:在 MySQL 中出现的唯一方法是通过 JOIN 。例如，可以尝试从每一行中获取两个实例——

SELECT id, SUBSTRING_INDEX(
             SUBSTRING_INDEX(column_a, '[u?u=', -1 - n.v)
           , ']', 1) AS result_a
FROM   TableA JOIN (
         SELECT 0 v
       UNION ALL
         SELECT 1
       ) n

当然，这种方法有一些问题:

1. 它为 every 行返回两条记录，这是不正确的——这可以通过仅过滤那些找到的文本与所需模式匹配的记录来解决:

   WHERE RIGHT(column_a, 5+CHAR_LENGTH(
           SUBSTRING_INDEX(column_a, '[u?u=', -1 - n.v)
         )) REGEXP '^\\[u\\?u=.*\\]'
   

2. 它要求连接表一直计数到预期实例的最大数量，你说的可能多达 100 个——而不是显式地这样做，这对于这样的一个对象来说显然很麻烦大数，一个人可以连接多个表来实现乘法而不是 e.g.使用二进制(至 128，但如果需要，表 WHERE 中的 n 过滤器可以限制为一些较小的数字):

   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=.*\\]'
   

3. MySQL 的字符串函数不是特别高效，这种方法会很慢。