mysql - 如何在 JOIN 语句的 WHERE 中使用 MySQL REGEXP

Question

我有两个表A和B表 A 包含列:ID 和 POST表 B 包含列:ID、POST_ID 和 UPERS

我想选择 a.POST 与正则表达式匹配的所有记录

'\\[cd(i|b)?(=[a-z0-9]+)?\\].+\\[/cd(i|b)?\\]' 

并在 a.ID = b.POST_ID 上 JOIN 表 B，其中 b.UPPERS 与正则表达式匹配

'(\\|[0-9]+\\![0-9]{4}[-]+[0-9]{2}[-]+[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2},){1,}' 

我提出了以下语句，但即使列包含与正则表达式匹配的内容，它也不会返回任何行

SELECT a.*,b.* 
FROM a JOIN
     b 
     ON b.POST_ID=a.ID 
WHERE a.POST RLIKE '\\[cd(i|b)?(=[a-z0-9]+)?\\].+\\[/cd(i|b)?\\]' AND
      b.UPPERS REGEXP '(\\|[0-9]+\\![0-9]{4}[-]+[0-9]{2}[-]+[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2},){1,}'

摘要:

我想选择用户发送与此正则表达式匹配的内容的记录

 '\\[cd(i|b)?(=[a-z0-9]+)?\\].+\\[/cd(i|b)?\\]' 

然后使用正则表达式检查该帖子是否至少收到了两次点赞(或点赞)

'(\\|[0-9]+\\![0-9]{4}[-]+[0-9]{2}[-]+[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2},){2,}'

可以简单地分解为:

a prefix pipe: |
a user id: [0-9]+
an exclamation mark: !
a datetime: [0-9]{4}[-]+[0-9]{2}[-]+[0-9]{2} [0-9]{2}:[0-9]{2}:[0-9]{2}
and a sufix: ,
NOTE: {2,}  simply to check how many times the match occurs

请有人指出我做错了什么的正确方向。

示例表数据:

Table A
ID    | POST
23  match found [cd=plain]6h+#gtyr[/cd]
24       match found [cd]65#%gte2!iu[/cd]
25       match found [cdi]*tre&y^g82u[/cdi]
26       no match found *tre&y^g82u
27       no match found rtyure99
28       match found [cdb]aha87ulchr[/cdb]

Table B
ID  |   POST_ID     |   UPPERS

4       24              |98!2018-02-10 22:43:03,
                    |35!2018-02-08 20:42:09,
                                            |3!2018-02-05 02:05:07,

5       26              |2!2018-02-10 22:43:03,
                    |30!2018-02-08 20:42:09,

6       25              |21!2018-02-10 22:43:03,

7       27              |23!2018-02-10 22:43:03,
                    |11!2018-02-08 20:42:09,

注意:表 B 中的 POST_ID 是引用表 A 的外键

Answer 1

如果您不介意，我实际上将回答您实际问题背后的问题。我相信我们可以解决为什么正则表达式不能按您的预期工作，但它引出了一个问题:为什么要使用正则表达式来完成如此简单的任务？

很多情况下，人们首先只是使用数据库来存储与代码中出现的格式相同的内容。但是，如果您花一点时间以有意义的方式分解数据，您就可以从简陋的 MySQL 中释放出巨大的力量。

考虑一下您希望此查询回答的问题:

• 哪些符合特定条件的帖子已被提升？

正如您已经意识到的那样，这建议使用两个表 - 一个用于存储有关帖子的信息，另一个用于存储有关谁发布帖子的信息。为了使您的查询快速、简单，请考虑信息的哪些属性将显示在您的 where 子句中。

您希望帖子包含在某些标记中。为了使您的搜索更加高效，请将标记标签放在其自己的列中:

CREATE TABLE `posts` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `tag` enum('cd','cdi','cdb') DEFAULT NULL,
  `tag_value` varchar(11) DEFAULT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对于上面列出的数据，表格可能类似于:

+-----+------+-----------+-------------+
| id  | tag  | tag_value | content     |
+-----+------+-----------+-------------+
|  23 | cd   | plain     | 6h+#gtyr    |
|  24 | cd   | NULL      | 65#%gte2!iu |
|  25 | cdi  | NULL      | *tre&y^g82u |
|  26 | NULL | NULL      | *tre&y^g82u |
|  27 | NULL | NULL      | rtyure99    |
|  28 | cdb  | NULL      | aha87ulchr  |
+-----+------+-----------+-------------+

将数据放入需要更多的工作(这是您的正则表达式功能更好地应用的地方，当您创建 INSERT 时)，但现在您可以用它做各种事情很容易。我对 tag 列使用了 ENUM，因为这样搜索起来速度非常快。如果您有大量标签或不知道它们是什么，则可以使用 VARCHAR 来代替。

那么如何跟踪UPPERS？这部分变得非常容易。您所需要的只是一个表格，其中每次有人添加某些东西时都会有一行:

CREATE TABLE `uppers` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `post_id` int(11) DEFAULT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

目前，当有人更新某些内容时，你必须去查找相关记录，向其中添加新数据，然后将其保存回来。现在您可以将记录放入表中。时间会自动设置；您需要插入的只是 user_id 和 post_id。您的一些数据可能如下所示:

+----+---------+---------+---------------------+
| id | user_id | post_id | time                |
+----+---------+---------+---------------------+
|  2 |      98 |      24 | 2018-02-10 15:23:03 |
|  3 |      35 |      24 | 2018-02-10 15:23:23 |
|  4 |      27 |      24 | 2018-02-10 15:23:43 |
|  5 |       2 |      26 | 2018-02-10 15:24:16 |
|  6 |      30 |      26 | 2018-02-10 15:24:28 |
+----+---------+---------+---------------------+

现在您可以利用 MySQL 引擎的强大功能来捕获您需要的所有信息:

所有带有所需标签的帖子:

SELECT * FROM posts where tag IN ('cd', 'cdi', 'cdb')

所有帖子均带有所需标签且至少有一个:

SELECT posts.*, uppers.user_id, uppers.time
    FROM posts
    INNER JOIN uppers ON posts.id = uppers.post_id
    WHERE tag IN ('cd', 'cdi', 'cdb')

这将为每个后上组合返回一行。 INNER JOIN 意味着它不会返回任何在上表中没有匹配项的帖子。这可能就是您正在寻找的内容，但是如果您想通过帖子 ID 将 ups 分组在一起，您可以要求 MySQL 为您将它们分组:

SELECT posts.*, COUNT(uppers.user_id)
    FROM posts
    INNER JOIN uppers
    WHERE tag IN ('cd', 'cdi', 'cdb')
    GROUP BY posts.id

如果您想排除同一用户的重复发布，您可以轻松地只计算每个帖子的唯一用户 ID:

SELECT posts.*, COUNT(DISTINCT uppers.user_id)
    FROM posts
    INNER JOIN uppers
    WHERE tag IN ('cd', 'cdi', 'cdb')
    GROUP BY posts.id

您可以使用许多函数(例如 COUNT())来处理分组在一起的数据。您可以使用 MAX(uppers.time) 来获取该帖子的最新发布时间，也可以使用诸如 GROUP_CONCAT() 之类的函数将这些值放在一个长字符串中。

最底层的是，通过将数据分解为基本部分，您可以让 MySQL(或任何其他关系数据库)更有效地工作，并且生活变得更加轻松。