MySQL: Row Literals

Mysql

Question on the Libera/#mysql IRC channel:

Is there a way to split a simple select into multiple returned rows?
For example, select 1, 2, 3 to be returned as rows?

This is actually asking for a table literal notation.
I know of four ways to construct a table literal in MySQL:


UNION ALL

The oldest way to construct a table literal in any SQL that supports UNION is the UNION ALL construct.
Write SELECT statements to return literal rows, and add them together to a table using UNION ALL:

mysql> select i from (
-> select 1 as i union all
-> select 2 as i union all
-> select 3 as i
-> ) as t;
+—+
| i |
+—+
| 1 |
| 2 |
| 3 |
+—+
3 rows in set (0.00 sec)

This has always worked, even on the oldest versions of MySQL.


JSON_TABLE()

There is actually a function to turn a JSON expression into a result table, JSON_TABLE().
It is documented here

.
The function exprects a JSON expression (or simply a literal JSON array in our case), and a JSON path expression with instructions on how to type and name the extracted values.

mysql> select * from json_table( ‘[{“i”: 1}, {“i”:2}, {“i”: 3} ]’,
-> “$[*]” columns(i int path “$.i”)
-> ) as t;
+——+
| i |
+——+
| 1 |
| 2 |
| 3 |
+——+
3 rows in set (0.00 sec)


VALUES statement and ROW() function

MySQL introduces the VALUES

statement and the ROW() function.
VALUES is a statement that returns a table constructed from literal values described with ROW() functions.
So in order to get the table for further processing, we need a subselect.

mysql> select * from (
-> values row(1), row(2), row(3)
-> ) as t;
+———-+
| column_0 |
+———-+
| 1 |
| 2 |
| 3 |
+———-+
3 rows in set (0.00 sec)


WITH and VALUES

We can do the same, but use a Common Table Expression instead of a subselect:

mysql> with t (i) as ( values row(1), row(2), row(3) )
-> select * from t;
+—+
| i |
+—+
| 1 |
| 2 |
| 3 |
+—+
3 rows in set (0.00 sec)