About MySQL's sleep (N) function

Know that you can have this statement run for n seconds by executing select sleep (n) in MySQL:

[SQL]View PlainCopy 

1. Mysql> Select Sleep (1);
2. +----------+  
3. | Sleep (1) |
4. +----------+  
5. | 0 |
6. +----------+  
7. 1 row in Set (1.00 sec)

The execution time returned to the client shows a wait of 1 seconds

With the help of the Sleep (N) function, we can capture in MySQL server's processlist a statement that executes quickly and easily to be seen to determine whether our program actually originated the statement on the server side. For example, when we are debugging, we want to determine whether the program actually makes a request to the server to execute the SQL statement, then we can see whether the statement appears by executing show processlist or by the Information_schema.processlist table. However, the sentence execution speed can be very fast, so it is difficult to determine whether the statement is actually executed by the above method. For example, the execution time of the following statement is 0.00 seconds, and the thread information flashes past, and it is impossible to detect.

[SQL]View PlainCopy 

1. mysql> Select name from animals where name=' Tiger ';
2. +-------+  
3. |  name |
4. +-------+  
5. | Tiger |
6. +-------+  
7. 1 row in Set (0.00 sec)

In this case, you can view the background thread by adding a sleep (n) function to the statement, forcing the statement to stay for N seconds, for example:

[SQL]View PlainCopy 

1. mysql> select sleep (1), name from animals where name= ' tiger ';   
2. +----------+-------+  
3. | sleep (1)  | NAME  |  
4. +----------+-------+  
5. |        0 | TIGER |  
6. +----------+-------+  
7. 1 row in set  (1.00 sec)    

The same condition returns the execution time of 1 seconds for the statement.

However, the use of this method is conditional, and only specify the condition of the record exists only to stop the specified number of seconds, such as the query condition is name= ' pig ', the result indicates that the record does not exist, the execution time is 0

[SQL]View PlainCopy 

1. mysql> Select name from animals where name=' pig ';
2. Empty Set (0.00 sec)

In such a condition, even if the function of sleep (N) is added, the execution of the statement will flash through, for example:

[SQL]View PlainCopy 

1. Mysql> Select Sleep (1),name from animals where name=' pig ';
2. Empty Set (0.00 sec)

It is also important to note that after adding the sleep (N) function, the execution of the statement will be based on the number of records that satisfy the condition, and MySQL will stay N seconds for each record that satisfies the condition.
For example, the name like '%ger ' has three records

[SQL]View PlainCopy 

1. mysql> select  name from animals where  name like  '%ger ';   
2. +-------+  
3. | name  |  
4. +- ------+  
5. | GER   |  
6. | iger   |  
7. | TIGER |  
8. +- ------+  
9. 3 rows in  set  (0.00 sec)   

After adding sleep (1) to the statement, the total execution time for the statement is 3.01 seconds, which results in a 1-second stay for each record that satisfies the condition.

[SQL]View PlainCopy 

2. Mysql> Select Sleep (1),name from animals where name is like '%ger ';
4. +----------+-------+  
6. | Sleep (1) |  name |
8. +----------+-------+  
10. | 0 | Ger |
12. | 0 | Iger |
14. | 0 | Tiger |
16. +----------+-------+  
18. 3 rows in Set (3.01 sec)

About MySQL's sleep (N) function