The information_schema database in MySQL

information_schema database

Foreword:

The information_schema database, like performance_schema, is an information database that comes with MySQL.

  • among them performance_schema For performance analysis
  • and information_schema Used to store database metadata (data about data), such as database name, table name, column data type, access permissions, etc.

Introduction to information_schema:

  1. In MySQL, regard information_schema as a database, to be preciseInformation database. Which saves the maintenance of the MySQL serverInformation from all other databases. Such as database name, database table, data type and access authority of table column, etc.
  2. In INFORMATION_SCHEMA, there are several read-only tables. They are actuallyview, Instead of the basic table, so you will not be able to see any files related to it.

Commonly used tables in the information_schema library

Table Name:

MariaDB [(none)]> use information_schema;
Database changed
MariaDB [information_schema]> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| ALL_PLUGINS                           |
| APPLICABLE_ROLES                      |
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| COLUMN_PRIVILEGES                     |
| ENABLED_ROLES                         |
| ENGINES                               |
| EVENTS                                |
| FILES                                 |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_CACHES                            |
| KEY_COLUMN_USAGE                      |
| PARAMETERS                            |
| PARTITIONS                            |
| PLUGINS                               |
| PROCESSLIST                           |
| PROFILING                             |
| REFERENTIAL_CONSTRAINTS               |
| ROUTINES                              |
| SCHEMATA                              |
| SCHEMA_PRIVILEGES                     |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| SYSTEM_VARIABLES                      |
| TABLES                                |
| TABLESPACES                           |
| TABLE_CONSTRAINTS                     |
| TABLE_PRIVILEGES                      |
| TRIGGERS                              |
| USER_PRIVILEGES                       |
| VIEWS                                 |
| GEOMETRY_COLUMNS                      |
| SPATIAL_REF_SYS                       |
| CLIENT_STATISTICS                     |
| INDEX_STATISTICS                      |
| INNODB_SYS_DATAFILES                  |
| USER_STATISTICS                       |
| INNODB_SYS_TABLESTATS                 |
| INNODB_LOCKS                          |
| INNODB_MUTEXES                        |
| INNODB_CMPMEM                         |
| INNODB_CMP_PER_INDEX                  |
| INNODB_CMP                            |
| INNODB_FT_DELETED                     |
| INNODB_CMP_RESET                      |
| INNODB_LOCK_WAITS                     |
| TABLE_STATISTICS                      |
| INNODB_TABLESPACES_ENCRYPTION         |
| INNODB_BUFFER_PAGE_LRU                |
| INNODB_SYS_FIELDS                     |
| INNODB_CMPMEM_RESET                   |
| INNODB_SYS_COLUMNS                    |
| INNODB_FT_INDEX_TABLE                 |
| INNODB_CMP_PER_INDEX_RESET            |
| user_variables                        |
| INNODB_FT_INDEX_CACHE                 |
| INNODB_SYS_FOREIGN_COLS               |
| INNODB_FT_BEING_DELETED               |
| INNODB_BUFFER_POOL_STATS              |
| INNODB_TRX                            |
| INNODB_SYS_FOREIGN                    |
| INNODB_SYS_TABLES                     |
| INNODB_FT_DEFAULT_STOPWORD            |
| INNODB_FT_CONFIG                      |
| INNODB_BUFFER_PAGE                    |
| INNODB_SYS_TABLESPACES                |
| INNODB_METRICS                        |
| INNODB_SYS_INDEXES                    |
| INNODB_SYS_VIRTUAL                    |
| INNODB_TABLESPACES_SCRUBBING          |
| INNODB_SYS_SEMAPHORE_WAITS            |
+---------------------------------------+
75 rows in set (0.000 sec)

MariaDB [information_schema]>

