You can have the information_schema build the entire query for you and execute it as Dynamic SQL (Prepared Statements as @a1ex07 first mentioned):
SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
GROUP_CONCAT(tb SEPARATOR
' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
INTO @foo_query FROM
(
select CONCAT(table_schema,'.',table_name) tb
from information_schema.tables
where table_name like 'foo_reference%'
) A;
SELECT @foo_query\G
PREPARE stmt FROM @foo_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
First, let's make some sample tables with sample data:
DROP DATABASE IF EXISTS timpost1;
DROP DATABASE IF EXISTS timpost2;
CREATE DATABASE timpost1;
CREATE DATABASE timpost2;
use timpost1
CREATE TABLE cookie_cutter
(
foo_id int,
bar_id int,
bar_weight int,
primary key (foo_id)
) ENGINE=MyISAM;
CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);
Here are the sample tables being made and loaded:
mysql> DROP DATABASE IF EXISTS timpost1;
Query OK, 4 rows affected (0.03 sec)
mysql> DROP DATABASE IF EXISTS timpost2;
Query OK, 3 rows affected (0.02 sec)
mysql> CREATE DATABASE timpost1;
Query OK, 1 row affected (0.00 sec)
mysql> CREATE DATABASE timpost2;
Query OK, 1 row affected (0.00 sec)
mysql> use timpost1
Database changed
mysql> CREATE TABLE cookie_cutter
-> (
-> foo_id int,
-> bar_id int,
-> bar_weight int,
-> primary key (foo_id)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE TABLE timpost1.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost1.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost1.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost2.foo_reference1 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.06 sec)
mysql> CREATE TABLE timpost2.foo_reference2 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> CREATE TABLE timpost2.foo_reference3 LIKE timpost1.cookie_cutter;
Query OK, 0 rows affected (0.05 sec)
mysql> INSERT INTO timpost1.foo_reference1 VALUES (1,17,10),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost1.foo_reference2 VALUES (1,27,90),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost1.foo_reference3 VALUES (1,37,40),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost2.foo_reference1 VALUES (1,47,70),(2,3,20);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost2.foo_reference2 VALUES (1,57,20),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO timpost2.foo_reference3 VALUES (1,67,50),(2,3,20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql>
Now let's build the query and execute it:
mysql> SELECT CONCAT('SELECT * FROM (SELECT bar_id,bar_weight FROM ',
-> GROUP_CONCAT(tb SEPARATOR
-> ' WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM '),
-> ' WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5')
-> INTO @foo_query FROM
-> (
-> select CONCAT(table_schema,'.',table_name) tb
-> from information_schema.tables
-> where table_name like 'foo_reference%'
-> ) A;
Query OK, 1 row affected (0.02 sec)
mysql> SELECT @foo_query\G
*************************** 1. row ***************************
@foo_query: SELECT * FROM (SELECT bar_id,bar_weight FROM timpost1.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost1.foo_reference3 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference1 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference2 WHERE foo_id=1 UNION SELECT bar_id,bar_weight FROM timpost2.foo_reference3 WHERE foo_id=1) as res order by res.bar_weight desc limit 0, 5
1 row in set (0.01 sec)
mysql> PREPARE stmt FROM @foo_query;
Query OK, 0 rows affected (0.01 sec)
Statement prepared
mysql> EXECUTE stmt;
+--------+------------+
| bar_id | bar_weight |
+--------+------------+
| 27 | 90 |
| 47 | 70 |
| 67 | 50 |
| 37 | 40 |
| 57 | 20 |
+--------+------------+
5 rows in set (0.00 sec)
mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)
mysql>
CAVEAT
Notice that the query will cross check all databases in the mysql instance that starts with foo_reference. In the above example, both timpost1 and timpost2 were checked.
Also, notice that the subquery will get 6 rows, and the limit 0,5
properly displays the first 5 rows of the subquery.
Give it a Try !!!
Best Answer
(copied from the chat)
The method to parse JSON string to enumerated table (up to 100 records) applicable to version 5.6.40.
This is example source JSON string.
It may be transferred to server as query parameter to the above query which assigns the value to a variable or inserted immediately into the query text as string literal.
Also It may be transferred to server as query parameter to the below query which parses it, i.e. intermediate user-defined variable is optional.
Now we parse JSON to a table (emulation of JSON_TABLE function available since 8.0 version):
Output:
You may join this parser into your query as a source data subquery and use its
idx
field for ordering the final result in the same order in which the records are posessed in source JSON.