MySQL in a Nutshell, 2nd Edition

Name

SLEEP()

Synopsis

SLEEP(seconds)

This function pauses the execution of an SQL statement in which it is given for the number of seconds given. It returns 0 in the results if successful; 1 if not. This function became available as of version 5.0.12 of MySQL. It’s not exactly a time and date function, but it’s included here due to it’s true time aspects. Here is an example:

SELECT SYSDATE( ) AS 'Start',
SLEEP(5) AS 'Pause',
SYSDATE( ) AS 'End';

+---------------------+-------+---------------------+
| Start               | Pause | End                 |
+---------------------+-------+---------------------+
| 2008-07-16 13:50:20 |     0 | 2008-07-16 13:50:25 | 
+---------------------+-------+---------------------+
1 row in set (5.13 sec)

The SYSDATE() function returns the time it is executed, not necessarily the time the statement started or finished. You can see that the time in the first field is different by five seconds from the results in the third field due to the use of SLEEP(). Notice also that the statement took a little over five seconds to execute.

If you type Ctrl-C one time before an SQL statement containing SLEEP() is completed, it will return 1 for the SLEEP() field and MySQL will then go on to execute the rest of the SQL statement. In that case, the third field in the previous example would show less than a five-second difference from the first.