Introduction:

  • SCHEMATA table:
    provides the current mysql instanceInformation of all databases. This table is the result of show databases.
  • TABLES table:
    provides information about theTable information (including views). It describes in detail which schema a table belongs to, table type, table engine, creation time and other information. This table is the result of show tables from schemaname.
  • COLUMNS table:
    providedColumn information in the table. It describes in detail all the columns of a table and the information of each column. This table is the result of show columns from schemaname.tablename.
  • STATISTICS table:
    provides information aboutTable indexInformation. This table is the result of show index from schemaname.tablename.
  • USER_PRIVILEGES (User Privileges) table:
    gives aboutFull accessInformation. This information comes from the mysql.user authorization table. Is a non-standard table.
  • SCHEMA_PRIVILEGES (program permissions) table:
    gives aboutProgram (database) permissionsInformation. This information comes from the mysql.db authorization table. Is a non-standard table.
  • TABLE_PRIVILEGES (table privileges) table:
    gives aboutTable permissionsInformation. This information comes from the mysql.tables_priv authorization table. Is a non-standard table.
  • COLUMN_PRIVILEGES (Column Privileges) table:
    gives aboutColumn permissionsInformation. This information comes from the mysql.columns_priv authorization table. Is a non-standard table.
  • CHARACTER_SETS (character set) table:
    provides a mysql instanceAvailable character setInformation. This table is taken from the SHOW CHARACTER SET result set.
  • COLLATIONS table:
    provides information aboutComparison information of each character set。
  • COLLATION_CHARACTER_SET_APPLICABILITY table:
    indicates that it can be usedCharacter set for proofreading. These columns are equivalent to the first two display fields of SHOW COLLATION.
  • TABLE_CONSTRAINTS table:
    describedThere are constrained tables. And the constraint type of the table。
  • KEY_COLUMN_USAGE table:
    describedKey column with constraints。
  • ROUTINES table:
    provides information aboutStore information about subroutines (stored procedures and functions). At this time, the ROUTINES table does not contain custom functions (UDF). The column named "mysql.proc name" indicates the mysql.proc table column for the INFORMATION_SCHEMA.ROUTINES table.
  • VIEWS table:
    gives information about theView information. You need to have the show views permission, otherwise you cannot view the view information.
  • TRIGGERS table:
    provides information aboutTrigger information. Must have super permission to view the table

Classic Case:

1. View each table in the database, the size of the space occupied by the data and the number of rows

DATA_LENGTH + INDEX_LENGTH ==>Data length + index length

SELECT TABLE_NAME,DATA_LENGTH+INDEX_LENGTH,TABLE_ROWS,CONCAT(ROUND((DATA_LENGTH+INDEX_LENGTH)/1024/1024,2), 'MB') AS DATA
 
FROM information_schema.tables WHERE TABLE_SCHEMA='jtdb' ORDER BY DATA_LENGTH+INDEX_LENGTH DESC;
Insert picture description here

2. View the number of fields in each table in the database

SELECT TABLE_NAME,COUNT(TABLE_NAME) FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA='db20' 
GROUP BY TABLE_NAME;
Insert picture description here

3. View the number of data items in each table of the database

  • TABLE_ROWS
SELECT TABLE_NAME,TABLE_ROWS FROM information_schema.tables WHERE TABLE_SCHEMA='jtdb';
Insert picture description here

Introduction to commonly used tables

SCHEMATA table

  • Information about all databases in the current mysql instance.

The SHOW DATABASES; command gets data from this table.

mysql> SELECT * FROM SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | sys                | utf8                       | utf8_general_ci        | NULL     |
| def          | test               | utf8                       | utf8_unicode_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
10 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
10 rows in set (0.00 sec)

TABLES table

  • Stored in the databaseAll table information (including views), Including which database the table belongs to, table type, storage engine, creation time and other information.

SELECT * FROM TABLES;

Insert picture description here

The SHOW TABLES FROM XX; command obtains table information from this database.

Insert picture description here

COLUMNS table

  • storageColumn information in all databases, all tables,, Including how many columns the table has, the type of each column, etc.

SELECT * FROM COLUMNS query column information in all tables

The SHOW COLUMNS FROM schemaname.tablename command gets the results from this table.

SELECT * FROM COLUMNS WHERE TABLE_SCHEMA ='db10'
Insert picture description here
SHOW COLUMNS FROM db10.emp
Insert picture description here

View the number of fields in each table in the database

SELECT TABLE_NAME,COUNT(TABLE_NAME) FROM information_schema.COLUMNS 
WHERE TABLE_SCHEMA='db20' 
GROUP BY TABLE_NAME;
Insert picture description here

