mysql 中的 REGEXP_SUBSTR 相当于什么？

Question

我想从表的字符串列中提取一个单词。

description
===========================
abc order_id: 2 xxxx yyy aa
mmm order_id: 3 nn kk yw

预期结果集

order_id
===========================
2
3

表最多有 100 行，文本长度约为 256 个字符，并且列始终有一个 order_id 存在。所以性能不是问题。

在 Oracle 中，我可以使用 REGEXP_SUBSTR 来解决这个问题。我该如何在 MySQL 中解决这个问题？

编辑1

我正在使用 LOCATE 和 SUBSTR 来解决问题。代码很丑陋。写完代码十分钟后，我就咒骂写出如此丑陋代码的家伙。

我在 MySQL 文档中没有找到 REGEXP_SUBSTR 函数。但我希望它存在..

回答：为什么不能优化表？为什么数据以如此愚蠢的方式存储？

我给出的例子只是表示我正在尝试解决的问题。在实际场景中，我使用基于数据库的第三方排队软件来执行异步任务。队列将 Ruby 对象序列化为文本。我无法控制表结构或数据格式。队列中的任务可以重复出现。在我们的测试设置中，一些重复任务由于数据陈旧而失败。我必须删除这些任务以防止出现错误。此类错误并不常见，因此我不想维护规范化的影子表。

Answer 1

“我在 MySQL 文档中没有找到 REGEXP_SUBSTR 函数。但我希望它存在......”

是的，从 MySQL 8.0 开始支持它。 正则表达式：

REGEXP_SUBSTR(expr, pat[, pos[,occurrence[, match_type]]])

返回字符串 expr 中与模式 pat 指定的正则表达式相匹配的子字符串，如果没有匹配则返回 NULL。如果expr或pat为NULL，则返回值为NULL。

Answer 2

正如 Konerak 所说，MySql 中没有 REGEXP_SUBSTR 的等效项。您可以使用 SUBSTRING 逻辑执行您需要的操作，但它很难看：

SELECT
  SUBSTRING(lastPart.end, 1, LOCATE(' ', lastPart.end) - 1) AS orderId
FROM
  (
    SELECT
      SUBSTRING(dataset.description, LOCATE('order_id: ', dataset.description) + LENGTH('order_id: ')) AS end
    FROM
      (
        SELECT 'abc order_id: 2 xxxx yyy aa' AS description
        UNION SELECT 'mmm order_id: 3 nn kk yw' AS description
        UNION SELECT 'mmm order_id: 1523 nn kk yw' AS description
      ) AS dataset
    ) AS lastPart

---

编辑：您可以尝试这个用户定义函数提供对 MySql 中 perl 正则表达式的访问

SELECT 
  PREG_CAPTURE( '/.*order_id:\s(\d+).*/', dataset.description,1)
FROM
  (
    SELECT 'abc order_id: 2 xxxx yyy aa' AS description
    UNION SELECT 'mmm order_id: 3 nn kk yw' AS description
    UNION SELECT 'mmm order_id: 1523 nn kk yw' AS description
  ) AS dataset

Answer 3

或者你可以这样做并避免丑陋：

select SUBSTRING_INDEX(SUBSTRING_INDEX('habc order_id: 2 xxxx yyy aa',' ',3),' ',-1);

Answer 4

没有 MySQL 的等效项。 MySQL REGEXP 可用于匹配字符串，但不能用于转换它们。

您可以尝试使用存储过程和大量 REPLACE/SUBSTRING 逻辑，或者使用您的编程语言进行操作 - 这应该是最简单的选择。

但您确定您的数据格式选择正确吗？如果您需要 order_id，那么将其存储在不同的列中是否有意义，以便您可以放置​​索引、使用联接等？