In MySQL, the show syntax is used to obtain detailed information about the table column and the entire database.

Show databases;

Show tables from db_name;

Show columns from table_name from db_name;

Show index from talbe_name [from db_name];

Show status;

Show variables;

Show [full] processlist;

Show table status [from db_name];

Show grants for user;

In addition to status, processlist, and grants, other options can contain the like wild option, which can use the SQL '%' and '_' characters;

Show databases like '% T ';

All databases whose names end with the 'T' character will be listed

Of course, in these SQL statements, you can also use db_name.table_name to replace table_name from db_name for easier writing!

If a user does not have any permissions for a table, the table will notSHOW TABLESOrmysqlshow db_nameShown in the output

You may still remember describe table_name, which achieves the same effect as show columns from db_name.table_name.

You can use mysqlshow -- status to get the same effect on show status.

Column	Description
Name	Table Name
Type	Table type (ISAM, MyISAM, or HEAP)
Row_format	Row Storage Format (fixed, dynamic, or compressed)
Rows	Number of rows
Avg_row_length	Average line length
Data_length	Data File Length
Max_data_length	Maximum data file length
Index_length	Index file length
Data_free	Allocated but not used bytes
Auto_increment	Next autoincrement (automatically add 1) Value
Create_time	Table Creation Time
Update_time	Last Update Time of the data file
Check_time	Last time a check was run on the table
Create_options	AndCREATE TABLEAdditional options used together
Comment	Note used when creating a table (or why MySQL cannot access some table information ).

 

 

SHOW FIELDSYesSHOW COLUMNSA synonym,SHOW KEYSYesSHOW INDEXA synonym. You can also usemysqlshow db_name tbl_nameOrmysqlshow -k db_name tbl_nameLists the columns or indexes of a table.

SHOW INDEXSimilar to ODBCSQLStatisticsThe format of the call to return the index information. The following columns are returned:

 

Column	Description
Table	Table Name
Non_unique	0. If the index cannot contain duplicates.
Key_name	Index name
Seq_in_index	The column sequence number in the index, starting from 1.
Column_name	Column name.
Collation	How columns are sorted in the index. InMySQL, Which can have a valueA(Ascending) orNULL(Not sorted ).
Cardinality	The number of unique values in the index. This can be done by runningisamchk -aChange.
Sub_part	If a column is partially indexed, the number of index characters is returned.NULLIf the entire key is indexed.

 

SHOW STATUSProvide server status information (e.g.mysqladmin extended-status). The output is similar to the following display, although the format and number can be a bit different:

+--------------------------+--------+
| Variable_name            | Value  |
+--------------------------+--------+
| Aborted_clients          | 0      |
| Aborted_connects         | 0      |
| Connections              | 17     |
| Created_tmp_tables       | 0      |
| Delayed_insert_threads   | 0      |
| Delayed_writes           | 0      |
| Delayed_errors           | 0      |
| Flush_commands           | 2      |
| Handler_delete           | 2      |
| Handler_read_first       | 0      |
| Handler_read_key         | 1      |
| Handler_read_next        | 0      |
| Handler_read_rnd         | 35     |
| Handler_update           | 0      |
| Handler_write            | 2      |
| Key_blocks_used          | 0      |
| Key_read_requests        | 0      |
| Key_reads                | 0      |
| Key_write_requests       | 0      |
| Key_writes               | 0      |
| Max_used_connections     | 1      |
| Not_flushed_key_blocks   | 0      |
| Not_flushed_delayed_rows | 0      |
| Open_tables              | 1      |
| Open_files               | 2      |
| Open_streams             | 0      |
| Opened_tables            | 11     |
| Questions                | 14     |
| Slow_queries             | 0      |
| Threads_connected        | 1      |
| Threads_running          | 1      |
| Uptime                   | 149111 |
+--------------------------+--------+

The state variables listed above have the following meanings:

