MYSQL SHOW Usage

1. Show CHARACTER set shows all available character sets

' UTF8 ' ; +---------+---------------+-------------------+--------+| Charset | Description | Default Collation | MaxLen |+---------+---------------+-------------------+--------+| UTF8 | utf-83 |+---------+---------------+-------------------+--------+1 in Set (  0.00 sec)

2, show COLLATION display all the proofing rules

' UTF8 ' ; +---------+---------------+-------------------+--------+| Charset | Description | Default Collation | MaxLen |+---------+---------------+-------------------+--------+| UTF8 | utf-83 |+---------+---------------+-------------------+--------+1 in Set (  0.00 sec)

3. Show COLUMNS information for each column displayed in a given table (equivalent to DESC)

Mysql> SHOW COLUMNS from zjf.a1; ID int (One) | NO | | NULL | |+-------+---------+------+-----+---------+-------+1 in Set (0.15 sec)

4. Show CREATE table displays the CREATE TABLE statement for creating a given table

mysql> SHOW CREATE TABLE zjf.a1; +-------+----------------------------------------------------------------------------------+| Table | Create Table |+-------+----------------------------------------------------------------------------------+| A1 | CREATE TABLE ' A1 ' ('IDint(one) ' notNULL) ENGINE=innodb DEFAULT charset= UTF8 |+-------+----------------------------------------------------------------------------------+1  in Set (0.00 sec)

5. Show open TABLES Displays the table that is being opened in the database

mysql> SHOW OPEN TABLES from ZJF; 0 0 |+----------+-------+--------+-------------+1 in Set (0.00 sec)

6, Show TABLES All tables of the database

mysql> use zjf;database changedmysql> SHOW TABLES; +---------------+| TABLES_IN_ZJF |+---------------+| A1 |+---------------+1 in Set (0.00 sec)

7. The show table STATUS is similar to show table, but can provide a large amount of information for each 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 |Ten| Dynamic |0|0|16384|0|0|0| NULL | .- Geneva- -  One: -: +| NULL | NULL | Utf8_general_ci | NULL | | |+------+--------+---------+------------+------+----------------+-------------+-----------------+-------- ------+-----------+----------------+---------------------+-------------+------------+-----------------+-------- --+----------------+---------+1RowinchSet (0.00Sec

8. SHOW DATABASES lists the database on the MySQL server host

' ZJF ' ; +----------------+| Database (ZJF) |+----------------+| ZJF |+----------------+1 in Set (0.00 sec)

9. Show engine displays log or status information for storage engines

Mysql>SHOW ENGINE innodb status \g***************************1. Row ***************************Type:InnoDBName:Status:===================================== .- Geneva- -  -: -: - 0x7f9eeea16700INNODB MONITOR OUTPUT=====================================Per second averages calculated from the Last  -seconds-----------------BACKGROUND THREAD-----------------Srv_master_thread Loops:4Srv_active,0Srv_shutdown,7149Srv_idlesrv_master_thread log Flush and writes:7153...

10. Show ENGINES Display the status information of the storage engine

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 This statement is close to show warnings, but the statement displays only errors, not errors, warnings, and notices.

Mysql>SHOW ERRORS;+-------+------+----------------------------------------------------------------------------------------------- -------------------------------------------------------------------+| Level | Code | Message |+-------+------+--------------------------------------------------------------------------------------- ---------------------------------------------------------------------------+| Error |1064| You have an errorinchyour SQL syntax; Check the manual, corresponds to your MySQL server version forThe right syntax' like'DEFAULT"'At line1|+-------+------+---------------------------------------------------------------------------------------------- --------------------------------------------------------------------+1RowinchSet (0.00sec) MySQL> SHOW COUNT (*) ERRORS;+-----------------------+| @ @session. error_count |+-----------------------+|1|+-----------------------+1RowinchSet (0.00Sec

12. Show warnings displays errors, warnings, and note messages caused by the previous statement that generated the message.

mysql> SHOW WARNINGS; Empty Set (0.08  sec) MySQL> SHOW COUNT (*) WARNINGS; 0 |+-------------------------+1 in Set (0.00 sec)

13. Show variables shows the value of the department MySQL system variable

' version ' ; 5.7.  |+---------------+--------+1 in Set (0.00 sec)

14. Grant statement that must be published when SHOW GRANTS user account copy Permissions

Mysql>SHOW GRANTS;+---------------------------------------------------------------------+| Grants for[Email protected] |+---------------------------------------------------------------------+| GRANT all privileges on * * to'Root'@'localhost'With GRANT OPTION | | GRANT PROXY on"'@"'To'Root'@'localhost'With GRANT OPTION |+---------------------------------------------------------------------+2RowsinchSet (0.00Sec

15. SHOW Index returns table indexing information

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 | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+---- ----+------+------------+---------+---------------+2RowsinchSet (0.04Sec

16. Show privileges shows the list of system permissions supported by MySQL server

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 shows which threads are running (high-end)

Mysql> show processlist; 3 0 | Starting | Show Processlist |+----+------+-----------+------+---------+------+----------+------------------+1  in Set (0.00 sec)

18. SHOW status provides server state information

' binlog% ' ; 0 0 0 0 |+----------------------------+-------+4 in Set (0.05 sec)

19. SHOW TRIGGERS Lists the trigger programs currently defined by MySQL server

' acc% ' ; +---------+--------+---------+-------------------------------+--------+---------+| Trigger | Event | Table | Statement | Timing | Created |+---------+--------+---------+-------------------------------+--------+---------+| Ins_sum | INSERT | Account | SET @sum = @sum + new.amount | Before | NULL |+---------+--------+---------+-------------------------------+--------+---------+

Reference Documentation:

MySQL 5.1 Manual

MYSQL SHOW Usage