SLEEP()

Description

Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0.

Syntax

  1. >
  2. SLEEP(duration)

Arguments

ArgumentsDescription
durationRequired. The number of seconds. The duration may have a fractional part. The duration is not allowed NULL or negative.

Returned Value

  • When sleep returns normally (without interruption), it returns 0.

  • When SLEEP() returns 1 (with interruption or time out), the query returns no error.

    For example:

    1. In session 1, execute the following command to query the current connection_id and execute the SLEEP() function:

      1. mysql> select connection_id();
      2. +-----------------+
      3. | connection_id() |
      4. +-----------------+
      5. |            1476 |
      6. +-----------------+
      7. 1 row in set (0.03 sec)
      8. mysql> select sleep(200);

    2. At this point, open a new session, interrupt session 1, and run the following command.

      1. mysql> kill 1463;
      2. Query OK, 0 rows affected (0.00 sec)

    3. Check the query result of session 1:

      1. mysql> select sleep(200);
      2. +------------+
      3. | sleep(200) |
      4. +------------+
      5. |          1 |
      6. +------------+
      7. 1 row in set (26.50 sec)

  • When SLEEP() returns an error (part of a query is uninterrupted). For example:

    1. mysql> SELECT 1 FROM t1 WHERE SLEEP(1000);
    2. ERROR 20101 (HY000): internal error: pipeline closed unexpectedly

Examples

  1. -- without interruption
  2. mysql> SELECT SLEEP(1);
  3. +----------+
  4. | sleep(1) |
  5. +----------+
  6. |        0 |
  7. +----------+
  8. 1 row in set (1.01 sec)
  10. -- without interruption
  11. mysql> SELECT SLEEP(1000);
  12. +-------------+
  13. | sleep(1000) |
  14. +-------------+
  15. |           0 |
  16. +-------------+
  17. 1 row in set (18 min 20.87 sec)
  19. create table t1 (a int,b int);
  20. insert into t1 values (1,1),(1,null);
  21. mysql> select sleep(a) from t1;
  22. +----------+
  23. | sleep(a) |
  24. +----------+
  25. |        0 |
  26. |        0 |
  27. +----------+
  28. 2 rows in set (2.01 sec)