Interpretation of mysql time functions sysdate () and now () _ MySQL

Interpretation of mysql time functions sysdate () and now () bitsCN.com

Interpretation of mysql time functions sysdate () and now ()

NOW ()

Returns the current date and time value in the format of 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS, depending on whether the function is used in a string or a numeric context.

Mysql> select now ();

-> '2017-12-15 23:50:26'

Mysql> select now () + 0;

-> 19971215235026

In a stored program or trigger, NOW () returns a constant time, which indicates the start time of execution of the program or trigger statement. This is different from SYSDATE.

SYSDATE ()

Returns the current date and time value in the format of 'yyyy-MM-DD HH: MM: SS' or YYYYMMDDHHMMSS. the specific format depends on whether the function is used in a string or numeric context.

In a storage program or trigger, SYSDATE () returns the execution time, rather than the time when the Chengdu or trigger statement starts to be executed. This NOW () operation is different.

My understanding:

Now () is generated when the application initiates an execution statement to the mysql server (whether it is the SQL statement sent or the name of the stored procedure.

Call now () in this stored procedure or in SQL to call this variable.

The sysdate () function retrieves data in real time each time.

In our company's business, because the use of stored procedures is prohibited, it is clear that both sysdate () and now () can meet business needs. Of course, considering that sysdate takes time in real time, I think now () is recommended.

Practice is the only criterion for testing truth:

1. Stored Procedure:

BEGIN

Select now (), sysdate ();

Select sleep (3 );

Select now (), sysdate ();

END

Execution result:

Mysql> call test;

+ --------------------- +

| Now () | sysdate () |

+ --------------------- +

| 11:22:58 | 11:22:58 |

+ --------------------- +

1 row in set (0.00 sec)

+ ---------- +

| Sleep (3) |

+ ---------- +

| 0 |

+ ---------- +

1 row in set (3.00 sec)

+ --------------------- +

| Now () | sysdate () |

+ --------------------- +

| 11:23:01 | 11:23:01 |

+ --------------------- +

1 row in set (3.01 sec)

Query OK, 0 rows affected (3.01 sec)

From the above results, the first now () and the second now () execution results are inconsistent. the now () function in the stored procedure is not taken from the execution start time of the stored procedure.

2. SQL statement test:

Mysql> select now (), sleep (3), now ();

+ --------------------- + ---------- + --------------------- +

| Now () | sleep (3) | now () |

+ --------------------- + ---------- + --------------------- +

| 11:25:45 | 0 | 11:25:45 |

+ --------------------- + ---------- + --------------------- +

1 row in set (3.00 sec)

Comment: now () function, the execution time of each query.

Mysql> select sysdate (), sleep (3), sysdate ();

+ --------------------- + ---------- + --------------------- +

| Sysdate () | sleep (3) | sysdate () |

+ --------------------- + ---------- + --------------------- +

| 11:26:44 | 0 | 11:26:47 |

+ --------------------- + ---------- + --------------------- +

1 row in set (3.02 sec)

Rating: sysdate () takes the actual time each time.

Based on the above results:

1. in most of the company's business, the now () function is enough, especially the time of the gmt_create and gmt_modified fields ()

2. sysdate () is used to obtain the time in real time when each field needs to be executed.

3. mysql fields are executed from left to right.

BitsCN.com