EXPLAIN INTO

The INTO clause for EXPLAIN introduced in MySQL 8.1 allows storing the output of an EXPLAIN query in a user variable, instead of returning it to the client. It is currently only supported for the JSON format, but that should be sufficient for most use cases.
With the EXPLAIN output stored in a user variable it can be treated just like any other JSON object in MySQL. This lets you programmatically extract, manipulate, and store data from EXPLAINs directly in the server.

As an example we can take this query

mysql> SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id;
+-------------+----------+
| name        | quantity |
+-------------+----------+
| Screwdriver |       23 |
| Screwdriver |        1 |
| Locket      |       17 |
| Armoire     |       42 |
| Armoire     |       16 |
+-------------+----------+

If we were interested in just the cost estimate for this query, we could start with explaining the query into @explain_output

mysql> EXPLAIN FORMAT=JSON INTO @explain_output SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id;
Query OK, 0 rows affected (0,00 sec)

The only output we now get from this EXPLAIN is a "Query OK". If we want to see the full EXPLAIN output we could SELECT @explain_output;, but the full JSON output takes so much space, and I'm only interested in the cost. To extract the cost from the JSON object we just use MySQL's JSON functions, since this is a regular old JSON object.

mysql> SELECT JSON_EXTRACT(@explain_output, "$.query_block.cost_info.query_cost") AS query_cost;
+------------+
| query_cost |
+------------+
| "1.60"     |
+------------+

EXPLAIN FOR SCHEMA

MySQL 8.2 introduces EXPLAIN FOR SCHEMA, which allows running EXPLAIN queries in another schema than the current active schema. This makes it possible to e.g. create a procedure that explains the most run queries in each of your schemas and gather statistics about them.
If the current schema is different from the schema of the tables we want to query, we can

mysql> USE statistics;
mysql> EXPLAIN FORMAT=TREE FOR SCHEMA customer1 SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id;
-> Nested loop inner join  (cost=2.3 rows=5)
    -> Table scan on items  (cost=0.55 rows=3)
    -> Index lookup on orders using fk_item_id (item_id=items.id)  (cost=0.472 rows=1.67)

This might not be too useful for a single EXPLAIN every now and then, but if we have several schemas with the same table structure and want to gather statistics about all of them in one go, we could create a procedure that does that for us:

DELIMITER |
CREATE PROCEDURE explain_query_for_schema(IN schema_name VARCHAR(64), IN query VARCHAR(1000))
BEGIN
    SET @explain_stmt = CONCAT("EXPLAIN FORMAT=JSON INTO @explain_output FOR SCHEMA ", schema_name, " ", query);
    PREPARE stmt FROM @explain_stmt;
    EXECUTE stmt;
    INSERT INTO explain_outputs (schema_name, query, explain_output) VALUES (schema_name, query, @explain_output);
END |

CREATE PROCEDURE explain_query_for_all_schemas(IN query VARCHAR(1000))
BEGIN
    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE schema_name VARCHAR(64);
    DECLARE cur_schema_names CURSOR FOR SELECT schema_name_table.schema_name FROM schema_name_table;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur_schema_names;

    explain_loop: LOOP
        FETCH cur_schema_names INTO schema_name;
        IF done THEN
            LEAVE explain_loop;
        END IF;
        CALL explain_query_for_schema(schema_name, query);
    END LOOP;
    CLOSE cur_schema_names;
END |
DELIMITER ;

SET @query = "SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id";
CALL explain_query_for_all_schemas(@query);

SELECT schema_name, query, JSON_EXTRACT(explain_output, "$.query_block.cost_info.query_cost") AS query_cost, created_at FROM explain_outputs;
+-------------+---------------------------------------------------------------------------+------------+---------------------+
| schema_name | query                                                                     | query_cost | created_at          |
+-------------+---------------------------------------------------------------------------+------------+---------------------+
| customer1   | SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id | "2.30"     | 2023-11-14 20:56:47 |
| customer2   | SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id | "0.70"     | 2023-11-14 20:56:47 |
| customer3   | SELECT name, quantity FROM orders JOIN items ON orders.item_id = items.id | "9.10"     | 2023-11-14 20:56:47 |
+-------------+---------------------------------------------------------------------------+------------+---------------------+

And that's it. Two relatively simple extensions to EXPLAIN that may not look important at first glance, but enables stored procedures to process EXPLAIN output programmatically. We use it ourselves for MySQL Autopilot Indexing, where EXPLAIN INTO and EXPLAIN FOR SCHEMA are vital for gathering data to suggest better indexes. We hope it will be as useful to you as it is to us.

If you want to read more, this is all documented in the MySQL EXPLAIN documentation.