MySQL - SHOW TABLE STATUS 语句

Table of content
上一页
下一页

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:
打印页面
上一页
下一页
广告
© . All rights reserved.