How do I query same table of several databases with same structure and get all the query result together?

Question

I have several databases that all have the same structure (same tables, same fields of each table).

| sword_0            |
| sword_1            |
| sword_2            |
| sword_3            |
| sword_4            |
| sword_5            |
| sword_6            |
| sword_7            |
| sword_8            |
| sword_9            |

+-------------------+
| Tables_in_sword_5 |
+-------------------+
| t_attr            |
| t_basics          |
| t_data            |
| t_equip           |
| t_friend          |
| t_mail            |
| t_resources       |
| t_union           |
| t_union_member    |
+-------------------+

What I need to do is (I do use sword_5; first, then):

select tba.rid from (
    select rid from t_basics where online_time <= (create_time + 259200)
) as tba
inner join
(select * from t_attr where type = 1000002 and heroLv >= 300) as tbb
on tba.rid = tbb.rid;

So the problem is that I don't want to switch between each database (use sword_0; use sword_1;....) and paste the same query and then copy all the query results manually.

Is there a way I can loop all the databases and get all results for once? (rid is unique among all those sword_* databases).

Answer 1

You don't need to use ... each database. Just prefix your table with the database name and UNION or UNION ALL your queries (https://dev.mysql.com/doc/refman/8.0/en/union.html):

SELECT rid FROM sword_0.t_basics
UNION ALL
SELECT rid FROM sword_1.t_basics
UNION ALL
SELECT rid FROM sword_2.t_basics

etc...

To answer your comment, yes it's possible to do this a bit more dynamically using a prepared statement. Here's a simple example:

# group_concat_max_len is usually set to 1024, too low!
SET @@group_concat_max_len = 1000000;
# create your query as a string and assign it to a variable
SET @queryString = (
    SELECT GROUP_CONCAT(
        CONCAT('SELECT rid FROM `', s.SCHEMA_NAME, '`.basics')
        SEPARATOR ' UNION ALL '
    )
    FROM information_schema.SCHEMATA s
    WHERE s.SCHEMA_NAME LIKE 'sword%'
);

# prepare, execute and deallocate your statement
# https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html
PREPARE stmt FROM @queryString;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;