MYSQL 日期计算函数 : DATE_ADD vs simple + INTERVAL - what's the difference?

Question

如果这是一个重复的问题，请原谅我，但我无法在这里或其他任何地方找到任何答案。

我已经习惯于使用函数 DATE_ADD、DATE_SUB 等在 MYSQL 中计算日期，详见此处:https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html .

但是我在 another answer 中看到可以简单地使用诸如 CURDATE() + INTERVAL 1 DAY 之类的代码。

以下测试产生了两行相同的结果。

SELECT 
  CURDATE() AS Today,
  CURDATE() + INTERVAL 5 DAY AS 5_Days,
  CURDATE() - INTERVAL 3 MONTH AS 3_Months_Ago,
  CURDATE() + INTERVAL 1 YEAR AS 1_Year
UNION ALL SELECT 
  CURDATE() AS Today,
  DATE_ADD(CURDATE(), INTERVAL 5 DAY) AS 5_Days,
  DATE_SUB(CURDATE(), INTERVAL 3 MONTH) AS 3_Months_Ago,
  DATE_ADD(CURDATE(), INTERVAL 1 YEAR) AS 1_Year

我的问题是:这两种方法有什么区别吗？哪个比另一个好？ IMO，不使用 DATE_ADD 函数提高了可读性和简单性，但我可能错了!

Answer 1

如您所见，两种变体的执行方式相同...

EXPLAIN EXTENDED
SELECT * FROM weddings WHERE CURDATE()-INTERVAL 40 YEAR > dob;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | weddings | ALL  | dob           | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

SHOW WARNINGS;
select test.weddings.id AS id
     , test.weddings.name AS name
     , test.weddings.gender AS gender
     , test.weddings.dob AS dob
     , test.weddings.birthplace AS birthplace
     , test.weddings.wedding_date AS wedding_date 
  from test.weddings 
 where ((curdate() - interval 40 year) > test.weddings.dob)

EXPLAIN EXTENDED
SELECT * FROM weddings WHERE DATE_SUB(CURDATE(),INTERVAL 40 YEAR) > dob;
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | weddings | ALL  | dob           | NULL | NULL    | NULL |   10 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

SHOW WARNINGS;

select test.weddings.id AS id
     , test.weddings.name AS name
     , test.weddings.gender AS gender
     , test.weddings.dob AS dob
     , test.weddings.birthplace AS birthplace
     , test.weddings.wedding_date AS wedding_date 
  from test.weddings 
 where ((curdate() - interval 40 year) > test.weddings.dob) 

FWIW，我同意你的看法。