sysdate()和now()差不多, 都可以返回當前的時間,形式為 ‘YYYY-MM-DD HH:MM:SS’ 或者 YYYYMMDDHHMMSS.uuuuuu。
mysql> SELECT version();
+------------+
| version() |
+------------+
| 5.5.22-log |
+------------+
1 ROW IN SET (0.01 sec)
mysql> SELECT now(), now()+0, sysdate(), sysdate()+0;
+---------------------+-----------------------+---------------------+----------------+
| now() | now()+0 | sysdate() | sysdate()+0 |
+---------------------+-----------------------+---------------------+----------------+
| 2012-12-06 11:15:03 | 20121206111503.000000 | 2012-12-06 11:15:03 | 20121206111503 |
+---------------------+-----------------------+---------------------+----------------+
1 ROW IN SET (0.02 sec)
區別有如下幾點:
1) sysdate() 返回的是sysdate()函數被調用時的時間,
now()返回的是整條sql語句開始執行時的時間。
(Within a stored function or trigger, NOW() returns the time at which the function or triggering statement began to execute.)
mysql> SELECT NOW(), SLEEP(2), NOW();
+---------------------+----------+---------------------+
| NOW() | SLEEP(2) | NOW() |
+---------------------+----------+---------------------+
| 2012-12-06 11:17:52 | 0 | 2012-12-06 11:17:52 |
+---------------------+----------+---------------------+
1 ROW IN SET (2.01 sec)
mysql> SELECT sysdate(), sleep(2), sysdate();
+---------------------+----------+---------------------+
| sysdate() | sleep(2) | sysdate() |
+---------------------+----------+---------------------+
| 2012-12-06 11:18:01 | 0 | 2012-12-06 11:18:03 |
+---------------------+----------+---------------------+
1 ROW IN SET (2.00 sec)
2) SET TIMESTAMP語句可以影響now()的返回值,卻不會影響到sysdate()。
mysql> SET TIMESTAMP=UNIX_TIMESTAMP('1999-01-01');
Query OK, 0 ROWS affected (0.06 sec)
mysql> SELECT NOW(),SYSDATE();
+---------------------+---------------------+
| NOW() | SYSDATE() |
+---------------------+---------------------+
| 1999-01-01 00:00:00 | 2012-12-06 11:21:53 |
+---------------------+---------------------+
1 ROW IN SET (0.00 sec)
3) 從2)可知,在statement-based的主從復制的時候,使用sysdate()會出問題。
詳情請看這種情況下的binary log。
解決辦法麼,使用row-based logging,或者使用 –sysdate-is-now option(主從服務器都要設置)。
4) sysdate()會影響到key的使用。
mysql> EXPLAIN SELECT * FROM shipin.xxxx2 WHERE add_time >( now() - INTERVAL 1
DAY) \G
*************************** 1. ROW ***************************
id: 1
select_type: SIMPLE
TABLE: xxxx2
TYPE: range
possible_keys: add_time
KEY: add_time
key_len: 4
REF: NULL
ROWS: 2883
Extra: USING WHERE
1 ROW IN SET (0.00 sec)
mysql> EXPLAIN SELECT * FROM shipin.xxxx2 WHERE add_time >( sysdate() - interva
l 1 DAY) \G
*************************** 1. ROW ***************************
id: 1
select_type: SIMPLE
TABLE: xxxx2
TYPE: ALL
possible_keys: NULL
KEY: NULL
key_len: NULL
REF: NULL
ROWS: 1071878
Extra: USING WHERE
1 ROW IN SET (0.00 sec)