mysql - 正则表达式模式来匹配不同类型的数据

Question

我有一个数据库(mysql)文本字段，包含不同的数据:

• data1 - 单个数字，以逗号分隔:1,2,3,4,5,6,7,8,9,10,11,12,13,14,15...n(其中n可以是任意正整数)

• data2 - 文本，以逗号分隔:Lorem ipsum、dolor、sit amet、consectetur、adipiscing、elit、Ut et、sollicitudin、enim、vel、consectetur lacus

数据中不允许使用开始和结束逗号，空格可以出现在逗号之前和/或开始或逗号之后，例如2,3, 4,5 , 6,7 或 Lorem ipsum、dolor、sit amet 等，这些空格现在可以计入比赛中。

我需要在这些数据中以几种不同的方式进行搜索，因此我需要使用正则表达式模式进行搜索:

• 模式 A:匹配 data1 中的数字。如果我搜索 1，结果我只需要 1，而不是 11、12 等，

现在我有:[0-9]+(,[0-9]+)*

• 模式 B:匹配一系列数字:如果字段包含(至少一个)两个给定数字之间的数字。
• 模式 C:匹配 data2 中由逗号分隔的完整字符串之一。就像:如果我搜索sit，我没有得到结果，但如果我搜索sit amet，我得到结果了。

有人可以帮我解决这些问题吗？我为模式 A 提供的方法是最好的方法吗？如何匹配范围(模式 B)和逗号分隔字符串(模式 C)？

Answer 1

您不需要正则表达式。
我将向您展示一个技巧，将 MySQL 数字生成器与嵌套 SUBSTRING_INDEX 函数结合使用。
如果您需要支持更多 CSV，请添加新的

   CROSS JOIN (
      SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
    ) AS record_[number]

查询

此查询将生成 1 到 100 之间的数字。因此最终查询最多可以支持 100 个分隔值。

SELECT 
 (@number  := @number + 1) AS number
FROM (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS record_1
CROSS JOIN (
  SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) AS record_2
CROSS JOIN ( SELECT @number := 0 ) AS init_user_param

查看演示 http://sqlfiddle.com/#!9/c314ca/5

现在我们将从逗号分隔的字符串中提取值

查询

将 [position] 替换为 0 - ... 之间的数字，您要从逗号分隔的字符串中提取哪个值。

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',', [position]), ',', -1) AS split;

或

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('Lorem ipsum, dolor, sit amet, consectetur, adipiscing,elit, Ut et, sollicitudin, enim, vel, consectetur lacus ', ',', [position]), ',', -1) AS split;

查看演示 http://sqlfiddle.com/#!9/c314ca/16

现在我们知道了基础知识，我们将把这两个查询组合成一个可行的解决方案。
此查询会将所有逗号分隔值转换为记录。
因为我不知道表结构，我假设它类似于

CREATE TABLE [name] (
    data1 TEXT
  , data2 TEXT
); 

查询

SELECT
  DISTINCT 
      SUBSTRING_INDEX(SUBSTRING_INDEX(data1, ',', generator.number), ',', -1) AS split
FROM (

  SELECT 
   (@number  := @number + 1) AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) AS record_1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) AS record_2
  CROSS JOIN ( SELECT @number := 0 ) AS init_user_param
) 
 AS generator
CROSS JOIN
 Table1

UNION ALL 

SELECT
  DISTINCT 
      SUBSTRING_INDEX(SUBSTRING_INDEX(data2, ',', generator.number), ',', -1) AS split
FROM (

  SELECT 
   (@number2  := @number2 + 1) AS number
  FROM (
    SELECT 0 UNION SELECT 1 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) AS record_1
  CROSS JOIN (
    SELECT 0 UNION SELECT 1 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
  ) AS record_2
  CROSS JOIN ( SELECT @number2 := 0 ) AS init_user_param
) 
 AS generator
CROSS JOIN
 Table1

查看演示 http://sqlfiddle.com/#!9/a19fc5/6

我们将其分解为小部分。

pattern A: to match a number in data1. if I search for 1, as a result, I just need 1, not 11, 12, etc.,

因为我们现在有记录而不是逗号分隔值，所以我们可以简单地使用最后一个查询作为交付的表添加 WHERE 子句。

查看演示 http://sqlfiddle.com/#!9/a19fc5/10

pattern B: to match a range of numbers: if the field contains (at least one of) the numbers between two given numbers.

现在 WHERE 子句是一个简单的 BETWEEN 子句

查看演示 http://sqlfiddle.com/#!9/a19fc5/11

pattern C: to match one of the full strings separated by the comas in the data2. like: if I search for sit, I got no result, but if I seach for sit amet, I got result.

现在您可以使用 LIKE

查看演示 http://sqlfiddle.com/#!9/a19fc5/14