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