MySQL Study之–MySQL schema_information数据库
information_schema数据库是在mysql的版本5.0之后产生的，一个虚拟数据库，物理上并不存在。
information_schema数据库类似与“数据字典”，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）。
案例：
mysql> show databases;
+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || prod || test |+--------------------+5 rows in set (0.00 sec)
访问information_schema：
mysql> use information_schema;
Database changed
mysql> show tables;
+---------------------------------------+| Tables_in_information_schema |+---------------------------------------+| CHARACTER_SETS || COLLATIONS || COLLATION_CHARACTER_SET_APPLICABILITY || COLUMNS || COLUMN_PRIVILEGES || ENGINES || EVENTS || FILES || GLOBAL_STATUS || GLOBAL_VARIABLES || KEY_COLUMN_USAGE || OPTIMIZER_TRACE || PARAMETERS || PARTITIONS || PLUGINS || PROCESSLIST || PROFILING || REFERENTIAL_CONSTRAINTS || ROUTINES || SCHEMATA || SCHEMA_PRIVILEGES || SESSION_STATUS || SESSION_VARIABLES || STATISTICS || TABLES || TABLESPACES || TABLE_CONSTRAINTS || TABLE_PRIVILEGES || TRIGGERS || USER_PRIVILEGES || VIEWS || INNODB_LOCKS || INNODB_TRX || INNODB_SYS_DATAFILES || INNODB_LOCK_WAITS || INNODB_SYS_TABLESTATS || INNODB_CMP || INNODB_METRICS || INNODB_CMP_RESET || INNODB_CMP_PER_INDEX || INNODB_CMPMEM_RESET || INNODB_FT_DELETED || INNODB_BUFFER_PAGE_LRU || INNODB_SYS_FOREIGN || INNODB_SYS_COLUMNS || INNODB_SYS_INDEXES <i style="color:transparent">本文来源gaodai$ma#com搞$$代**码)网8</i> || INNODB_FT_DEFAULT_STOPWORD || INNODB_SYS_FIELDS || INNODB_CMP_PER_INDEX_RESET || INNODB_BUFFER_PAGE || INNODB_CMPMEM || INNODB_FT_INDEX_TABLE || INNODB_FT_BEING_DELETED || INNODB_SYS_TABLESPACES || INNODB_FT_INDEX_CACHE || INNODB_SYS_FOREIGN_COLS || INNODB_SYS_TABLES || INNODB_BUFFER_POOL_STATS || INNODB_FT_CONFIG |+---------------------------------------+59 rows in set (0.00 sec)
mysql> desc SCHEMATA;
+----------------------------+--------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------------------------+--------------+------+-----+---------+-------+| CATALOG_NAME | varchar(512) | NO | | | || SCHEMA_NAME | varchar(64) | NO | | | || DEFAULT_CHARACTER_SET_NAME | varchar(32) | NO | | | || DEFAULT_COLLATION_NAME | varchar(32) | NO | | | || SQL_PATH | varchar(512) | YES | | NULL | |+----------------------------+--------------+------+-----+---------+-------+5 rows in set (0.00 sec)
mysql> select SCHEMA_NAME,DEFAULT_CHARACTER_SET_NAME from SCHEMATA;
+--------------------+----------------------------+| SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME |+--------------------+----------------------------+| information_schema | utf8 || mysql | latin1 || performance_schema | utf8 || prod | latin1 || test | latin1 |+--------------------+----------------------------+5 rows in set (0.00 sec)
mysql> desc 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.00 sec)
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：MySQLStudy之–MySQLschema_information数据库_MySQL
转载请注明原文链接：MySQLStudy之–MySQLschema_information数据库_MySQL