STATISTICS table

  • Table index information

The SHOW INDEX FROM schemaname.tablename; command gets the results from this table.

SHOW INDEX FROM jtdb.tb_user
Insert picture description here

CHARACTER_SETS 表

  • Provides information about the character sets available for mysql.

The SHOW CHARACTER SET; command gets the results from this table.

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
...
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
+----------+---------------------------------+---------------------+--------+
41 rows in set (0.07 sec)

mysql> SELECT * FROM CHARACTER_SETS;
+--------------------+----------------------+---------------------------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION                     | MAXLEN |
+--------------------+----------------------+---------------------------------+--------+
| big5               | big5_chinese_ci      | Big5 Traditional Chinese        |      2 |
| dec8               | dec8_swedish_ci      | DEC West European               |      1 |
| cp850              | cp850_general_ci     | DOS West European               |      1 |
...
| eucjpms            | eucjpms_japanese_ci  | UJIS for Windows Japanese       |      3 |
| gb18030            | gb18030_chinese_ci   | China National Standard GB18030 |      4 |
+--------------------+----------------------+---------------------------------+--------+
41 rows in set (0.00 sec)

USER_PRIVILEGES 表

  • User permission table. The content comes from the mysql.user authorization table. Is a non-standard table.
mysql> SELECT * FROM USER_PRIVILEGES;
+-------------------------+---------------+-------------------------+--------------+
| GRANTEE                 | TABLE_CATALOG | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-------------------------+---------------+-------------------------+--------------+
| 'mysql.sys'@'localhost' | def           | USAGE                   | NO           |
| 'root'@'%'              | def           | SELECT                  | YES          |
| 'root'@'%'              | def           | INSERT                  | YES          |
| 'root'@'%'              | def           | UPDATE                  | YES          |
| 'root'@'%'              | def           | DELETE                  | YES          |
| 'root'@'%'              | def           | CREATE                  | YES          |
| 'root'@'%'              | def           | DROP                    | YES          |
| 'root'@'%'              | def           | RELOAD                  | YES          |
| 'root'@'%'              | def           | SHUTDOWN                | YES          |
| 'root'@'%'              | def           | PROCESS                 | YES          |
| 'root'@'%'              | def           | FILE                    | YES          |
| 'root'@'%'              | def           | REFERENCES              | YES          |
| 'root'@'%'              | def           | INDEX                   | YES          |
| 'root'@'%'              | def           | ALTER                   | YES          |
| 'root'@'%'              | def           | SHOW DATABASES          | YES          |
| 'root'@'%'              | def           | SUPER                   | YES          |
| 'root'@'%'              | def           | CREATE TEMPORARY TABLES | YES          |
| 'root'@'%'              | def           | LOCK TABLES             | YES          |
| 'root'@'%'              | def           | EXECUTE                 | YES          |
| 'root'@'%'              | def           | REPLICATION SLAVE       | YES          |
| 'root'@'%'              | def           | REPLICATION CLIENT      | YES          |
| 'root'@'%'              | def           | CREATE VIEW             | YES          |
| 'root'@'%'              | def           | SHOW VIEW               | YES          |
| 'root'@'%'              | def           | CREATE ROUTINE          | YES          |
| 'root'@'%'              | def           | ALTER ROUTINE           | YES          |
| 'root'@'%'              | def           | CREATE USER             | YES          |
| 'root'@'%'              | def           | EVENT                   | YES          |
| 'root'@'%'              | def           | TRIGGER                 | YES          |
| 'root'@'%'              | def           | CREATE TABLESPACE       | YES          |
+-------------------------+---------------+-------------------------+--------------+
29 rows in set (0.00 sec)

SCHEMA_PRIVILEGES table

  • Scheme permission table. Gives information about the permissions of the program (database). The content comes from the mysql.db authorization table. Is a non-standard table.
