mysql schemata_SCHEMATA · xiaoboluo768/mysql-system-schema Wiki · GitHub

该表提供查询MySQL Server中的数据库列表信息，一个schema就代表一个database

该表为Memory引擎临时表

表定义语句

CREATE TEMPORARY TABLE `SCHEMATA` (

`CATALOG_NAME` varchar(512) NOT NULL DEFAULT '',

`SCHEMA_NAME` varchar(64) NOT NULL DEFAULT '',

`DEFAULT_CHARACTER_SET_NAME` varchar(32) NOT NULL DEFAULT '',

`DEFAULT_COLLATION_NAME` varchar(32) NOT NULL DEFAULT '',

`SQL_PATH` varchar(512) DEFAULT NULL

) ENGINE=MEMORY DEFAULT CHARSET=utf8

表字段含义

SCHEMA_NAME：数据库名称

DEFAULT_CHARACTER_SET_NAME：数据库的默认字符集名称

DEFAULT_COLLATION_NAME：数据库的默认校对规则名称

SQL_PATH：总是为NULL

表记录内容示例

root@localhost : information_schema 06:35:32> 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 | employees | utf8 | utf8_bin | NULL |

| def | luoxiaobo | utf8 | utf8_bin | NULL |

| def | mysql | utf8 | utf8_bin | NULL |

| def | performance_schema | utf8 | utf8_general_ci | NULL |

| def | qfsys | utf8 | utf8_bin | NULL |

| def | sbtest | utf8 | utf8_bin | NULL |

| def | sys | utf8 | utf8_general_ci | NULL |

| def | test | utf8 | utf8_bin | NULL |

+--------------+--------------------+----------------------------+------------------------+----------+

9 rows in set (0.00 sec)

PS：该表中的信息还可以使用show语句查询

root@localhost : information_schema 06:41:30> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| employees |

| luoxiaobo |

| mysql |

| performance_schema |

| qfsys |

| sbtest |

| sys |

| test |

+--------------------+

9 rows in set (0.00 sec)