Show processlist result Filtering

Show processlist result filtering in MySQL the show variables where variable_name like '% auto %' statement can be executed normally, but the show processlist where host like '% 100' error will be reported: error Code: 1064You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where host like '% 10% "at line 1 show processlist can be executed directly, however, filtering is not possible, which is a headache. The same structure of variables is acceptable, but why processlist is No? The cause is that MySQL has this BUG: Description: show processlist lists to parallel lines when you have 250 or even 1000 users connect to yousystem. how to repeat: n. a. suggested fix: It wocould be nice having something like show processlist like 'user | host |... 'To reduce the output to the relevant informations. much more nice wocould be having something like: SELECT user, host, time, command, time FROM [mysql | information_schema]. processlist WHERE user = 'me' and state is not null; therefore, the show method of processlist cannot use filter lookup. It may be due to the internal security mechanism of MySQL, show is used to view the internal running data of MySQL. In fact, processlist is a table in the information_schema database. Therefore, you can use the table query method: SELECT user, host, time, command, time FROM [mys Ql | information_schema]. processlist WHERE user = 'me' and state is not null; for more information about the MySQL Show command, see show tables or show tables from database_name or show database_name.tables. Explanation: show databases: displays the names of all tables in the current database. Description: displays the names of all databases in mysql. Description: displays all processes running in the system, that is, the query currently being executed. Most users can view their own processes, but if they have the process permission, they can view all processes, including passwords. Show table status: displays information about each table in the currently used or specified database. The information includes the table type and the latest update time of the table. show columns from table_name from database_name; or show columns from database_name.table_name; or show fields; explanation: show grants for user_name @ localhost; show the name of the column in the table (the same effect as the desc table_name command). Explanation: displays the permissions of a user. The result is similar to the grant command show index from table_name; or show keys; Explanation: displays the table index show status; Explanation: displays information about specific system resources, for example, the number of running threads show variables; explanation: show the name and value of the system variable show privileges; Explanation: show the different permissions supported by the server show create database Database_name; Description: The SQL statement show create table table_name for creating a specified database is displayed. Description: The SQL statement show engies for creating a specified data table is displayed. Explanation: displays available storage engines and default engines after installation. Show innodb status; Description: displays the status of the innoDB Storage engine. Description: displays the logs of the BDB storage engine. Description: show errors: displays the errors, warnings, and notifications generated by the last executed statement. Explanation: Only displays the errors generated by the last executed statement. Most of the preceding commands can use like, for example, show table like '% abce % '. Appendix: show status result description column meaning Name table Name Type table Type (ISAM, MyISAM or HEAP) Row_format Row Storage Format (fixed, dynamic, or compressed) rows row count Avg_row_length average row length Data_length data file length Max_data_length Maximum length of the data file Index_length index file length Data_free allocated but no Bytes Auto_increment next autoincrement (automatically add 1) value: Create_time: the time when the TABLE was created. Update_time: The Last Update Time of the data file. Check_time: The last time the TABLE was run. The Create_options option is used together with create table. Comment: when creating a TABLE, note used (or why MySQL cannot access some table information ). Show index result Description: The column meaning Table name Non_unique 0. If the index cannot contain duplicates. The column sequence number in the Key_name index name Seq_in_index, starting from 1. Column_name column name. How to sort Collation columns in the index. In MySQL, this value can be A (ascending) or NULL (unordered ). The number of unique values in the Cardinality index. This can be changed by running isamchk-a. Sub_part. If the column is only partially indexed, the number of index characters is displayed. NULL, if the entire key is indexed. Show variables result Description: Aborted_clients indicates the number of dropped connections because the client fails to properly close the connection. The number of connection times of the MySQL server that failed the Aborted_connects attempt. The number of times that Connections attempted to connect to the MySQL server. Created_tmp_tables: Number of implicit temporary tables created when the statement is executed. The number of threads that Delayed_insert_threads is using to insert a delayed processor. The number of rows written by Delayed_writes Using insert delayed. The number of rows in which Delayed_errors writes data using insert delayed (which may duplicate key values. The number of times Flush_commands executes the FLUSH command. The number of rows that Handler_delete requests to delete from a table. The number of times the Handler_read_first request reads the first row of the table. The Handler_read_key request number is based on the key-read row. The number of times the Handler_read_next request reads a row based on a key. The number of times the Handler_read_rnd request reads a row based on a fixed position. The number of times Handler_update requests to update a row in the table. The number of times a Handler_write request inserts a row into a table. The number of Key_blocks_used Blocks Used for keyword cache. The number of times Key_read_requests requests read a key value from the cache. The number of times that Key_reads reads a key value from the disk physically. Number of times that Key_write_requests requests write a key block to the cache. The number of times that Key_writes physically writes a key-Value block to a disk. The maximum number of connections simultaneously used by Max_used_connections. Not_flushed_key_blocks has been changed in the key cache but has not been cleared to the disk. The number of rows that Not_flushed_delayed_rows is waiting to write in the insert delay queue. The number of open tables in Open_tables. The number of open files in Open_files. Open_streams: Number of opened tables in Opened_tables (mainly used for logging. The number of queries sent by Questions to the server. Slow_queries takes more than long_query_time. The number of connections currently opened by Threads_connected. The number of threads whose Threads_running is not sleeping. How many seconds does the Uptime server work. NOTE: If Opened_tables is too large, your table_cache variable may be too small. If key_reads is too large, your key_cache may be too small. The cache hit rate can be calculated using key_reads/key_read_requests. If Handler_read_rnd 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 operation ).