Shulou(Shulou.com)05/31 Report--

This article mainly introduces what the pager command in MySQL has, which can be used for reference by interested friends. I hope you can learn a lot after reading this article.

I. paging result set

In Linux systems, we often use some paging commands, such as less, more, and so on. Similarly, the MySQL client provides similar commands to help us paginate the query result set. For example, it is very useful to view lock information through paging in SHOW ENGINE INNODB STATUS, so that you don't have to go to the bottom of the screen.

Mysql > pager lessPAGER set to 'less'mysql > show engine innodb status\ G [...]

Now you can easily browse the result set (use Q to exit, space scroll down, etc.).

If you want to leave your custom pager, it's easy, just run the pager command:

Mysql > pagerDefault pager wasn't set, using stdout.

Or

Mysql >\ nPAGER set to stdout

But the pager command is not limited to this basic usage! You can pass the query output to most Unix programs that can handle text. Here are some examples.

Discard the result set

Sometimes you don't care about the result set and just want to see the time information. This may happen if you try a different execution plan for the query by changing the index. Using pager, you can discard the result:

Mysql > pager cat > / dev/nullPAGER set to 'cat > / dev/null'# Trying an execution planmysql > SELECT. 1000 rows in set (0.91 sec) # Another execution planmysql > SELECT. 1000 rows in set (1.63 sec)

Now, it's much easier to view all the time information on one screen.

III. Compare result sets

Suppose you are rewriting the query and want to check whether the result set is the same before and after the rewrite. Unfortunately, it has many lines:

Mysql > SELECT. [..] 989 rows in set (.42 sec)

You can calculate the checksum and only compare the checksum instead of manually comparing each line:

Mysql > pager md5sumPAGER set to 'md5sum' # Original querymysql > SELECT... 32a1894d773c9b85172969c659175d2d-1 row in set (0.40 sec) # Rewritten query-wrongmysql > SELECT... fdb94521558684afedc8148ca724f578-1 row in set (0.16 sec)

Well, the checksum doesn't match. There's something wrong. Let's try again:

# Rewritten query-correctmysql > SELECT... 32a1894d773c9b85172969c659175d2d-1 row in set (0.17 sec)

The checksum is the same, and the rewritten query is likely to produce the same results as the original query.

Fourth, combine the system command

If there are many connections on the MySQL, it is difficult to read the output of the SHOW PROCESSLIST. For example, if you have hundreds of connections and you want to know how many connections are in Sleep state, manually calculating the lines in the SHOW PROCESSLIST output may not be the best solution. With pager, it is simple:

Mysql > pager grep Sleep | wc-lPAGER set to 'grep Sleep | wc-l'mysql > show processlist;337346 rows in set (0.00 sec)

This should be interpreted to mean that 337 of the 346 connections are in the Sleep state.

Now it's a little more complicated: you want to know the number of connections in each state.

Mysql > pager awk-F'|'{print $6}'| sort | uniq-c | sort-rPAGER set to 'awk-F' |'{print $6}'| sort | uniq-c | sort-r'mysql > show processlist; 309 Sleep 3 2 Query 2 Binlog Dump 1 Command

Of course, these problems can be solved by querying INFORMATION_SCHEMA. For example, calculating the number of Sleep connections can be done in the following ways:

Mysql > SELECT COUNT (*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Sleep';+-+ | COUNT (*) | +-+ | 320 | +-+

And calculate the number of connections for each state can be done in the following ways:

Mysql > SELECT COMMAND,COUNT (*) TOTAL FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY COMMAND ORDER BY TOTAL DESC;+-+-+ | COMMAND | TOTAL | +-+-+ | Sleep | 344 | Query | 5 | Binlog Dump | 2 | +-+-+

However, some people may feel more comfortable writing SQL queries, while others prefer to use command-line tools.

As you can see, pager is your friend! It is very easy to use, and it can solve problems in an elegant and efficient way. You can even write a custom script (if it's too complex to put on one line) and pass it to pager. In short, using more pager commands can help you get twice the result with half the effort.

Thank you for reading this article carefully. I hope the article "what are the pager commands in MySQL" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.