Query to shuffle a string list stored with a separator in a string mysql database column

Question

Is there a single query which can shuffle a string list which is stored in a string column?

E.g. the table has a column list like this:

list
----
a|e|q
k|w|z
...

I'm looking for a simple query which when excuted randomizes the lists, e.g. a|e|q becomes e|q|a, k|w|z becomes w|k|z, etc.

Is this possible with a single query?

please read https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad — nbk, Jul 11 '21 at 18:51
you have to split it and then order it randomly and then concat, but that is not possible in a "simple" query as you see in the link — nbk, Jul 11 '21 at 18:53

score 1 · Answer 1 · answered Jul 11 '21 at 21:55

Here's a demo for MySQL 8.0:

CREATE TABLE mytable (list VARCHAR(10));
INSERT INTO mytable VALUE ('a|e|q'), ('k|w|z');

WITH m AS (SELECT list, ROW_NUMBER() OVER () AS r FROM mytable), 
n AS (SELECT 1 AS N UNION SELECT 2 UNION SELECT 3), 
x AS (SELECT m.r, SUBSTRING_INDEX(SUBSTRING_INDEX(m.list, '|', n.n), '|', -1) AS val FROM n CROSS JOIN m) 
SELECT r, GROUP_CONCAT(val ORDER BY RAND() SEPARATOR '|') as list FROM x GROUP BY r;

Output if I run it repeatedly:

+---+-------+
| r | list  |
+---+-------+
| 1 | a|q|e |
| 2 | w|k|z |
+---+-------+

+---+-------+
| r | list  |
+---+-------+
| 1 | q|e|a |
| 2 | w|k|z |
+---+-------+

+---+-------+
| r | list  |
+---+-------+
| 1 | e|a|q |
| 2 | z|k|w |
+---+-------+

Query to shuffle a string list stored with a separator in a string mysql database column

1 Answers1