MySQL中如何透過一條語句描述資料庫中的所有表格？

您可以使用 INFORMATION_SCHEMA.COLUMNS 透過一條語句來描述資料庫中的所有資料表。語法如下。

SELECT *FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=’yourDatabaseName’\G

這裡我使用兩個表格的資料庫範例。

表格名稱如下 -

• mytable
• yourtable

為您的資料庫實作上述語法。查詢如下 -

mysql> select * FROM information_schema.columns WHERE table_schema = 'sample'\G

以下是描述我們資料庫中兩個表的輸出。

*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sample
TABLE_NAME: mytable
COLUMN_NAME: id
ORDINAL_POSITION: 1
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 10
NUMERIC_SCALE: 0
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: int(11)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
*************************** 2. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sample
TABLE_NAME: mytable
COLUMN_NAME: Name
ORDINAL_POSITION: 2
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 100
CHARACTER_OCTET_LENGTH: 400
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_0900_ai_ci
COLUMN_TYPE: varchar(100)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
*************************** 3. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sample
TABLE_NAME: yourtable
COLUMN_NAME: id
ORDINAL_POSITION: 1
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: int
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: 10
NUMERIC_SCALE: 0
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: int(11)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
*************************** 4. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: sample
TABLE_NAME: yourtable
COLUMN_NAME: YourName
ORDINAL_POSITION: 2
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 100
CHARACTER_OCTET_LENGTH: 400
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_0900_ai_ci
COLUMN_TYPE: varchar(100)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULL
4 rows in set (0.00 sec)

以上是MySQL中如何透過一條語句描述資料庫中的所有表格？的詳細內容。更多資訊請關注PHP中文網其他相關文章！