MySQL统计数据库表和字段数量

• 查看MySQL数据库中某一数据库中 \’weijing\’ 所有表的信息

mysql> select * from information_schema.tables where table_schema=\'weijing\'\\G;
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: weijing
TABLE_NAME: alarm_contacts
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Compact
TABLE_ROWS: 0
AVG_ROW_LENGTH: 0
DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 16384
DATA_FREE: 0
AUTO_INCREMENT: 1
CREATE_TIME: 2020-04-24 18:04:00
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
*************************** 2. row ***************************

• 统计MySQL数据库中weijing共计有多少表

mysql> select count(*) from information_schema.tables where table_schema=\'weijing\';
+----------+
| count(*) |
+----------+
| 21 |
+----------+
1 row in set (0.01 sec)

• 查看数据库“weijing”中所有字段信息

mysql> select * from information_schema.columns where table_schema=\'weijing\'\\G;
*************************** 198. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: weijing
TABLE_NAME: users
COLUMN_NAME: deleted_by_id
ORDINAL_POSITION: 15
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: MUL
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
*************************** 199. row ***************************

统计“weijing”库中有多少字段

mysql> select count(*) from information_schema.columns where table_schema=\'weijing\';
+----------+
| count(*) |
+----------+
| 199 |
+----------+
1 row in set (0.00 sec)