MySQL 8.0: finally, modern SQL

@row := @row + 1. I have a report in production that stands on that trick, and the trick was never guaranteed to work. Evaluation order of user variables in SELECT is undefined, it just happened to behave. MySQL 8.0 went GA last week, and for me the release is about SQL. Window functions and CTEs, the things Postgres people stopped noticing years ago, are here.

The classic task: top three orders per customer. In 5.7 you had a self-join nobody could read a month later, or the variable trick. Now:

SELECT customer_id, order_id, amount
FROM (
    SELECT customer_id, order_id, amount,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY amount DESC
           ) AS rn
    FROM orders
) t
WHERE rn <= 3;

Boring, standard, readable. Exactly what a report query should be.

CTEs are the second gift. WITH lets you name the steps of a report instead of nesting subqueries five levels deep. And WITH RECURSIVE closes an old wound: category trees. Everyone who stored a hierarchy in MySQL either did nested sets or walked the tree in PHP with a query per level. A recursive CTE does it in one statement.

One warning before you rewrite everything. More expressive SQL is not cheaper SQL. A window function over the whole orders table with no useful index on the partition and order columns is still a full scan plus a sort. The query got shorter, the work did not. Read the EXPLAIN like before.

Migration note: 8.0 defaults to utf8mb4. Good default, but check connection settings and column collations before the upgrade surprises you with mixed collation errors in joins.

The report with the variable trick is still in production. I know how to fix it now. It has worked for years, and that is exactly why nobody will let me touch it.