We have received a wonderful greeting message from the MySQL Community Team again this year.
"A Message From the Community Team":
https://blogs.oracle.com/mysql/post/a-message-from-the-community-team-2024https://blogs.oracle.com/mysql/post/a-message-from-the-community-team-2024
select char(32,95,32,32,32,32,95 using ASCII) union select char(124,32,124,32,32,124,32,124 using ASCII) union select char(124,32,124,95,95,124,32,124,32,32,95,95,32,95,32,32,95,32,95,95,32,32,32,95,32,95,95,32,32,32,95,32,32,32,95 using ASCII) union select char(124,32,32,95,95,32,32,124,32,47,32,95,96,32,124,124,32,39,95,32,32,124,32,39,95,32,32,124,32,124,32,124,32,124 using ASCII) union select char(124,32,124,32,32,124,32,124,124,32,40,95,124,32,124,124,32,124,95,41,32,124,124,32,124,95,41,32,124,124,32,124,95,124,32,124 using ASCII) union select char(124,95,124,32,32,124,95,124,32,92,95,95,44,95,124,124,32,46,95,95,47,32,124,32,46,95,95,47,32,32,92,95,95,44,32,124 using ASCII) union select char(32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,124,32,124,32,32,32,32,124,32,124,32,32,32,32,32,32,95,95,47,32,124 using ASCII) union select char(32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,124,95,124,32,32,32,32,124,95,124,32,32,32,32,32,124,95,95,95,47 using ASCII) union select char(32,95,32,32,32,32,95,32,32,32,32,32,32,32,32,32,95,32,32,95,32,32,32,32,32,32,95 using ASCII) union select char(124,32,124,32,32,124,32,124,32,32,32,32,32,32,32,124,32,124,40,95,41,32,32,32,32,124,32,124 using ASCII) union select char(124,32,124,95,95,124,32,124,32,32,95,95,95,32,32,124,32,124,32,95,32,32,32,95,95,124,32,124,32,32,95,95,32,95,32,32,95,32,32,32,95,32,32,95,95,95 using ASCII) union select char(124,32,32,95,95,32,32,124,32,47,32,95,32,92,32,124,32,124,124,32,124,32,47,32,95,96,32,124,32,47,32,95,96,32,124,124,32,124,32,124,32,124,47,32,95,95,124 using ASCII) union select char(124,32,124,32,32,124,32,124,124,32,40,95,41,32,124,124,32,124,124,32,124,124,32,40,95,124,32,124,124,32,40,95,124,32,124,124,32,124,95,124,32,124,92,95,95,32,92 using ASCII) union select char(124,95,124,32,32,124,95,124,32,92,95,95,95,47,32,124,95,124,124,95,124,32,92,95,95,44,95,124,32,92,95,95,44,95,124,32,92,95,95,44,32,124,124,95,95,95,47 using ASCII) union select char(32,95,95,95,95,95,95,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,95,95,47,32,124 using ASCII) union select char(124,32,32,95,95,95,95,124,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,32,124,95,95,95,47 using ASCII) union select char(124,32,124,95,95,32,32,95,32,95,95,32,32,95,95,95,32,32,32,95,32,95,95,32,95,95,95 using ASCII) union select char(124,32,32,95,95,124,124,32,39,95,95,124,47,32,95,32,92,32,124,32,39,95,32,96,32,95,32,92 using ASCII) union select char(124,32,124,32,32,32,124,32,124,32,32,124,32,40,95,41,32,124,124,32,124,32,124,32,124,32,124,32,124 using ASCII) union select char(124,95,124,32,32,32,124,95,124,32,32,32,92,95,95,95,47,32,124,95,124,32,124,95,124,32,124,95,124 using ASCII) union select char(32,95,95,32,32,95,95,32,32,32,32,32,32,32,32,32,32,95,95,95,95,95,32,32,32,95,95,95,95,32,32,32,95 using ASCII) union select char(124,32,32,92,47,32,32,124,32,32,32,32,32,32,32,32,47,32,95,95,95,95,124,32,47,32,95,95,32,92,32,124,32,124 using ASCII) union select char(124,32,92,32,32,47,32,124,32,95,32,32,32,95,32,124,32,40,95,95,95,32,32,124,32,124,32,32,124,32,124,124,32,124 using ASCII) union select char(124,32,124,92,47,124,32,124,124,32,124,32,124,32,124,32,92,95,95,95,32,92,32,124,32,124,32,32,124,32,124,124,32,124 using ASCII) union select char(124,32,124,32,32,124,32,124,124,32,124,95,124,32,124,32,95,95,95,95,41,32,124,124,32,124,95,95,124,32,124,124,32,124,95,95,95,95 using ASCII) union select char(124,95,124,32,32,124,95,124,32,92,95,95,44,32,124,124,95,95,95,95,95,47,32,32,92,95,95,95,92,95,92,124,95,95,95,95,95,95,124 using ASCII) union select char(32,32,32,32,32,32,32,32,32,32,95,95,47,32,124 using ASCII) union select char(32,32,32,32,32,32,32,32,32,124,95,95,95,47 using ASCII);
But the mumumumumultipul UNION is not elegant for us.
We are database engineer, so we need more SQL like SQL.
This is one of the answer:
Make data:
CREATE TABLE greeting (id integer, m integer, p json); INSERT INTO greeting VALUES (1,32,'[1,3,4,5,6]'),(1,95,'[2,7]'),(2,32,'[2,4,5,7]'),(2,124,'[1,3,6,8]'), (3,32,'[2,7,9,10,13,15,16,18,21,22,23,25,28,29,30,32,33,34]'), (3,95,'[4,5,11,12,14,17,19,20,24,26,27,31,35]'),(3,124,'[1,3,6,8]'),(4,32,'[2,3,6,7,9,11,14,17,20,21,23,26,27,29,31,33]'), (4,39,'[18,24]'),(4,47,'[10]'),(4,95,'[4,5,12,19,25]'),(4,96,'[13]'),(4,124,'[1,8,15,16,22,28,30,32,34]'), (5,32,'[2,4,5,7,10,14,17,21,24,28,31,35]'),(5,40,'[11]'),(5,41,'[20,27]'),(5,95,'[12,19,26,33]'), (5,124,'[1,3,6,8,9,13,15,16,18,22,23,25,29,30,32,34,36]'),(6,32,'[4,5,9,17,22,24,29,30,35]'),(6,44,'[13,34]'), (6,46,'[18,25]'),(6,47,'[21,28]'),(6,92,'[10,31]'),(6,95,'[2,7,11,12,14,19,20,26,27,32,33]'),(6,124,'[1,3,6,8,15,16,23,36]'), (7,32,'[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,17,19,20,21,22,24,26,27,28,29,30,31,35]'), (7,47,'[34]'),(7,95,'[32,33]'),(7,124,'[16,18,23,25,36]'),(8,47,'[35]'), (8,32,'[1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,19,20,21,22,26,27,28,29,30]'), (8,95,'[17,24,32,33,34]'),(8,124,'[16,18,23,25,31]'),(9,95,'[2,7,17,20,27]'), (9,32,'[1,3,4,5,6,8,9,10,11,12,13,14,15,16,18,19,21,22,23,24,25,26]'),(10,40,'[19]'), (10,32,'[2,4,5,7,9,10,11,12,13,14,15,17,22,23,24,25,27]'),(10,41,'[21]'),(10,95,'[20]'),(10,124,'[1,3,6,8,16,18,26,28]'), (11,32,'[2,7,9,10,14,15,17,19,21,22,23,27,29,30,33,35,36,38,39,40,42,43]'), (11,95,'[4,5,11,12,13,20,24,25,31,32,34,37,41,44,45,46]'),(11,124,'[1,3,6,8,16,18,26,28]'), (12,32,'[2,3,6,7,9,11,13,15,17,20,22,24,27,29,31,34,37,39,41,44]'),(12,47,'[10,23,30,43]'),(12,92,'[14]'), (12,95,'[4,5,12,25,32,45,46]'),(12,96,'[26,33]'),(12,124,'[1,8,16,18,19,21,28,35,36,38,40,42,47]'), (13,32,'[2,4,5,7,10,14,17,20,23,27,30,34,37,41,46]'),(13,40,'[11,24,31]'),(13,41,'[13]'),(13,92,'[43,47]'), (13,95,'[12,25,32,39,44,45]'),(13,124,'[1,3,6,8,9,15,16,18,19,21,22,26,28,29,33,35,36,38,40,42]'), (14,32,'[4,5,9,15,22,29,36,41]'),(14,44,'[26,33,40]'),(14,47,'[14,47]'),(14,92,'[10,23,30,37]'), (14,95,'[2,7,11,12,13,17,20,24,25,27,31,32,34,38,39,44,45,46]'),(14,124,'[1,3,6,8,16,18,19,21,28,35,42,43]'), (15,32,'[1,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,41]'), (15,47,'[40]'),(15,95,'[2,3,4,5,6,7,38,39]'),(15,124,'[42]'),(16,47,'[41]'),(16,95,'[4,5,6,7,38,39,40]'), (16,32,'[2,3,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36]'), (16,124,'[1,8,37]'),(17,32,'[2,6,7,9,12,13,17,18,19,21,24]'),(17,95,'[4,5,8,10,11,14,15,16,20,22,23,25,26,27]'), (17,124,'[1,3]'),(18,32,'[2,3,8,14,16,18,20,23,25,27]'),(18,39,'[9,21]'),(18,47,'[13]'),(18,92,'[17,28]'), (18,95,'[4,5,10,11,15,22,26]'),(18,96,'[24]'),(18,124,'[1,6,7,12,19]'),(19,32,'[2,4,5,6,8,10,11,13,17,20,22,24,26,28]'), (19,40,'[14]'),(19,41,'[16]'),(19,95,'[15]'),(19,124,'[1,3,7,9,12,18,19,21,23,25,27,29]'),(20,32,'[4,5,6,10,11,12,18,22,26]'), (20,47,'[17]'),(20,92,'[13]'),(20,95,'[2,8,14,15,16,20,24,28]'),(20,124,'[1,3,7,9,19,21,23,25,27,29]'), (21,32,'[1,4,5,8,9,10,11,12,13,14,15,16,17,23,24,25,30,31,32]'),(21,95,'[2,3,6,7,18,19,20,21,22,26,27,28,29,33]'), (22,32,'[2,3,6,7,9,10,11,12,13,14,15,16,18,24,26,29,31,33]'),(22,47,'[5,17,25]'),(22,92,'[4,30]'), (22,95,'[19,20,21,22,27,28]'),(22,124,'[1,8,23,32,34]'),(23,32,'[2,4,5,7,9,11,12,13,15,17,22,23,25,27,28,30,33]'), (23,40,'[18]'),(23,47,'[6]'),(23,92,'[3]'),(23,95,'[10,14,19,20,21]'),(23,124,'[1,8,16,24,26,29,31,32,34]'), (24,32,'[2,7,10,12,14,16,21,23,25,27,28,30,33]'),(24,47,'[5]'),(24,92,'[4,17,22]'),(24,95,'[18,19,20]'), (24,124,'[1,3,6,8,9,11,13,15,24,26,29,31,32,34]'),(25,32,'[2,4,5,7,10,14,16,22,25,30,33]'),(25,41,'[21]'), (25,95,'[12,17,18,19,20,27,28,35,36,37,38]'),(25,124,'[1,3,6,8,9,11,13,15,23,24,26,29,31,32,34]'), (26,32,'[4,5,9,14,23,24]'),(26,44,'[13]'),(26,47,'[22]'),(26,92,'[10,25,29,31]'), (26,95,'[2,7,11,12,17,18,19,20,21,26,27,28,30,33,34,35,36,37,38]'),(26,124,'[1,3,6,8,15,16,32,39]'), (27,32,'[1,2,3,4,5,6,7,8,9,10,14]'),(27,47,'[13]'),(27,95,'[11,12]'),(27,124,'[15]'),(28,32,'[1,2,3,4,5,6,7,8,9]'), (28,47,'[14]'),(28,95,'[11,12,13]'),(28,124,'[10]');
SQL like SQL:)
set @@cte_max_recursion_depth=1500; WITH RECURSIVE seq AS (SELECT 0 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 100), ex AS ( SELECT g.id, g.m, JSON_UNQUOTE(JSON_EXTRACT(g.p, CONCAT('$[', seq.n, ']')))+0 v FROM greeting g JOIN seq ON JSON_LENGTH(g.p) > seq.n ) SELECT GROUP_CONCAT(CHAR(m USING ASCII) ORDER BY v SEPARATOR '') FROM ex GROUP BY id;
Result:
Try it yourself!
Next
I believe that this data structure can be made more elegant.
Would anyone like to give it a try?
Conclusion
Have a good New Year!!