SQL语句之SHOW语句使用
SHOW语句有许多形式，提供关于服务器的数据库、表、列或状态信息的信息。
SHOW语法格式： SHOW 关键字 LIKE 'pattern'
如果对于一个给定的说明语句的语法包括像'模式'，'模式'是一个字符串，可以包含“%”和“_“通配符。该模式是有用的限制语句输出匹配的值。本节介绍以下：
SHOW DATABASES – 显示当前所有数据库的名称
mysql> SHOW DATABASES;
SHOW TABLES – 显示当前数据库中所有表的名称(需要设置默认数据库use DATABASE_NAME)
Mysql> SHOW TABLES;
SHOW TABLES FROM db_name – 显示数据库中的所有表
Mysql> SHOW TABLES FROM db_name;
SHOW ENGINES - 显示MySQL当前支持哪些存储引擎和默认存储引擎
mysql> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.00 sec)
SHOW CHARACTER SET – 显示MySQL当前支持哪些字符集
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 | ------------------------------------------------------------------------- mysql> SHOW CHARACTER SET LIKE '%utf%'; +---------+------------------+--------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+------------------+--------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | | utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 | | utf16 | UTF-16 Unicode | utf16_general_ci | 4 | | utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 | | utf32 | UTF-32 Unicode | utf32_general_ci | 4 | +---------+------------------+--------------------+--------+ 5 rows in set (0.00 sec)
SHOW COLLATION – 显示MySQL支持字符集的排序规则
mysql> SHOW COLLATION; +--------------------------+----------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +--------------------------+----------+-----+---------+----------+---------+ | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | | big5_bin | big5 | 84 | | Yes | 1 |
SHOW BINARY | MASTER – 显示二进制文件以及文件大小（需要开启二进制日志记录功能）
mysql> SHOW BINARY LOGS; mysql> SHOW MASTER LOGS; +------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | mysql-bin.000001 | 143 | | mysql-bin.000002 | 143 | SHOW BINLOG EVENTS – 显示二进制文件的执行过程 #打开显示内容太多了 mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001'; +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ | mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.27-log, Binlog ver: 4 | | mysql-bin.000001 | 120 | Stop | 1 | 143 | | +------------------+-----+-------------+-----------+-------------+---------------------------------------+ 2 rows in set (0.00 sec) mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 120; +------------------+-----+------------+-----------+-------------+------+ | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | +------------------+-----+------------+-----------+-------------+------+ | mysql-bin.000001 | 120 | Stop | 1 | 143 | | +------------------+-----+------------+-----------+-------------+------+ 1 row in set (0.01 sec)
SHOW COLUMNS – 显示表的列信息（等同于DESC，需要先创建表）
mysql> SHOW COLUMNS FROM blog.info; +---------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------------+------+-----+---------+----------------+ | ID | int(11) | NO | PRI | NULL | auto_increment | | NAME | char(8) | NO | | NULL | | | AGE | int(11) | NO | | NULL | | | ADDRESS | varchar(20) | NO | | NULL | | | SALARY | decimal(10,2) | NO | | NULL | | +---------+---------------+------+-----+---------+----------------+ 5 rows in set (0.06 sec)
SHOW CREATE DATABASES – 显示已经创建的库，创建时的语句
mysql> SHOW CREATE DATABASE blog; +----------+-----------------------------------------------------------------+ | Database | Create Database | +----------+-----------------------------------------------------------------+ | testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ | +----------+-----------------------------------------------------------------+ 1 row in set (0.00 sec)
SHOW CREATE TABLE – 显示已经创建的表，创建时的语句
mysql> SHOW CREATE TABLE info\G
*************************** 1. row ***************************
Table: info
Create Table: CREATE TABLE `info` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` char(8) NOT NULL,
`AGE` int(11) NOT NULL,
`ADDRESS` varchar(20) NOT NULL,
`SALARY` decimal(10,2) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
SHOW CREATE FUNTCION – 显示已经创建的函数，创建时的语句
SHOW CREATE PROCEDURE – 显示已经创建的存储过程，创建时的语句
mysql> SHOW CREATE PROCEDURE test.simpleproc\G
*************************** 1. row ***************************
Procedure: simpleproc - 存储过程的名字
sql_mode:
Create Procedure: CREATE PROCEDURE `simpleproc`(OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
SHOW CREATE TRIGGER - 显示已经创建的触发器，创建时的语句
mysql> SHOW CREATE TRIGGER ins_sum\G
*************************** 1. row ***************************
Trigger: ins_sum
sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`me`@`localhost` TRIGGER ins_sum
BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
SHOW CREATE VIEW – 显示已经创建的视图，创建时的语句
mysql> SHOW CREATE VIEW v\G
*************************** 1. row ***************************
View: v
Create View: CREATE ALGORITHM=UNDEFINED
DEFINER=`bob`@`localhost`
SQL SECURITY DEFINER VIEW
`v` AS select 1 AS `a`,2 AS `b`
character_set_client: latin1
collation_connection: latin1_swedish_ci
SHOW CREATE EVENTS – 显示已经创建的事件，创建时的语句
mysql> SHOW CREATE EVENT test.e_daily\G
*************************** 1. row ***************************
Event: e_daily
sql_mode:
time_zone: SYSTEM
Create Event: CREATE EVENT `e_daily`
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR
ON COMPLETION NOT PRESERVE
ENABLE
COMMENT 'Saves total number of sessions then
clears the table each day'
DO BEGIN
INSERT INTO site_activity.totals (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
SHOW ENGINE – 显示存储引擎的详细信息
mysql> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2015-11-22 19:05:05 7f6b6c780700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 61 seconds ..................
SHOW WARNINGS – 显示最后一个执行语句所产生的警告信息
mysql> SHOW WARNINGS\G mysql> SELECT @@warning_count; +-----------------+ | @@warning_count | +-----------------+ | 1 | +-----------------+ 1 row in set (0.01 sec)
SHOW ERRORS – 显示最后一个执行语句所产生的错误信息
mysql> SHOW ERRORS\G
SHOW EVENTS – 显示事件信息
mysql> SHOW EVENTS; Empty set (0.02 sec)
SHOW GRANTS – 显示一个用户所拥有的权限
mysql> SHOW GRANTS; mysql> SHOW GRANTS FOR CURRENT_USER(); mysql> SHOW GRANTS FOR 'root'@'localhost'; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+
SHOW PROCESSLIST – 显示系统中正在运行的所有进程，普通用户只能查看自己的进行信息
mysql> SHOW PROCESSLIST; +----+------+-----------+------+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+-------+------------------+ | 6 | root | localhost | blog | Query | 0 | init | SHOW PROCESSLIST | +----+------+-----------+------+---------+------+-------+------------------+ 1 row in set (0.00 sec)
SHOW PRIVILEGES – 显示MySQL所支持的所有权限，及权限可操作的对象
mysql> SHOW PRIVILEGES; +-------------------------+---------------------------------------+-------------------------------------------------------+ | Privilege | Context | Comment | +-------------------------+---------------------------------------+-------------------------------------------------------+ | Alter | Tables | To alter the table | | Alter routine | Functions,Procedures | To alter or drop stored functions/procedures |
SHOW PLUGINS – 显示MySQL插件信息
mysql> SHOW PLUGINS; +----------------------------+----------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +----------------------------+----------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL | | mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL | | sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | ...................
SHOW MASTER STATUS – 显示Master当前正在使用的二进制信息
mysql> SHOW MASTER STATUS; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000010 | 7040 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
SHOW TABLE STATUS – 显示表属性信息
mysql> SHOW TABLE STATUS LIKE 'info'\G
*************************** 1. row ***************************
Name: info - 表名
Engine: InnoDB - 表存储引擎
Version: 10 - 表文件版本号
Row_format: Compact - 行存储格式（固定、动态、压缩、冗余、紧凑）
Rows: 1 - 表中行数量
Avg_row_length: 16384 - 平均行长度
Data_length: 16384 - 数据文件长度
Max_data_length: 0 - 数据文件最大长度，这是可以存储在表中的数据的总字节数
Index_length: 0 - 索引文件长度
Data_free: 0 - 表文件已分配但未使用的字节数
Auto_increment: 2 - 下一个auto_increment的值
Create_time: 2015-11-22 17:53:10 - 表创建时间
Update_time: NULL - 表更新时间，没有为NULL
Check_time: NULL - 表检查时间，没有为NULL
Collation: utf8_general_ci - 表使用的排序规则
Checksum: NULL - 表校验
Create_options:
Comment:
1 row in set (0.00 sec)
SHOW INDEX – 显示表索引信息（需要先创建索引）
mysql> show index from testdb.info\G
*************************** 1. row ***************************
Table: info - 表名
Non_unique: 0
Key_name: PRIMARY - 索引名字
Seq_in_index: 1 - 索引中列顺序号从1开始
Column_name: ID - 索引列
Collation: A - 列怎样在索引中被排序
Cardinality: 6 - 索引中唯一值的数量
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE - 索引算法类型
Comment:
Index_comment:
1 row in set (0.01 sec)
SHOW PROCEDURE STATUS – 显示存储过程信息（需要先创建存储过程，详情请看“存储过程文章”）
mysql> SHOW PROCEDURE STATUS LIKE 'sp1'\G
*************************** 1. row ***************************
Db: test
Name: sp1
Type: PROCEDURE
Definer: testuser@localhost
Modified: 2004-08-03 15:29:37
Created: 2004-08-03 15:29:37
Security_type: DEFINER
Comment:
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
SHOW FUNCTION STATUS – 显示存储函数信息（需要先创建存储函数，详情看“存储函数文章”）
mysql> SHOW fUNCTION STATUS; Empty set (0.00 sec)
SHOW TRIGGERS – 显示触发器信息（需要先创建触发器，详情看“触发器部分”）
mysql> SHOW TRIGGERS LIKE 'acc%'\G
*************************** 1. row ***************************
Trigger: ins_sum - 触发器名字
Event: INSERT - 触发事件
Table: account - 在那个表定义触发器
Statement: SET @sum = @sum + NEW.amount - 触发体
Timing: BEFORE - 是在触发之前激活触发事件，还是在触发之后激活触发事件
Created: NULL -
sql_mode: NO_ENGINE_SUBSTITUTION - 触发器执行时的SQL模式
Definer: me@localhost - 谁创建了触发器
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
SHOW PROFILE and SHOW PROFILES – 显示执行语句的资源使用情况
mysql> SELECT @@profiling; +-------------+ | @@profiling | +-------------+ | 0 | +-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SET profiling = 1; mysql> CREATE TABLE T1 (id INT); mysql> SHOW PROFILES; +----------+------------+--------------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------------+ | 1 | 0.11698325 | CREATE TABLE T1 (id INT) | | 2 | 0.12255200 | CREATE TABLE T2 (id INT) | +----------+------------+--------------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> SHOW PROFILE; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000080 | | checking permissions | 0.000015 | | Opening tables | 0.000129 | | creating table | 0.106344 | | After create | 0.000033 | | query end | 0.000066 | | closing tables | 0.000017 | | freeing items | 0.000241 | | cleaning up | 0.015628 | +----------------------+----------+ 9 rows in set, 1 warning (0.00 sec) mysql> SHOW PROFILE FOR QUERY 2; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000080 | ----------------------------------- mysql> SHOW PROFILE CPU FOR QUERY 2; +----------------------+----------+----------+------------+ | Status | Duration | CPU_user | CPU_system | +----------------------+----------+----------+------------+ | starting | 0.000080 | 0.000000 | 0.000000 | ---------------------------------------------------------- TYPE: CPU | MEMORY | IPC | SOURCE | ALL | BLOCK IO | SWAPS
SHOW SLAVE HOSTS – 显示Master主机上已注册的复制主机列表（需要先做主从复制，详情看“主从复制部分”）
mysql> SHOW SLAVE HOSTS; +-----------+-----------+-------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+-----------+-------+-----------+--------------------------------------+ | 192168010 | iconnect2 | 3306 | 192168011 | 14cb6624-7f93-11e0-b2c0-c80aa9429562 | | 1921680101 | athena | 3306 | 192168011 | 07af4990-f41f-11df-a566-7ac56fdaf645 | +------------+-----------+------+-----------+--------------------------------------+
SHOW SLAVE STATUS – 显示Slave主机状态信息（需要先做主从复制，详情看“主从复制部分”）
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 13000
Connect_Retry: 60
Master_Log_File: master-bin.000002
Read_Master_Log_Pos: 1307
Relay_Log_File: slave-relay-bin.000003
Relay_Log_Pos: 1508
Relay_Master_Log_File: master-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
...........
SHOW GLOBAL | SESSION STATUS – 显示MySQL状态变量信息
mysql> SHOW GLOBAL STATUS; +-----------------------------------------------+--------------------------------------------------+ | Variable_name | Value | +-----------------------------------------------+--------------------------------------------------+ | Aborted_clients | 3 | | Aborted_connects | 1 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 25 | ................
SHOW GLOBAL | SESSION VARIABLES – 显示MySQL系统变量信息
mysql> SHOW SESSION VARIABLES LIKE '%INNODB%'; +------------------------------------------+------------------------+ | Variable_name | Value | +------------------------------------------+------------------------+ | ignore_builtin_innodb | OFF | | innodb_adaptive_flushing | ON | | innodb_adaptive_flushing_lwm | 10 | | innodb_adaptive_hash_index | ON | | innodb_adaptive_max_sleep_delay | 150000 | | innodb_additional_mem_pool_size | 8388608 | | innodb_api_bk_commit_interval | 5 | | innodb_api_disable_rowlock | OFF | | innodb_api_enable_binlog | OFF | | innodb_api_enable_mdl | OFF | | innodb_api_trx_level | 0 | | innodb_autoextend_increment | 64 | | innodb_autoinc_lock_mode | 1 | | innodb_buffer_pool_dump_at_shutdown | ON | | innodb_buffer_pool_dump_now | OFF | | innodb_buffer_pool_filename | ib_buffer_pool | ..........................
浙公网安备 33010602011771号