mysql> SELECT * FROM SCHEMA_PRIVILEGES;
+-------------------------+---------------+--------------+-------------------------+--------------+
| GRANTEE                 | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE          | IS_GRANTABLE |
+-------------------------+---------------+--------------+-------------------------+--------------+
| 'mysql.sys'@'localhost' | def           | sys          | TRIGGER                 | NO           |
| 'root'@'%'              | def           | mysql        | SELECT                  | YES          |
| 'root'@'%'              | def           | mysql        | INSERT                  | YES          |
| 'root'@'%'              | def           | mysql        | UPDATE                  | YES          |
| 'root'@'%'              | def           | mysql        | DELETE                  | YES          |
| 'root'@'%'              | def           | mysql        | CREATE                  | YES          |
| 'root'@'%'              | def           | mysql        | DROP                    | YES          |
| 'root'@'%'              | def           | mysql        | REFERENCES              | YES          |
| 'root'@'%'              | def           | mysql        | INDEX                   | YES          |
| 'root'@'%'              | def           | mysql        | ALTER                   | YES          |
| 'root'@'%'              | def           | mysql        | CREATE TEMPORARY TABLES | YES          |
| 'root'@'%'              | def           | mysql        | LOCK TABLES             | YES          |
| 'root'@'%'              | def           | mysql        | EXECUTE                 | YES          |
| 'root'@'%'              | def           | mysql        | CREATE VIEW             | YES          |
| 'root'@'%'              | def           | mysql        | SHOW VIEW               | YES          |
| 'root'@'%'              | def           | mysql        | CREATE ROUTINE          | YES          |
| 'root'@'%'              | def           | mysql        | ALTER ROUTINE           | YES          |
| 'root'@'%'              | def           | mysql        | EVENT                   | YES          |
| 'root'@'%'              | def           | mysql        | TRIGGER                 | YES          |
+-------------------------+---------------+--------------+-------------------------+--------------+
19 rows in set (0.00 sec)

TABLE_PRIVILEGES table

  • Table permission table. Gives information about table permissions. The content is derived from the mysql.tables_priv authorization table. Is a non-standard table.
mysql> SELECT * FROM TABLE_PRIVILEGES;
+-------------------------+---------------+--------------+------------+----------------+--------------+
| GRANTEE                 | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE |
+-------------------------+---------------+--------------+------------+----------------+--------------+
| 'mysql.sys'@'localhost' | def           | sys          | sys_config | SELECT         | NO           |
+-------------------------+---------------+--------------+------------+----------------+--------------+
1 row in set (0.00 sec)

COLUMN_PRIVILEGES 表

  • Column permission table. Gives information about column permissions. The content comes from the mysql.columns_priv authorization table. Is a non-standard table.
mysql> SELECT * FROM COLUMN_PRIVILEGES;
Empty set (0.00 sec)

COLLATIONS table

  • Provides comparison information about each character set. The SHOW COLLATION; command gets the results from this table.
mysql> SELECT * FROM COLLATIONS;
+--------------------------+--------------------+-----+------------+-------------+---------+
| COLLATION_NAME           | CHARACTER_SET_NAME | ID  | IS_DEFAULT | IS_COMPILED | SORTLEN |
+--------------------------+--------------------+-----+------------+-------------+---------+
| big5_chinese_ci          | big5               |   1 | Yes        | Yes         |       1 |
| big5_bin                 | big5               |  84 |            | Yes         |       1 |
| dec8_swedish_ci          | dec8               |   3 | Yes        | Yes         |       1 |
| dec8_bin                 | dec8               |  69 |            | Yes         |       1 |
...
| gb18030_bin              | gb18030            | 249 |            | Yes         |       1 |
| gb18030_unicode_520_ci   | gb18030            | 250 |            | Yes         |       8 |
+--------------------------+--------------------+-----+------------+-------------+---------+
222 rows in set (0.03 sec)

TABLE_CONSTRAINTS 表

  • Describes tables with constraints. And the constraint type of the table.

SELECT * FROM TABLE_CONSTRAINTS;

KEY_COLUMN_USAGE 表

  • Describes the key columns with constraints.

SELECT * FROM KEY_COLUMN_USAGE;

VIEWS table

  • Gives information about the views in the database. The show views permission is required, otherwise the view information cannot be viewed.

SELECT * FROM VIEWS LIMIT 1\G

TRIGGERS table

  • Provides information about the trigger. You must have super permission to view the table.

SELECT * FROM TRIGGERS