INFORMATION_SCHEMA
INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges
[root@localhost][information_schema]> describe tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG | varchar(512) | NO | | | |
| TABLE_SCHEMA | varchar(64) | NO | | | |
| TABLE_NAME | varchar(64) | NO | | | |
| TABLE_TYPE | varchar(64) | NO | | | |
| ENGINE | varchar(64) | YES | | NULL | |
| VERSION | bigint(21) unsigned | YES | | NULL | |
| ROW_FORMAT | varchar(10) | YES | | NULL | |
| TABLE_ROWS | bigint(21) unsigned | YES | | NULL | |
| AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | |
| INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | |
| DATA_FREE | bigint(21) unsigned | YES | | NULL | |
| AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | |
| CREATE_TIME | datetime | YES | | NULL | |
| UPDATE_TIME | datetime | YES | | NULL | |
| CHECK_TIME | datetime | YES | | NULL | |
| TABLE_COLLATION | varchar(32) | YES | | NULL | |
| CHECKSUM | bigint(21) unsigned | YES | | NULL | |
| CREATE_OPTIONS | varchar(255) | YES | | NULL | |
| TABLE_COMMENT | varchar(2048) | NO | | | |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.01 sec)
[root@localhost][information_schema]> select * from TABLES\G
....
....
*************************** 1072. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: yunt
TABLE_NAME: tb_ticket
TABLE_TYPE: BASE TABLE
ENGINE: MyISAM
VERSION: 10
ROW_FORMAT: Fixed
TABLE_ROWS: 956
AVG_ROW_LENGTH: 20
DATA_LENGTH: 19120
MAX_DATA_LENGTH: 5629499534213119
INDEX_LENGTH: 38912
DATA_FREE: 0
AUTO_INCREMENT: 984
CREATE_TIME: 2018-05-16 12:24:48
UPDATE_TIME: 2018-05-16 12:24:48
CHECK_TIME: 2018-05-16 12:24:48
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1072 rows in set, 7 warnings (0.22 sec)
[root@localhost][information_schema]>
===> 各個資料庫的 table 有異動時，這裡就會同步更新
TABLES 這個表格的格式是 MEMORY
需要讀取某 table 的資料總筆數(rows) 可以從這裡讀取 (尤其是 Innodb 格式的表格)
以 select count(*) from tableabc 來說
MyIsam or InnoDB 格式差很多，MyIsam 的表格 內有記錄總筆數，
但 InnoDB 則需掃瞄整個 table 後算出總筆數
某 table 的欄位資料:
[root@localhost][information_schema]> select * from COLUMNS\G
....
....
*************************** 10822. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: yuant
TABLE_NAME: tbl_ticket
COLUMN_NAME: ymd
ORDINAL_POSITION: 5
COLUMN_DEFAULT: 0000-00-00
IS_NULLABLE: YES
DATA_TYPE: date
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: date
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
10822 rows in set, 20 warnings (0.85 sec)
一些參考
https://dev.mysql.com/doc/refman/8.0/en/information-schema.html
MySQL 深潛 - 一文詳解 MySQL Data Dictionary
