MySQL - SHOW TABLE STATUS 语句
MySQL SHOW TABLE STATUS 语句
CREATE TABLE 语句用于在 MYSQL 数据库中创建表。这里，您需要指定表的名称以及每列的定义(名称和数据类型)。
MySQL 的 SHOW TABLE STATUS 语句提供有关数据库中非临时表的信息。
语法
以下是 SHOW TABLES 语句的语法 -
SHOW TABLE STATUS
[{FROM | IN} db_name]
[LIKE 'pattern' | WHERE expr]
示例
假设我们使用 CREATE 语句在当前数据库中创建了 4 个表，如下所示 -
CREATE TABLE TestTable1(value VARCHAR(10)); CREATE TABLE TestTable2(value VARCHAR(10)); CREATE TABLE TestTable3(value VARCHAR(10)); CREATE TABLE TestTable4(value VARCHAR(10));
以下语句显示当前数据库中非临时高亮的信息 -
SHOW TABLE STATUS\G;
输出
查询执行后，将产生以下输出 -
*************** 1. row ***************
Name: testtable1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:04:03
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 2. row ***************
Name: testtable2
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:04:08
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 3. row ***************
Name: testtable3
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:04:21
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 4. row ***************
Name: testtable4
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:10:12
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
FROM 或 IN 子句
您可以使用 FROM 子句从特定数据库中检索非临时表的信息。
示例
假设我们使用 CREATE DATABASE 语句创建了一个名为 demo 的数据库 -
CREATE DATABASE demo;
现在，我们使用 CREATE TABLE 语句在其中创建表 -
CREATE TABLE demo.myTable1 (data INT); CREATE TABLE demo.myTable2 (data INT); CREATE TABLE demo.myTable3 (data INT); CREATE TABLE demo.myTable4 (data INT);
以下查询列出了数据库"demo"中表的信息 -
SHOW TABLE STATUS FROM demo\G;
输出
上述查询生成如下所示的输出 -
*************** 1. row ***************
Name: mytable1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:09:34
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 2. row ***************
Name: mytable2
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:09:47
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 3. row ***************
Name: mytable3
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:10:12
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 4. row ***************
Name: mytable4
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:10:12
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
您也可以使用 IN 子句代替 FROM 作为 -
SHOW TABLE STATUS IN demo\G;
输出
以下是上述查询的输出 -
*************** 1. row ***************
Name: mytable1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:09:34
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 2. row ***************
Name: mytable2
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:09:47
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 3. row ***************
Name: mytable3
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:10:00
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 4. row ***************
Name: mytable4
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:10:12
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
LIKE 子句
使用 LIKE 子句，您可以指定一个模式来检索特定表的信息。以下查询检索名称以 "my" 开头的表的描述。
use demo; Database changed SHOW TABLE STATUS LIKE 'my%'\G;
输出
上述查询生成以下输出 -
*************** 1. row ***************
Name: mytable1
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:09:34
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 2. row ***************
Name: mytable2
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:09:47
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 3. row ***************
Name: mytable3
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:10:00
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
*************** 4. row ***************
Name: mytable4
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 0
Avg_row_length: 0
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2021-05-13 20:10:12
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_0900_ai_ci
Checksum: NULL
Create_options:
Comment:
