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:
列印頁面
上一頁
下一頁
廣告