After MySQL table, how to query all the records in the table?

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

Reply content:

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

MySQL has a merge storage engine dedicated to doing this, and this blog post understands: MySQL Merge engine sub-table

If you need to extract two times

• or write your own program and extract it from the union result set above.

• or sub-query

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, I wrote above those you can not see, now some common applications use the sub-table is because MySQL 5.1 does not support partition before, at least you this scenario is appropriate partition, and non-table.

The number of tables affects which scenario is used.

From the database level to address:

• Mysql MERGE engine, @samoay answered.
• Mysql table partition, supported after version 5.1, @Yj. Lee answered.

From the SQL level to address:

The standard usage is union, @Yj. Lee also answered.

The number of sub-tables may be more, the SQL method may not be very suitable, you can consider the database-level solution. or from the application layer to solve, multiple queries, flattening the results, or by the type of a simple redundant table/cache query, or take any method but cache query results and so on.

The above is the case of the sub-table, if it is a sub-library, the database level approach is basically not possible, only from the application to solve.

If the amount of data is not removed, then directly removed. When MySQL is torn down, think of it as a persistent layer of key value, not cross-library across tables. (unless it's a statistical/Data warehouse requirement, a query can take a long time to stop killing the entire server.)

Choose the split basis carefully, such as the type is very balanced, the application scenario and basically always known type, you may want to directly by the type of split.
Second, if there is a large amount of data, and there are different search requirements to split the latitude, then you need a search service, or Indexing Service. I have no practical experience in this area, I can only give a string of keywords Lucene solr sphinx elasticsearch

This should be in accordance with the route to store the table, but if you want to query a not by the route key, you need to query the whole library

Visually you need a partition instead of a table.

The best way to do this is with the merge engine, which is suitable for most scenarios, depending on the amount of data.

MySQL deployment, in turn, refer to:

Partition
Sub-table
Sharding

However, the MySQL Tens table index is designed properly and queries are fast. Do not know why to divide the table.

Let's build a view.

It is best to have middleware support for the sub-table architecture to block these details.

If it is a sub-database table, there is a mature plan not?