Aborted_clients	The number of dropped connections because the customer does not close the connection correctly.
Aborted_connects	The number of times the MySQL server has failed to be connected.
Connections	Number of attempts to connect to the MySQL server.
Created_tmp_tables	The number of implicit temporary tables that have been created when a statement is executed.
Delayed_insert_threads	The number of threads that are using the delayed insert processor.
Delayed_writes	UseINSERT DELAYEDNumber of lines written.
Delayed_errors	UseINSERT DELAYEDWrite errors (possibleDuplicate key value.
Flush_commands	RunFLUSHThe number of times the command is executed.
Handler_delete	The number of times a row is deleted from a table.
Handler_read_first	The number of times the first row of the table is read.
Handler_read_key	The request number is based on the key-read row.
Handler_read_next	The number of times a single key-based row is read.
Handler_read_rnd	The number of times a request reads a row based on a fixed position.
Handler_update	The number of requests to update a row in the table.
Handler_write	The number of times a row is inserted into the table.
Key_blocks_used	The number of blocks used for keyword cache.
Key_read_requests	The number of times a key value is read from the cache.
Key_reads	The number of times a key value is physically read from a disk.
Key_write_requests	The number of times a keyword block is written to the cache.
Key_writes	The number of times a key-Value block is physically written to a disk.
Max_used_connections	The maximum number of connections used at the same time.
Not_flushed_key_blocks	The key block that has been changed in the key cache but has not been cleared to the disk.
Not_flushed_delayed_rows	InINSERT DELAYThe number of rows waiting to be written in the queue.
Open_tables	The number of opened tables.
Open_files	The number of opened files.
Open_streams	Number of opened streams (mainly used for logging)
Opened_tables	The number of opened tables.
Questions	The number of queries sent to the server.
Slow_queries	Morelong_query_timeTime query count.
Threads_connected	The number of currently opened connections.
Threads_running	Number of threads not sleeping.
Uptime	How many seconds does the server work.

Notes:

• IfOpened_tablesToo big, so yourtable_cacheThe variable may be too small.
• Ifkey_readsToo big, so yourkey_cacheIt may be too small. The cache hit rate can be used.key_reads/key_read_requestsComputing.
• IfHandler_read_rndIf it is too large, you may have a large number of queries that require MySQL to scan the entire table or that you have not correctly used the join key value ).

SHOW VARIABLESShow someMySQLSystem variable value, you can also usemysqladmin variablesCommand to obtain this information. If the default value is not suitable, you canmysqldUse the command line option to set the majority of these variables at startup. The output is similar to the following display, although the format and number can be a bit different:

+ ------------------------ + -------------------------- +
| Variable_name | value |
+ ------------------------ + -------------------------- +
| Back_log | 5 |
| Connect_timeout | 5 |
| Basedir |/My/Monty/|
| Datadir |/My/Monty/data/|
| Delayed_insert_limit | 1, 100 |
| Delayed_insert_timeout | 300 |
| Delayed_queue_size | 1, 1000 |
| Join_buffer_size | 131072 |
| Flush_time | 0 |
| Interactive_timeout | 28800 |
| Key_buffer_size | 1048540 |
| Language |/my/monty/share/english/|
| Log | OFF |
| Log_update | OFF |
| Long_query_time | 10 |
| Low_priority_updates | OFF |
| Max_allowed_packet| 1048576 |
| Max_connections | 100 |
| Max_connect_errors | 10 |
| Max_delayed_threads | 20 |
| Max_heap_table_size | 16777216 |
| Max_join_size | 4294967295 |
| Max_sort_length | 1024 |
| Max_tmp_tables | 32 |
| Net_buffer_length | 16384 |
| Port | 1, 3306 |
| Protocol-version | 10 |
| Record_buffer | 131072 |
| Skip_locking | ON |
| Socket |/tmp/mysql. sock |
| Sort_buffer | 2097116 |
| Table_cache | 64 |
| Thread_stack | 131072 |
| Tmp_table_size | 1048576 |
| Tmpdir |/machine/tmp/|
| Version | 3.23.0-alpha-Debug |
| Wait_timeout | 28800 |
+ ------------------------ + -------------------------- +
SHOW PROCESSLISTShows which thread is running. You can also usemysqladmin processlistCommand to obtain this information.
If you haveProcessPermission, you can see all the threads, otherwise, you can only see your own threads.
See 7.20 KILLSyntax. If you do not useFULLThen, each query is only displayed with the first 100 characters.
SHOW GRANTS FOR userLists authorization commands that must be repeatedly authorized to a user.
mysql> SHOW GRANTS FOR root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root''localhost' WITH GRANT OPTION  |
+---------------------------------------------------------------------+
 
 
Original article link: http://www.busfly.cn/csdn/post/MySQL-show-get-database-info.html