MYSQL SHOW 用法
1、SHOW CHARACTER SET 显示所有可用的字符集
mysql> SHOW CHARACTER SET LIKE 'utf8'; +---------+---------------+-------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------+-------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | +---------+---------------+-------------------+--------+ 1 row in set (0.00 sec)
2、SHOW COLLATION 显示所有的校对规则
mysql> SHOW CHARACTER SET LIKE 'utf8'; +---------+---------------+-------------------+--------+ | Charset | Description | Default collation | Maxlen | +---------+---------------+-------------------+--------+ | utf8 | UTF-8 Unicode | utf8_general_ci | 3 | +---------+---------------+-------------------+--------+ 1 row in set (0.00 sec)
3、SHOW COLUMNS 显示在一个给定表中的各列的信息（等同于DESC）
mysql> SHOW COLUMNS FROM zjf.a1; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.15 sec)
4、SHOW CREATE TABLE 显示用于创建给定表的CREATE TABLE语句
mysql> SHOW CREATE TABLE zjf.a1; +-------+----------------------------------------------------------------------------------+ | Table | Create Table | +-------+----------------------------------------------------------------------------------+ | a1 | CREATE TABLE `a1` ( `id` int(11) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-------+----------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
5、SHOW OPEN TABLES 显示数据库中正在打开的表
mysql> SHOW OPEN TABLES FROM zjf; +----------+-------+--------+-------------+ | Database | Table | In_use | Name_locked | +----------+-------+--------+-------------+ | zjf | a1 | 0 | 0 | +----------+-------+--------+-------------+ 1 row in set (0.00 sec)
6、SHOW TABLES 显示数据库的所有表
mysql> USE zjf; Database changed mysql> SHOW TABLES; +---------------+ | Tables_in_zjf | +---------------+ | a1 | +---------------+ 1 row in set (0.00 sec)
7、SHOW TABLE STATUS 性质与SHOW TABLE类似，不过，可以提供每个表的大量信息
mysql> SHOW TABLE STATUS FROM zjf WHERE Name='a1'; +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ | a1 | InnoDB | 10 | Dynamic | 0 | 0 | 16384 | 0 | 0 | 0 | NULL | 2016-02-17 11:24:19 | NULL | NULL | utf8_general_ci | NULL | | | +------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-----------------+----------+----------------+---------+ 1 row in set (0.00 sec)
8、SHOW DATABASES 在MySQL服务器主机上列举数据库
mysql> SHOW DATABASES LIKE 'zjf'; +----------------+ | Database (zjf) | +----------------+ | zjf | +----------------+ 1 row in set (0.00 sec)
9、SHOW ENGINE 显示存储引擎的日志或状态信息
mysql> SHOW ENGINE innodb status \G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2016-02-17 13:28:24 0x7f9eeea16700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 24 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 4 srv_active, 0 srv_shutdown, 7149 srv_idle srv_master_thread log flush and writes: 7153 ...
10、SHOW ENGINES 显示存储引擎的状态信息
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| CSV | YES | CSV storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
11、SHOW ERRORS 本语句与SHOW WARNINGS接近，不过该语句只显示错误，不同时显示错误、警告和注意。
mysql> SHOW ERRORS; +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE 'DEFAULT'' at line 1 | +-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW COUNT(*) ERRORS; +-----------------------+ | @@session.error_count | +-----------------------+ | 1 | +-----------------------+ 1 row in set (0.00 sec)
12、SHOW WARNINGS显示由上一个生成消息的语句导致的错误、警告和注意消息。
mysql> SHOW WARNINGS; Empty set (0.08 sec) mysql> SHOW COUNT(*) WARNINGS; +-------------------------+ | @@session.warning_count | +-------------------------+ | 0 | +-------------------------+ 1 row in set (0.00 sec)
13、SHOW VARIABLES显示了部门MySQL系统变量的值
mysql> SHOW VARIABLES LIKE 'version'; +---------------+--------+ | Variable_name | Value | +---------------+--------+ | version | 5.7.11 | +---------------+--------+ 1 row in set (0.00 sec)
14、 SHOW GRANTS 用户账户复制权限时必须发布的GRANT语句
mysql> SHOW GRANTS; +---------------------------------------------------------------------+ | Grants for root@localhost | +---------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +---------------------------------------------------------------------+ 2 rows in set (0.00 sec)
15、SHOW INDEX 返回表索引信息
mysql> SHOW INDEX FROM mysql.user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | | | user | 0 | PRIMARY | 2 | User | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.04 sec)
16、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 | | Create | Databases,Tables,Indexes | To create new databases and tables | | Create routine | Databases | To use CREATE FUNCTION/PROCEDURE | | Create temporary tables | Databases | To use CREATE TEMPORARY TABLE | | Create view | Tables | To create new views | | Create user | Server Admin | To create new users | ...
17、SHOW PROCESSLIST显示哪些线程正在运行（高端）
mysql> show processlist; +----+------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+------------------+ | 3 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+------+-----------+------+---------+------+----------+------------------+ 1 row in set (0.00 sec)
18、SHOW STATUS提供服务器状态信息
mysql> SHOW STATUS LIKE 'Binlog%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Binlog_cache_disk_use | 0 | | Binlog_cache_use | 0 | | Binlog_stmt_cache_disk_use | 0 | | Binlog_stmt_cache_use | 0 | +----------------------------+-------+ 4 rows in set (0.05 sec)
19、SHOW TRIGGERS 列出了目前被MySQL服务器定义的触发程序
mysql> SHOW TRIGGERS LIKE 'acc%'; +---------+--------+---------+-------------------------------+--------+---------+ | Trigger | Event | Table | Statement | Timing | Created | +---------+--------+---------+-------------------------------+--------+---------+ | ins_sum | INSERT | account | SET @sum = @sum + NEW.amount | BEFORE | NULL | +---------+--------+---------+-------------------------------+--------+---------+
