mysql - 使用 REGEX 而不是使用 BETWEEN 运算符的 SQL 搜索

Question

我有 MySQL 数据库，以及带有广告的内表。在该数据库表的一个字段中，数据以 json 格式保存。在那个 json 格式的数据中，我有一个键，它的值包含价格(带小数值)。

该字段(例如命名为 ad_data)保存在数据库字段中，包含如下 (json) 数据:

{"single_input_51":"Ad 44 test.","price":"20.00","single_input_4":"ad test title, ad tes title, .","single_input_11":"8.8.2015.","single_input_5":"video test","single_input_6":"https://www.youtube.com/watch?v=nlTPeCs2puw"}

我想在该字段中进行搜索，以便找到搜索到的价格范围。例如，如果用户以 html 形式设置他想在 100.00 到 755.00 的范围内搜索，SQL 应该只返回该字段(数据保存为 json)包含从 100.00 到 755.00 的那些值的行。

所以基本上，我想用 SQL 中的 REGEX 为该字段的 json 格式内容编写类似的东西(这里的数字只是示例，我必须能够为每个开始和结束的十进制数和数字我将以编程方式通过):

SELECT id, price FROM ads WHERE price BETWEEN 100.00 AND 755.00

通过 REGEX 搜索的 SQL 命令是什么？

Answer 1

不要使用 REGEX 进行匹配，那样会很痛苦。如果你有一个特定的价格范围，你正在寻找，它可能是可行的，但是当价格可以是两个、三个或更多字符时，动态生成正则表达式以“工作”任何指定的价格范围，这就是很难。 (MySQL 中的 REGEXP 函数只返回一个 bool 值，指示是否找到匹配；它不会返回匹配的字符串部分。)

如果我必须对“价格”进行比较，我会从字符串中解析价格的值，然后将其转换为数值，然后对其进行比较。

例如:

SELECT t.col
  FROM mytable t 
 WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(t.col,'"price":"',-1),'"',1) + 0
       BETWEEN 100.00 AND 755.00

---

要回答您提出的问题:您将使用什么表达式来使用 REGEX 执行此匹配...

对于“价格在 100.00 和 755.00 之间”，使用 MySQL REGEXP，您需要的正则表达式类似于此查询的 SELECT 列表中的第二个表达式:

SELECT t.col
     , t.col REGEXP '"price":"([1-6][0-9][0-9]\.[0-9][0-9]|7[0-4][0-9]\.[0-9][0-9]|75[0-4]\.[0-9][0-9]|755\.00)"' AS _match
  FROM ( SELECT 'no' AS col
         UNION ALL SELECT 'no "price":"14.00"def'
         UNION ALL SELECT 'ok "price":"99.99" def'
         UNION ALL SELECT 'ok "price":"100.00" def'
         UNION ALL SELECT 'ok "price":"699.99" def'
         UNION ALL SELECT 'ok "price":"703.33" def'
         UNION ALL SELECT 'ok "price":"743.15" def'
         UNION ALL SELECT 'ok "price":"754.99" def'
         UNION ALL SELECT 'no "price":"755.01" def'
       ) t

此示例中的正则表达式几乎是一个微不足道的示例，因为我们要匹配的价格值在小数点前都有三位数字。

用于正则表达式的字符串需要针对每个可能的值范围进行制作。制作过程需要考虑小数点前不同位数的价格，并分别处理每个价格。

要检查 95.55 到 1044.44 之间的价格范围，需要将其制作成正则表达式以检查这些范围内的价格:

    95.55 thru   95.59         95\.5[5-9]
    95.60 thru   95.99         95\.[6-9][0-9]
    96.00 thru   99.99         9[6-9]\.[0-9][0-9] 
   100.00 thru  999.99         [1-9][0-9][0-9]\.[0-9][0-9]
  1000.00 thru 1039.99         10[0-3][0-9]\.[0-9][0-9]
  1040.00 thru 1043.99         1040[0-3]\.[0-9][0-9]
  1044.00 thru 1044.39         1044\.[0-3][0-9]
  1044.40 thru 1044.44         1044\.4[0-4]

可以做到，但是生成正则表达式字符串的代码不会很漂亮。 (并且对其进行全面测试也不会很好。)