mysql - 如何在 MySQL 5 .7 中实现 CTE 功能？

Question

我有一个 USERSEARCH 表，应该用于用户的快速子字符串搜索。此功能用于在有人输入用户名或姓名时进行的自动完成搜索。但是，我感兴趣的查询只会显示搜索者所关注的用户子集的匹配项。这可以在USERRELATIONSHIP 表中找到。

USERSEARCH
-----------------------------------------------
user_id(FK)    username_ngram          name_ngram
1              "AleBoy leBoy eBoy..."  "Ale le e"
2              "craze123 raze123 ..."  "Craze raze aze ze e"
3              "john1990 ohn1990 ..."  "John ohn hn n"
4              "JJ_1 J_1 _1 1"         "JJ"


USERRELATIONSHIP
-----------------------------------------------
user_id(FK)    follows_id(FK)
2              1
2              3

当有人刚刚输入“Al”(不考虑用户关系)时，会进行这样的查询:

SELECT * FROM myapp.usersearch where username_ngram like 'Al%'
        UNION DISTINCT
        SELECT * FROM myapp.usersearch where name_ngram like 'Al%'
        UNION DISTINCT
        SELECT * FROM myapp.usersearch                            
        WHERE MATCH (username_ngram, name_ngram) AGAINST ('Al')  
        LIMIT 10

由于 username_ngram、name_ngram 和 FULLTEXT(username_ngram, name_ngram) 上的现有索引，这非常快。但是，在我的应用程序上下文中，我需要将搜索限制为搜索者所关注的用户。我想用“myapp.usersearch”表的子集替换“myapp.usersearch”表，其中仅包括搜索者关注的用户。这是我的尝试:

    WITH

--Part 1, restrict the USERSEARCH table to just the users that are followed by searcher

        tempUserSearch AS (SELECT T1.id, T2.username_ngram, T2.name_ngram FROM
        (SELECT follows_id FROM myapp.userrelationship WHERE user_id = {user_idOfSearcher} ) AS T1 
        LEFT JOIN myapp.usersearch AS T2  ON T2.user_id = T1.follows_id)

            SELECT * FROM tempUserSearch where username_ngram like 'Al%'
            UNION DISTINCT
            SELECT * FROM tempUserSearch where name_ngram like 'Al%'
            UNION DISTINCT
            SELECT * FROM tempUserSearch                            
            WHERE MATCH (username_ngram, name_ngram) AGAINST ('Al')  
            LIMIT 10

不幸的是，MySQL 5.7 不支持 CTE WITH 子句。

有没有办法在所有后续子查询中引用查询的第 1 部分，而无需重新查询该人关注的用户的 user_ids？ (在 MySQL 5.7 中)

更新:

在 MySQL 5.7 中真的没有办法多次引用查询吗？似乎有些不对劲，因为这在我看来是任何数据库的基本任务。

为什么不这样做:“x 在 a 或 b 或 c 上加入 y”？我的子字符串查询的速度取决于以下索引:

index(username_ngram)

index(name_ngram)

FULLTEXT(username_ngram, name_ngram)

并且使用 OR 不受任何索引的帮助。

Answer 1

MySQL 5.7 不支持普通表表达式； WITH语法仅在 8.0 版中可用。

由于您现有的查询运行速度很快，因此在外部查询中进行过滤可能是可行的解决方案:

SELECT ur.id, ng.username_ngram, ng.name_ngram
FROM myapp.userrelationship ur
INNER JOIN (
    SELECT * FROM myapp.usersearch WHERE username_ngram LIKE 'Al%'
    UNION DISTINCT
    SELECT * FROM myapp.usersearch WHERE name_ngram LIKE 'Al}%'
    UNION DISTINCT
    SELECT * FROM myapp.usersearch WHERE MATCH (username_ngram, name_ngram) AGAINST ('Al')  
) ng ON ng.user_id = ur.follows_id
WHERE ur.user_id = {user_idOfSearcher}
ORDER BY ??
LIMIT 10

笔记:

我转了 LEFT JOIN到 INNER JOIN因为我认为这更接近你想要的(如果它不符合你的要求，你可以把它改回来)

您需要一个 ORDER BY条款一起去LIMIT ，否则当结果集中的行数超过 10 行时，结果不确定