mysql query data from all tables

MySQL is a relational database management system (RDBMS) that is widely used in data storage and development.

When using MySQL, querying data from all tables is a very common operation. This article will introduce two common methods for the convenience of readers.

Method 1: Use SHOW TABLES to query the table name, and then use SELECT * FROM to query the table data

Step 1: Use SHOW TABLES to query the names of all tables

Use SHOW The TABLES command can query the names of all tables in MySQL.

Sample code:

SHOW TABLES;

After executing the above code, the names of all tables in the database will be output.

Second step: Use SELECT * FROM to query table data

After obtaining the names of all tables through the first step, we can use the SELECT * FROM command to query all tables for each table The data.

Sample code:

SELECT * FROM 表名;

Please replace the above "table name" with the name of the table you are querying.

After executing the above code, all data in the table will be output.

Although the above method is straightforward, it requires a separate query for each table. When the number of tables is very large, this method is not realistic.

Then, we can use the second method.

Method 2: Use the information schema to query the data of all tables in MySQL

MySQL has a special database called information_schema, which contains information about all tables in MySQL.

By querying the information_schema database, we can obtain detailed information about all tables.

Sample code:

SELECT TABLE_NAME FROM information_schema.Tables WHERE TABLE_SCHEMA='数据库名称';

Please replace "database name" with the name of the database you are querying.

After executing the above code, the names of all tables in the database will be output.

Next, we can write a loop statement to traverse and output the data of all tables.

Sample code:

SET group_concat_max_len=250000;
SELECT GROUP_CONCAT(
    CONCAT('SELECT * FROM ',
        TABLE_NAME)
    SEPARATOR ' UNION ALL ')
FROM information_schema.Tables
WHERE TABLE_SCHEMA='数据库名称';

Please replace "database name" with the name of the database you are querying.

After executing the above code, a very long SQL statement will be output. Copy this statement to the SQL client or MySQL command line, and then execute it to obtain data from all tables.

It should be noted that the above method is only suitable for querying relatively simple data. If you need to perform complex data query or other operations, please use it with caution.

Conclusion

With the above two methods, it will be very easy to query the data of all tables in MySQL. The second method is more direct and efficient than the first, so information mode queries should be given priority when dealing with large amounts of data.

The above is the detailed content of mysql query data from all tables. For more information, please follow other related articles on the PHP Chinese website!