如何在MySQL 5 .7中实现CTE功能? [英] How to achieve CTE functionality in MySQL 5 .7?

问题描述

我有一个USERSEARCH表，该表应用于用户的快速子字符串搜索.此功能适用于在有人输入用户名或名称时进行的自动完成搜索.但是，我感兴趣的查询将仅显示来自用户的匹配项，即搜索者遵循的用户子集.这可以在USERRELATIONSHIP表中找到.

I have a USERSEARCH table that should be used for fast substring searches for users. This feature is for an autocomplete search that occurs while someone is typing in a username or name. However, the query I am interested in will only show matches from users the subset of users the searcher follows. This is found in the USERRELATIONSHIP table.

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"(不考虑用户关系)时，将进行这样的查询:

A query like this would be made when someone has just typed in "Al" (not accounting for user relationships):

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"表的子集，仅包括搜索者关注的用户.这是我尝试过的:

This is blazingly fast because of the existing indices on username_ngram, name_ngram and FULLTEXT(username_ngram, name_ngram). However, in my application context, I need to restrict the search to users that the searcher is following. I would like to replace the "myapp.usersearch" table with a subset of the "myapp.usersearch" table including only users the searcher is following. Here is what I attempted:

    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子句.

Unfortunately MySQL 5.7 does not support the CTE WITH clause.

是否可以在所有后续子查询中引用查询的第1部分，而无需重新查询该人员所跟踪用户的user_id? (在MySQL 5.7中)

更新:

在MySQL 5.7中真的没有办法多次引用查询吗?在我看来，这似乎是任何数据库的基本任务.

Is there really no way to reference a query multiple times in MySQL 5.7? Something seems off as this appears to me as a fundamental task for any db.

为什么不做:"x将y加入a或b或c"?我的子字符串查询的速度取决于以下索引:

Why not do : "x join y on a or b or c"? The speed of my substring query depends on the following indices:

index(username_ngram)

index(name_ngram)

FULLTEXT(username_ngram, name_ngram)

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

And using OR is not helped by any indices.

推荐答案

MySQL 5.7不支持公共表表达式； WITH语法仅在8.0版中可用.

MySQL 5.7 does not support common table expression; the WITH syntax is available in version 8.0 only.

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

Since your existing query runs fast, filtering in an outer query might be viable solution:

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，因为我认为这更接近于您想要的(如果不符合您的要求，您可以将其改回)

• I turned LEFT JOIN to an INNER JOIN because I think that's closer to what you want (you can change it back if it doesn't fit you requirement)

您需要一个ORDER BY子句与LIMIT一起使用，否则当结果集中有10行以上时，结果将不确定.

You need an ORDER BY clause to go along with the LIMIT, otherwise the results are not deterministic when there are more that 10 rows in the resultset

这篇关于如何在MySQL 5 .7中实现CTE功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！