MySQL cache and date functions

问题:

I once read in a performance blog that it is better to use PHP's date functions to set dates in a MySQL query instead of using mysql date functions like curdate() because mysql can then cache the query or the result or something like that. Does anyone have any insight into this? Does it hold any water or is it baseless?

example:

$query = 'SELECT id FROM table WHERE publish_date = \''.date('Y-m-d').'\'';

vs

$query = 'SELECT id FROM table WHERE publish_date = CURDATE()';

回答1:

Any function containing CURDATE() will not be cached. Source

Hardcoding the date should still be cached as far as I can tell. Though you might want to consider using the prepare functionality instead of splicing strings into your query (for sanity and security sake).

回答2:

It's quite simple actually. The MySQL server does not see your PHP code so it'll receive one of these:

SELECT id FROM table WHERE publish_date = '2010-01-18';
SELECT id FROM table WHERE publish_date = CURDATE();

It will not read your intentions either. For MySQL, '2010-01-18' is a string and is deterministic: its value is always '2010-01-18'. However, CURDATE() is not deterministic: its value varies depending on the date when you run it. Thus the first one is cacheable and the second one is not.

回答3:

I personally preffer first way, because it give clear head about server time (time zone), my mysql server happend to be 10h earlier when promissed :)

localtime in your PHP script will apply in SQL