在MySQL中使用子查询展示表状态

在本文中，我们将介绍如何在MySQL中使用子查询展示表状态。首先，我们需要了解一下什么是子查询。

阅读更多：MySQL 教程

什么是子查询？

子查询是指嵌套在主查询中的一个查询语句，可以在WHERE、HAVING子句以及FROM子句中使用。

例如，我们可以使用以下子查询查询出某个学生的平均成绩：

SELECT AVG(score) FROM scores WHERE student_id = (SELECT id FROM students WHERE name='小明');

在这个查询语句中，子查询 (SELECT id FROM students WHERE name='小明') 返回一个学生的ID，然后用它来查询对应学生的成绩平均值。

MySQL中展示表状态的语法

如果我们想要查看MySQL中某个表的状态，可以使用以下语句：

SHOW TABLE STATUS WHERE Name='表名';

这将返回如下表格：

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
表名	InnoDB	10	Compact	0	0	16384	0	0	2097152	NULL	2021-08-10 10:20:02	2021-08-10 10:20:02	NULL	utf8_general_ci	NULL	row_format=COMPACT

如果我们想要在另一个查询中使用这个结果，可以将SHOW TABLE STATUS作为子查询使用。例如，我们可以使用以下语句查询所有表的状态：

SELECT * FROM information_schema.tables WHERE table_schema = '数据库名' AND table_name IN (SELECT Name FROM information_schema.tables WHERE table_schema = '数据库名' AND table_type = 'BASE TABLE');

这个查询语句将返回所有BASE TABLE类型的表的状态。

MySQL子查询展示表状态的语法

那么，如果我们想要在一个查询中展示表的状态，该怎么实现呢？我们可以使用以下语句：

SELECT 
    t1.Name, 
    (SELECT Engine FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Engine', 
    (SELECT Version FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Version', 
    (SELECT row_format FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Row_format', 
    (SELECT Rows FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Rows', 
    (SELECT Avg_row_length FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Avg_row_length', 
    (SELECT Data_length FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Data_length', 
    (SELECT Max_data_length FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Max_data_length', 
    (SELECT Index_length FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Index_length', 
    (SELECT Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Data_free', 
    (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Auto_increment', 
    (SELECT Create_time FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Create_time', 
    (SELECT Update_time FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Update_time', 
    (SELECT Check_time FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME =t1.Name) AS 'Check_time', 
    (SELECT Collation FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Collation', 
    (SELECT Checksum FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Checksum',
    (SELECT Create_options FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Create_options', 
    (SELECT Comment FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Comment'
FROM information_schema.TABLES t1
WHERE TABLE_SCHEMA = 'test' AND TABLE_TYPE = 'BASE TABLE';

在这个查询语句中，我们将SHOW TABLE STATUS语句的每个字段转换为一个子查询，并将其作为SELECT语句的一部分来获取表的状态。注意，我们使用t1.Name将子查询与主查询连接起来。

示例

假设我们有以下两个表：

CREATE TABLE `orders` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

我们可以使用以下查询语句来展示这些表的状态：

SELECT 
    t1.Name,
    (SELECT Engine FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Engine',
    (SELECT Version FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Version',
    (SELECT row_format FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Row_format',
    (SELECT Rows FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Rows',
    (SELECT Avg_row_length FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Avg_row_length',
    (SELECT Data_length FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Data_length',
    (SELECT Max_data_length FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Max_data_length',
    (SELECT Index_length FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Index_length',
    (SELECT Data_free FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Data_free',
    (SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Auto_increment',
    (SELECT Create_time FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Create_time',
    (SELECT Update_time FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Update_time',
    (SELECT Check_time FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Check_time',
    (SELECT Collation FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Collation',
    (SELECT Checksum FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Checksum',
    (SELECT Create_options FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Create_options',
    (SELECT Comment FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = t1.Name) AS 'Comment'
FROM information_schema.TABLES t1
WHERE TABLE_SCHEMA = 'test' AND TABLE_TYPE = 'BASE TABLE';

查询结果：

Name	Engine	Version	Row_format	Rows	Avg_row_length	Data_length	Max_data_length	Index_length	Data_free	Auto_increment	Create_time	Update_time	Check_time	Collation	Checksum	Create_options	Comment
customers	InnoDB	10	Compact	0	0	16384	0	0	1048576	1	2021-08-10 11:00:50	2021-08-10 11:00:50	NULL	utf8mb4_general_ci	NULL	row_format=COMPACT
orders	InnoDB	10	Compact	0	0	16384	0	0	1048576	1	2021-08-10 11:00:50	2021-08-10 11:00:50	NULL	utf8mb4_general_ci	NULL	row_format=COMPACT