在MySQL中,可以使用INFORMATION_SCHEMA
這個特殊的數(shù)據(jù)庫來查詢數(shù)據(jù)庫元數(shù)據(jù)。INFORMATION_SCHEMA
提供了關(guān)于數(shù)據(jù)庫對象(如表、列、索引等)的詳細信息。
以下是一些常用的INFORMATION_SCHEMA
表及其查詢示例:
SELECT SCHEMA_NAME AS 'Database Name', DEFAULT_CHARACTER_SET_NAME AS 'Default Charset', DEFAULT_COLLATION_NAME AS 'Default Collation' FROM INFORMATION_SCHEMA.SCHEMATA;
SELECT TABLE_SCHEMA AS 'Database Name', TABLE_NAME, TABLE_TYPE, ENGINE, TABLE_ROWS, AVG_ROW_SIZE, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, AUTO_INCREMENT, CREATE_TIME, UPDATE_TIME, CHECK_TIME, Collation_NAME, Checksum, Create_Options, Table_Comment FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database_name';
SELECT TABLE_SCHEMA AS 'Database Name', TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA, PRIVILEGES, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
SELECT TABLE_SCHEMA AS 'Database Name', TABLE_NAME, INDEX_NAME, COLUMN_NAME, ORDINAL_POSITION, COLUMN_POSITION, CARDINALITY, SUB_PART, PACKED, NULLABLE, INDEX_TYPE, COMMENT FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'your_database_name' AND TABLE_NAME = 'your_table_name';
注意:在實際使用時,需要將your_database_name
和your_table_name
替換為實際的數(shù)據(jù)庫名和表名。
這些查詢只是INFORMATION_SCHEMA
提供的眾多功能中的一部分。你可以根據(jù)自己的需求組合這些查詢來獲取所需的信息。