How mysql queries specified data in multiple tables

1. Problem description
assume that there are n game order tables in the database, and the table name is game_ [0-9a-z] + _ order.
. It is known that there is an order number 18demo000001. How can we efficiently find out which game table this order is in?

2. Do your own thinking
all you can think of at present is to list all the games first

show tables like "game_%_order"

then manually check one by one, but this is too inefficient.
is there a better way to query more efficiently?

3. Note: only discuss the solution to this scenario, not the reasonableness of the scenario. In fact, the order number has game information, but I don"t know why it doesn"t fall into the game table of its corresponding game, so we need to query the full table of the whole library. Please treat the table as if there is no relationship between the table and the order

< hr >

4. Note: thank you for your answer. There seems to be no better solution. I was wondering if I could use show tables like "game_%_order" to get an array of all table names. Then the select * from table name array where orderID = "18demo000001" is solved. But whether it"s an show tables assignment to an array or an select from array, this doesn"t seem to be possible in mysql.
newcomers are welcome to answer the discussion if they have any relevant implementation on this idea. The current practice is to manually check more than 100 tables to find the location of the error data.
the specific method is

update  game_demo1_order set ext1 = "debug" where orderID = "18demo000001";
update  game_test2_order set ext1 = "debug" where orderID = "18demo000001";
......
update  game_table100_order set ext1 = "debug" where orderID = "18demo000001";

if the table has this data, navicat (mysql command line or other tool should also prompt affected row: 1 , if this table has no data, it will prompt affected row: 0
where ext1 is a reserved field in the table or a field that has no practical meaning and does not affect business
Writing 100 rows of update uses some tricks to copy the result (table name) of show tables like "game_%_order" to sublime,. Use find all to find \ n , make multiple cursors appear on each line, enter update and set ext1 = "debug" where orderID = "18demo000001";
copy the results to sublime, global search

Feb.27,2021

in this case, you can only check the whole table.
you can hash the order number. Then save it in the relative table. Isn't that easy to find


there is a problem with the rules. The naming rules of game_ [0-9a-z] _ order should be described in detail. There are two ways:
1-game_%game_id%_orders:. At this time, your order number should be marked with game_id, so that you can find the order label of
2-game_%begin%_%end%_orders: pure number according to game_id, and artificially specify one table for every 100, 000 orders. Then you can naturally figure out which table it is in.

in fact, if the amount of data is so large that the logic is much more complex, it requires a special process to maintain a set of read tables, which may be based on the game index, the user index, or the date index. When the data is inserted, it is just put into the processing pool, and the independent process parses the data and sends it to each table.


first of all, no rules, no relationship between the order number and table name, is to play hooligans, needless to say, directly pull out and cut
then, according to the rules can not find the corresponding table, that is, either you understand the rules have a problem, or the rules themselves have a problem
in short, give an order number, no rules, can not find the corresponding table, then only traverse all the tables, no other programs. So.. Define your rules!


convert MySQL to ES or index the table set according to the rules, the index points to the table set, the table set points to the dataset, and the dataset points to the rows, to give the result.

MySQL Query : SELECT * FROM `codeshelper`.`v9_news` WHERE status=99 AND catid='6' ORDER BY rand() LIMIT 5
MySQL Error : Disk full (/tmp/#sql-temptable-64f5-1c20666-334db.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
MySQL Errno : 1021
Message : Disk full (/tmp/#sql-temptable-64f5-1c20666-334db.MAI); waiting for someone to free some space... (errno: 28 "No space left on device")
Need Help?