本文共 15745 字,大约阅读时间需要 52 分钟。
information_schema INFORMATION_SCHEMA 是信息数据库,其中保存着关于MySQL服务器所维护的所有其他数据库的信息。 在INFORMATION_SCHEMA中,有数个只读表。它们实际上是视图,而不是基本表, 你将无法看到与之相关的任何文件 不能在其中插入内容,不能更新它们,也不能删除其中的内容 了解某个表中列信息 select column_name from columns where table_schema='INFORMATION_SCHEMA' and table_name='partitions'; 扩展 show - SHOW CHARACTER SET SHOW COLLATIONSHOW COLUMNS SHOW DATABASES SHOW FUNCTION STATUS SHOW KEYS SHOW OPEN TABLES SHOW PROCEDURE STATUS SHOW STATUS SHOW TABLE STATUS SHOW TABLES SHOW VARIABLES
CHARACTER_SETS - root@localhost[information_schema]>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 | 当前数据库支持字符集信息
COLLATIONS - root@localhost[information_schema]>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 | | cp850_general_ci | cp850 | 4 | Yes | Yes | 1 | | cp850_bin | cp850 | 80 | | Yes | 1 | | hp8_english_ci | hp8 | 6 | Yes | Yes | 1 | | hp8_bin | hp8 | 72 | | Yes | 1 | 对应列仲字符定义信息
COLLATION_CHARACTER_SET_APPLICABILITY - root@localhost[information_schema]>select * from COLLATION_CHARACTER_SET_APPLICABILITY; +----------------------+--------------------+ | COLLATION_NAME | CHARACTER_SET_NAME | +----------------------+--------------------+ | big5_chinese_ci | big5 | | big5_bin | big5 | | dec8_swedish_ci | dec8 | | dec8_bin | dec8 | | cp850_general_ci | cp850 | | cp850_bin | cp850 | | hp8_english_ci | hp8 | 列中字符集与默认字符集对应关系
columns - root@localhost[information_schema]>desc columns; +--------------------------+---------------------+------+-----+---------+-------+ | Field | +--------------------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | 总是 null | TABLE_SCHEMA | 数据库, 库名 | TABLE_NAME | 对应数据库中每个表 | COLUMN_NAME | 每个表中的列 | ORDINAL_POSITION | 数据库序号 | COLUMN_DEFAULT | 列中默认值 | IS_NULLABLE | 是否 NULL | DATA_TYPE | 列数据类型 | CHARACTER_MAXIMUM_LENGTH | 列中字符集最大长度 10 进制(只针对字符类型) | CHARACTER_OCTET_LENGTH | 列中字符集最大长度 8 进制(只针对字符类型) | NUMERIC_PRECISION | 列中数字长度(整数部分) | NUMERIC_SCALE | 列中数字长度(浮点数部分) | CHARACTER_SET_NAME | 数据库对应字符列默认字符编码 | COLLATION_NAME | 列中字符列默认字符编码 | COLUMN_TYPE | 列对应数据类型 char varchar int ... | COLUMN_KEY | 列对应索引类型 PRI | EXTRA | 列扩展信息 (on update CURRENT_TIMESTAMP, auto_increment) | PRIVILEGES | 对应列可用权限 | COLUMN_COMMENT | 列注释 +--------------------------+---------------------+------+-----+---------+-------+
COLUMN_PRIVILEGES - 功能未知
engines - 当前数据库所支持引擎类型
EVENTS - EVENT_CATALOG: 总是 NULL EVENT_SCHEMA: 数据库名 EVENT_NAME: 事件名称 DEFINER: 创建者 TIME_ZONE: SYSTEM EVENT_BODY: SQL EVENT_DEFINITION: call pro_del_early() EVENT_TYPE: RECURRING EXECUTE_AT: 什么时候执行 (null 为创建事件执行) INTERVAL_VALUE: 时间间隔 INTERVAL_FIELD: 时间间隔单位 SQL_MODE: STARTS: 2012-02-13 19:27:03 启动时间 ENDS: NULL 结束时间 STATUS: ENABLED 是否可用 ON_COMPLETION: NOT PRESERVE CREATED: 2012-02-13 19:27:03 LAST_ALTERED: 2012-02-13 19:27:03 LAST_EXECUTED: 2012-02-13 19:27:03 EVENT_COMMENT: ORIGINATOR: 0 CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci 1 row in set (0.00 sec)
files - NDB 6.3.27 开始用于记录 NDB 文件分配空间, 可以用于检测剩余空间 | FILE_ID | 自动生成序号 | FILE_NAME | 由 CREATE LOGFILE GROUP 生成的 UNDO log file 与 create tablesapces 生成数据文件 | FILE_TYPE | UNDOFILE or DATAFILE | TABLESPACE_NAME | 表空间 | TABLE_CATALOG | 永远 NULL | TABLE_SCHEMA | | TABLE_NAME | 表名 | LOGFILE_GROUP_NAME | 数据文件或日志文件对应组名 | LOGFILE_GROUP_NUMBER | 自动生成日志组序号 | ENGINE | NDB 或 NDBCLUSTER | FULLTEXT_KEYS | 永远为 NULL | DELETED_ROWS | 删除行数量 | UPDATE_COUNT | 更新总数 | FREE_EXTENTS | 空闲 EXTENT | TOTAL_EXTENTS | 总 EXTENTS | EXTENT_SIZE | EXTENT 大小 | INITIAL_SIZE | 初始化 EXTNET 大小 | MAXIMUM_SIZE | 可用最大 EXTENT 大小 | AUTOEXTEND_SIZE | 自动扩展大小 | CREATION_TIME | 创建时间 | LAST_UPDATE_TIME | | LAST_ACCESS_TIME | | RECOVER_TIME | | TRANSACTION_COUNTER | 事务计算器 | VERSION | 版本 | ROW_FORMAT | | TABLE_ROWS | | AVG_ROW_LENGTH | | DATA_LENGTH | | MAX_DATA_LENGTH | | INDEX_LENGTH | | DATA_FREE | | CREATE_TIME | | UPDATE_TIME | | CHECK_TIME | | CHECKSUM | | STATUS | | EXTRA |
global_status - root@localhost[information_schema]>select * from global_status limit 0,10; +------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------------+----------------+ | ABORTED_CLIENTS | 0 | | ABORTED_CONNECTS | 0 | | BINLOG_CACHE_DISK_USE | 0 | | BINLOG_CACHE_USE | 0 | | BYTES_RECEIVED | 3306 | | BYTES_SENT | 80916 | | COM_ADMIN_COMMANDS | 1 | | COM_ASSIGN_TO_KEYCACHE | 0 | | COM_ALTER_DB | 0 | | COM_ALTER_DB_UPGRADE | 0 | +------------------------+----------------+ 类似 show status
global_variables - root@localhost[information_schema]>select * from global_variables limit 0,10; +-------------------------+----------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------------+----------------------------+ | MAX_PREPARED_STMT_COUNT | 16382 | | CHARACTER_SETS_DIR | /usr/share/mysql/charsets/ | | HAVE_CRYPT | YES | | CONNECT_TIMEOUT | 10 | | MYISAM_REPAIR_THREADS | 1 | | AUTOMATIC_SP_PRIVILEGES | ON | | MAX_BINLOG_SIZE | 1073741824 | | BINLOG_CACHE_SIZE | 32768 | | MAX_JOIN_SIZE | 18446744073709551615 | | BACK_LOG | 50 | +-------------------------+----------------------------+ 类似 show variables
KEY_COLUMN_USAGE - select * from KEY_COLUMN_USAGE where constraint_schema='new' and table_name='t6' \G *************************** 1. row *************************** CONSTRAINT_CATALOG: NULL 永远 NULL CONSTRAINT_SCHEMA: new 约束对应库 CONSTRAINT_NAME: t6_id 约束命名 TABLE_CATALOG: NULL 永远 NULL TABLE_SCHEMA: new 对应库 TABLE_NAME: t6 表 COLUMN_NAME: id 对应列 ORDINAL_POSITION: 1 默认序号 POSITION_IN_UNIQUE_CONSTRAINT: NULL 是否具有唯一性 REFERENCED_TABLE_SCHEMA: NULL 是否涉及外键库 REFERENCED_TABLE_NAME: NULL 是否涉及外键表 REFERENCED_COLUMN_NAME: NULL 是否涉及外键列
partition - +-------------------------------+ | TABLE_CATALOG | 永远 NULL | TABLE_SCHEMA | 对应库 | TABLE_NAME | 对应表 | PARTITION_NAME | 分区命名 | SUBPARTITION_NAME | 子分区 | PARTITION_ORDINAL_POSITION | | SUBPARTITION_ORDINAL_POSITION | | PARTITION_METHOD | | SUBPARTITION_METHOD | | PARTITION_EXPRESSION | | SUBPARTITION_EXPRESSION | | PARTITION_DESCRIPTION | | TABLE_ROWS | 表对应行数量 | AVG_ROW_LENGTH | 平均行长 | DATA_LENGTH | 数据行长 | MAX_DATA_LENGTH | 最大数据行长 | INDEX_LENGTH | 索引行长 | DATA_FREE | | CREATE_TIME | | UPDATE_TIME | | CHECK_TIME | | CHECKSUM | | PARTITION_COMMENT | | NODEGROUP | | TABLESPACE_NAME | 对应表空间 +-------------------------------+
plugins - 支持引擎信息 PLUGIN_NAME: MyISAM PLUGIN_VERSION: 1.0 PLUGIN_STATUS: ACTIVE PLUGIN_TYPE: STORAGE ENGINE PLUGIN_TYPE_VERSION: 50152.0 PLUGIN_LIBRARY: NULL PLUGIN_LIBRARY_VERSION: NULL PLUGIN_AUTHOR: MySQL AB PLUGIN_DESCRIPTION: Default engine as of MySQL 3.23 with great performance PLUGIN_LICENSE: GPL
processlist - root@localhost[information_schema]>select * from processlist; +----+------+-----------+--------------------+---------+------+-----------+---------------------------+ | ID | USER | HOST | DB | COMMAND | TIME | STATE | INFO | +----+------+-----------+--------------------+---------+------+-----------+---------------------------+ | 6 | root | localhost | information_schema | Query | 0 | executing | select * from processlist | +----+------+-----------+--------------------+---------+------+-----------+---------------------------+ 当前数据库连接状态信息
profiling - select column_name from columns where table_schema='INFORMATION_SCHEMA' and table_name='profiling'; 只能够显示用户使用 SQL 信息资源 必须设定 profiling = 0 禁止 profiling_history_size = 100 +---------------------+ | QUERY_ID | | SEQ | | STATE | | DURATION | | CPU_USER | | CPU_SYSTEM | | CONTEXT_VOLUNTARY | | CONTEXT_INVOLUNTARY | | BLOCK_OPS_IN | | BLOCK_OPS_OUT | | MESSAGES_SENT | | MESSAGES_RECEIVED | | PAGE_FAULTS_MAJOR | | PAGE_FAULTS_MINOR | | SWAPS | | SOURCE_FUNCTION | | SOURCE_FILE | | SOURCE_LINE | +---------------------+
show profile - 由 MySQL 5.0.37 开始支持 my.cnf profiling = 0 禁止 profiling_history_size = 100 (最大值) 15 默认, 记录 profile 信息记录 (不提供Enterprise Server users) SHOW PROFILE [type [, type] ... ] [FOR QUERY n] [LIMIT row_count [OFFSET offset]] type: ALL | BLOCK IO | CONTEXT SWITCHES | CPU | IPC | MEMORY | PAGE FAULTS | SOURCE | SWAPS
REFERENTIAL_CONSTRAINTS - +---------------------------+ | CONSTRAINT_CATALOG | 永远 NULL | CONSTRAINT_SCHEMA | 对应外键库 | CONSTRAINT_NAME | 对应外键表 | UNIQUE_CONSTRAINT_CATALOG | 永远 NULL | UNIQUE_CONSTRAINT_SCHEMA | 对应外键库 | UNIQUE_CONSTRAINT_NAME | 对应外键表 | MATCH_OPTION | 永远 NONE | UPDATE_RULE | | DELETE_RULE | | TABLE_NAME | | REFERENCED_TABLE_NAME | +---------------------------+
ROUTINES - select * from ROUTINES where routine_name='test9' \G 对应存储过程, 函数信息 *************************** 1. row *************************** SPECIFIC_NAME: test9 ROUTINE_CATALOG: NULL ROUTINE_SCHEMA: new ROUTINE_NAME: test9 ROUTINE_TYPE: PROCEDURE DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: label_1: begin label_2: while 0 = 1 do leave label_2; end while label_2; label_3: repeat leave label_3; until 0 =0 end repeat label_3 ; label_4: loop leave label_4; end loop label_4 ; end label_1 EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: NULL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: CONTAINS SQL SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2012-02-06 13:03:28 LAST_ALTERED: 2012-02-06 13:03:28 SQL_MODE: ROUTINE_COMMENT: DEFINER: tt@localhost CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci
schemata - root@localhost[information_schema]>select * from schemata; +--------------+--------------------+----------------------------+------------------------+----------+ | CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | +--------------+--------------------+----------------------------+------------------------+----------+ | NULL | information_schema | utf8 | utf8_general_ci | NULL | | NULL | class | latin1 | latin1_swedish_ci | NULL | | NULL | mysql | latin1 | latin1_swedish_ci | NULL | | NULL | new | latin1 | latin1_swedish_ci | NULL | 注释:SQL_PATH列总是 NULL schema_name -> 库名 DEFAULT_CHARACTER_SET_NAME -> 字符集 (创建表时候字符集) DEFAULT_COLLATION_NAME -> 字符集 ( 对应列默认字符集)
SCHEMA_PRIVILEGES - root@localhost[information_schema]>select * from SCHEMA_PRIVILEGES; +------------------+---------------+--------------+-------------------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | PRIVILEGE_TYPE | IS_GRANTABLE | +------------------+---------------+--------------+-------------------------+--------------+ | 'tt'@'localhost' | NULL | class | SELECT | NO | | 'tt'@'localhost' | NULL | class | INSERT | NO | | 'tt'@'localhost' | NULL | class | UPDATE | NO | | 'tt'@'localhost' | NULL | class | DELETE | NO | | 'tt'@'localhost' | NULL | class | CREATE | NO | | 'tt'@'localhost' | NULL | class | DROP | NO | 用于对应某个表响应的权限
session_status - select * from session_status limit 0,10; +------------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------------+----------------+ | ABORTED_CLIENTS | 0 | | ABORTED_CONNECTS | 0 | | BINLOG_CACHE_DISK_USE | 0 | | BINLOG_CACHE_USE | 0 | | BYTES_RECEIVED | 4435 | | BYTES_SENT | 114658 | | COM_ADMIN_COMMANDS | 0 | | COM_ASSIGN_TO_KEYCACHE | 0 | | COM_ALTER_DB | 0 | | COM_ALTER_DB_UPGRADE | 0 | +------------------------+----------------+ session 当前状态信息
session_variables - select * from session_variables limit 0,10; +-------------------------+----------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------------+----------------------------+ | MAX_PREPARED_STMT_COUNT | 16382 | | CHARACTER_SETS_DIR | /usr/share/mysql/charsets/ | | HAVE_CRYPT | YES | | CONNECT_TIMEOUT | 10 | | MYISAM_REPAIR_THREADS | 1 | | AUTOMATIC_SP_PRIVILEGES | ON | | MAX_BINLOG_SIZE | 1073741824 | | BINLOG_CACHE_SIZE | 32768 | | MAX_JOIN_SIZE | 18446744073709551615 | | BACK_LOG | 50 | +-------------------------+----------------------------+ 用户 session 当前参数
statistics - select * from statistics where table_schema='new'; TABLE_CATALOG: NULL TABLE_SCHEMA: new TABLE_NAME: t6 NON_UNIQUE: 0 INDEX_SCHEMA: new INDEX_NAME: t6_id SEQ_IN_INDEX: 1 COLUMN_NAME: id COLLATION: A CARDINALITY: 0 SUB_PART: NULL PACKED: NULL NULLABLE: INDEX_TYPE: BTREE COMMENT: 类似 show index 功能, 显示索引信息
table_constraints - select * from table_constraints where CONSTRAINT_SCHEMA='new'; +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE | +--------------------+-------------------+-----------------+--------------+------------+-----------------+ | NULL | new | PRIMARY | new | error_log | PRIMARY KEY | | NULL | new | id_idx | new | t3 | UNIQUE | | NULL | new | PRIMARY | new | t5 | PRIMARY KEY | | NULL | new | t6_id | new | t6 | UNIQUE | +--------------------+-------------------+-----------------+--------------+------------+-----------------+ 表对应索引信息
TABLE_PRIVILEGES - root@localhost[information_schema]>select * from TABLE_PRIVILEGES; +------------------+---------------+--------------+------------+----------------+--------------+ | GRANTEE | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | PRIVILEGE_TYPE | IS_GRANTABLE | +------------------+---------------+--------------+------------+----------------+--------------+ | 'aa'@'localhost' | NULL | new | a1 | SELECT | YES | | 'aa'@'localhost' | NULL | new | a1 | INSERT | YES | | 'aa'@'localhost' | NULL | new | a1 | UPDATE | YES | | 'a1'@'%' | NULL | new | a1 | SELECT | YES | | 'a1'@'%' | NULL | new | a1 | INSERT | YES | | 'a1'@'%' | NULL | new | a1 | UPDATE | YES | +------------------+---------------+--------------+------------+----------------+--------------+ 具有 GRANTEE 功能的用户
TRIGGERS - select * from TRIGGERS TRIGGER_CATALOG: NULL TRIGGER_SCHEMA: new TRIGGER_NAME: tr_sum_d EVENT_MANIPULATION: DELETE EVENT_OBJECT_CATALOG: NULL EVENT_OBJECT_SCHEMA: new EVENT_OBJECT_TABLE: tr2 ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: begin declare num int; select sum(id) into num from tr2; update tr_count set id=num; end ACTION_ORIENTATION: ROW ACTION_TIMING: AFTER ACTION_REFERENCE_OLD_TABLE: NULL ACTION_REFERENCE_NEW_TABLE: NULL ACTION_REFERENCE_OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: NULL SQL_MODE: DEFINER: tt@localhost CHARACTER_SET_CLIENT: latin1 COLLATION_CONNECTION: latin1_swedish_ci DATABASE_COLLATION: latin1_swedish_ci
USER_PRIVILEGES - root@localhost[information_schema]>select * from USER_PRIVILEGES; +--------------------------------+---------------+-------------------------+--------------+ | GRANTEE | TABLE_CATALOG | PRIVILEGE_TYPE | IS_GRANTABLE | +--------------------------------+---------------+-------------------------+--------------+ | 'root'@'localhost' | NULL | SELECT | YES | | 'root'@'localhost' | NULL | INSERT | YES | | 'root'@'localhost' | NULL | UPDATE | YES | | 'tt'@'%' | NULL | SUPER | NO | | 'tt'@'%' | NULL | EXECUTE | NO | | 'tt'@'%' | NULL | CREATE ROUTINE | NO | | 'tt'@'%' | NULL | ALTER ROUTINE | NO | | 'a1'@'%' | NULL | CREATE | YES | 所有系统中用户的系统权限
VIEWS - +----------------------+ | TABLE_CATALOG | | TABLE_SCHEMA | | TABLE_NAME | | VIEW_DEFINITION | | CHECK_OPTION | | IS_UPDATABLE | | DEFINER | | SECURITY_TYPE | | CHARACTER_SET_CLIENT | | COLLATION_CONNECTION | +----------------------+ 对应视图
转载地址:http://xqnni.baihongyu.com/