DWQA QuestionsCategory: DatabaseAfter MySQL is divided into tables, how to query the records in all tables?
Senior typist asked 1 month ago

After a table is divided into n tables, how to query all the data with table type = 1?
(a user table, divided into 10 tables, I want to get all the data with user type = 1)

Senior typist replied 1 month ago

This is the existing structure. We have divided n tables. We have the right to adjust the DB in our business. Now we need to find out all the data. When we divide the tables, we split them by another field uid. The application is also based on this uid for retrieval. It seems that we can only do it by @ YJ. Lee’s method, @ Samoa’s method. We have the right to do it, and prepare for the operation and maintenance of the student guide. Come out, thank you!

Senior typist replied 1 month ago

I’ve invited some dada to see if they can help.

10 Answers
Best Answer
ex90rts answered 1 month ago

MySQL has a merge storage engine dedicated to this task. Learn from this blog:MySQL merge engine sub table

Fwolf answered 1 month ago

The number of sub tables will affect which scheme to adopt.
From the database level to solve:

  • MySQL merge engine, @ Samoa answers
  • MySQL table partition is only supported after version 5.1, @ yj.lee replied

From the SQL level:
The standard usage is union, @ YJ. Lee also replied.
There may be a large number of sub tables in the main topic, so the SQL method may not be suitable, so the solution at the database level can be considered. It can be solved from the application layer by multiple queries and merging results, or by building a simple redundant table / cache based on type, or by using any method but caching query results, etc.
All of the above are sub table situations. If it is sub database, the database level method is basically not feasible and can only be solved from the application.

Yj_Lee answered 1 month ago
(SELECT xx FROM table1 WHERE type=1) union
(SELECT xx FROM table1 WHERE type=1) union
(SELECT xx FROM table1 WHERE type=1) union
(SELECT xx FROM table1 WHERE type=1) 

If you need a second extraction

  • Or write your own program to extract it from the union result set above.
  • Or subquery
SELECT xxx FROM (
    (SELECT xx FROM table1 WHERE type=1) union
    (SELECT xx FROM table1 WHERE type=1) union
    (SELECT xx FROM table1 WHERE type=1) union
    (SELECT xx FROM table1 WHERE type=1) 
) t WHERE t.xxx....

However, you don’t need to look at those I wrote above. Now some general programs use the sub table because MySQL 5.1 does not support partition. At least, what’s appropriate for your scenario ispartition, instead of the sub table.

mcfog answered 1 month ago

If the amount of data can’t be disassembled, it can be disassembled directly. After MySQL is disassembled, it will be regarded as the persistence layer of key value. Do not want to cross tables and libraries. (except for the demand of statistics / data warehouse, a query can be done for a long time at no cost to block the whole server.)
Carefully choose the basis for splitting. For example, the type is very balanced, and the application scenario always has a known type. You may as well split by type directly.
Secondly, if there is a large amount of data, and there are different retrieval requirements from the split latitude, what you need is a search service, or index service. I have no practical experience in this field, so I can only give a string of keywords Lucene Solr Sphinx elasticsearch

zwan0518 answered 1 month ago

In this case, the table should be stored according to the route, but if you want to query a table that is not by the route key, you need to query the whole database.