MySQL 8.0 introduces multiple advanced SQL functions, which significantly improves the efficiency and expressive capabilities of data operations. 1. Common table expressions (CTEs) replace complex nested subqueries, making the logic clearer and more maintainable; 2. Recursive queries combine with CTEs to support hierarchical data processing, such as organizational structure or classification tree traversal; 3. Window functions implement inter-row calculation without grouping and aggregation, and are suitable for scenarios such as ranking and cumulative statistics; 4. JSON enhancement function optimizes the query and index of JSON fields, improving semi-structured data processing performance. These features not only simplify SQL writing, but also enhance the functional boundaries of the database.

MySQL 8.0 brought a bunch of advanced SQL features that can really change how you work with data. If you're still using basic queries and haven't looked into what's new, you're probably missing out on some powerful tools. These aren't just for DBAs or hardcore developers — they can make your day-to-day SQL writing cleaner, faster, and more expressive.

Let's go over a few key features that stand out and how you can start using them in real scenarios.

Common Table Expressions (CTEs)

CTEs are like temporary result sets you can reference in a SELECT, INSERT, UPDATE, or DELETE statement. They make complex queries easier to read and maintain — especially when dealing with nested subqueries.

Before CTEs, you might have written something like:

 SELECT * FROM (
    SELECT id, name FROM users WHERE created_at > '2023-01-01'
) AS new_users;

Now with CTEs:

 WITH new_users AS (
    SELECT id, name FROM users WHERE created_at > '2023-01-01'
)
SELECT * FROM new_users;

This becomes really useful when chaining logic. For example, if you need to calculate user growth month-over-month, you can break it down step by step.

Use CTEs when:

• You have deeply nested subqueries
• You want to reuse a query block multiple times
• You're building recursive queries (more on that next)

Recursive Queries with CTEs

This is where CTEs get really powerful. Recursive queries let you traverse hierarchical data — like organizational charts, categories with subcategories, or threaded comments.

Here's a simple example for building a hierarchy:

 WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.id, c.name, c.parent_id
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;

What's happening here:

• The first part selects the root categories (no parent)
• The second part keeps joining until all child levels are found
• The recursion stops when there are no more matches

This is super handy when you need to flatten or analyze nested data without relying on application logic.

Window Functions

Window functions let you perform calculations across a set of table rows that are somehow related to the current row — without collapse the result set like GROUP BY does.

For example, calculating a running total:

 SELECT id, amount,
    SUM(amount) OVER (ORDER BY id) AS running_total
FROM sales;

Or comparing each row to its group average:

 SELECT product_id, price,
    AVG(price) OVER (PARTITION BY category_id) AS avg_price
FROM products;

Some common use cases:

• Ranking rows (ROW_NUMBER, RANK, DENSE_RANK)
• Cumulative sums or averages
• Comparing current row with previous/next rows (LAG, LEAD)

They're a bit tricky to get right at first, but once you understand how the window frame works (ROWS BETWEEN ...), they become indispensable.

JSON Enhancements

MySQL has been improving JSON support for a while, and 8.0 continues that trend. You can now do more with JSON data types without having to extract everything in your app code.

For example, querying nested JSON:

 SELECT * FROM orders
WHERE JSON_EXTRACT(details, '$.items[0].product_id') = '123';

And there's better indexing support for JSON columns using virtual columns:

 ALTER TABLE orders
ADD COLUMN product_id VARCHAR(50)
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(details, '$.items[0].product_id'))) STORED;

CREATE INDEX idx_product_id ON orders(product_id);

This makes querying JSON fields more performant and practical — especially if you're storing semi-structed data.

These features aren't just bells and whistles. They solve real problems in data modeling, performance, and readability. You don't need to use all of them all the time, but knowing when to reach for a CTE, a window function, or a recursive query can make a big difference.

And honestly, once you get used to them, going back to older SQL syntax feels like driving without power steering.

Basically that's it.

The above is the detailed content of Advanced SQL Features in MySQL 8.0: A Deep Dive. For more information, please follow other related articles on the PHP Chinese website!