mysql - 如何使此 SQL 查询与 mysql 8 一起使用

Question

以下查询适用于 mysql 5.x，但在 mysql 8.0.x 上出错:

SET @num := 0, @website_id_host := 0;

SELECT website_id_host, url, date_inserted, row_number
FROM (
    SELECT website_id_host, url, date_inserted, @num := if(@website_id_host = website_id_host, @num+1, 1) as row_number, @website_id_host := website_id_host as dummy1
    FROM (
        SELECT website_id_host, url, date_inserted
        FROM records
        WHERE date_inserted between '2019-11-14 00:00:00' and '2019-11-14 23:59:59' and website_id_host is not null
        ORDER BY website_id_host, date_inserted desc
    ) intermediate
) final
where row_number <=100;

我得到的错误是:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL 
server version for the right syntax to use near 'from (  SELECT website_id_host, url, date_inserted, @num := if(@website_id_host ' 
at line 2

该错误不是很清楚出了什么问题。任何人都可以帮助弄清楚为什么它现在失败了？

Answer 1

只需重命名 row_number .它现在是一个函数:

SELECT website_id_host, url, date_inserted, seqnum
FROM (SELECT website_id_host, url, date_inserted,
              @num := if(@website_id_host = website_id_host, @num+1, 1) as seqnum,
              @website_id_host := website_id_host as dummy1
    FROM (SELECT website_id_host, url, date_inserted
          FROM records
          WHERE date_inserted between '2019-11-14 00:00:00' and '2019-11-14 23:59:59' and website_id_host is not null
          ORDER BY website_id_host, date_inserted desc
         ) intermediate
     ) final
where seqnum <= 100;

这段代码有一个隐藏的错误，可能并不总是有效。如 documentation 中所述:

The order of evaluation for expressions involving user variables is undefined. For example, there is no guarantee that SELECT @a, @a:=@a+1 evaluates @a first and then performs the assignment.

您的代码分配了一个变量并在同一个 SELECT 中使用它-- 并且取决于评估的顺序。这被特别记录为不可靠。

此外，正如 documentation 中所述，正在逐步淘汰变量的使用。 :

Previous releases of MySQL made it possible to assign a value to a user variable in statements other than SET. This functionality is supported in MySQL 8.0 for backward compatibility but is subject to removal in a future release of MySQL.

所以，在 MySQL 8+ 中写这个的正确方法是使用 row_number() :

SELECT r.*
FROM (SELECT website_id_host, url, date_inserted,
             ROW_NUMBER() OVER (PARTITION BY website_id_host ORDER BY date_inserted DESC) as seqnum
      FROM records r
      WHERE date_inserted >= '2019-11-14' and
            date_inserted < '2019-11-15' and
            website_id_host is not null
     ) r
WHERE seqnum <= 100

请注意，我还简化了日期比较。