MySQL timestamp 类型比较的测试

TIMESTAMP 类型字段与 CHAR 比较:

BEGIN
  #Routine body goes here...
  DECLARE rdToday CHAR(10) DEFAULT CURDATE();
  DECLARE rdBegin CHAR(19) DEFAULT CONCAT(rdToday, '00:00:00');
  DECLARE rdEnd CHAR(19) DEFAULT CONCAT(rdToday, '23:59:59');
  SELECT mdc_ordertime FROM mz_drugrecipe WHERE mdc_ordertime BETWEEN rdBegin AND rdEnd;
END

TIMESTAMP 类型字段与 TIMESTAMP 比较:

BEGIN
  #Routine body goes here...
  DECLARE rdBegin TIMESTAMP DEFAULT CURDATE();
  DECLARE rdEnd TIMESTAMP DEFAULT TIMESTAMPADD(SECOND, -1, DATE_ADD(CURDATE(),INTERVAL 1 DAY));
  -- 或:
  -- DECLARE rdToday CHAR(10) DEFAULT CURDATE();
  -- DECLARE rdBegin CHAR(19) DEFAULT CONCAT(rdToday, '00:00:00');
  -- DECLARE rdEnd CHAR(19) DEFAULT CONCAT(rdToday, '23:59:59');
  SELECT mdc_ordertime FROM mz_drugrecipe WHERE mdc_ordertime BETWEEN rdBegin AND rdEnd;
END

实验步骤:

都执行 100000 次,第一种 CHAR 变量与 TIMESTAMP 类型字段比较,花费时间40s+, 第二种 TIMESTAMP(不论通过哪种构造方式构造)变量与 TIMESTAMP 类型字段比较,花费时间 30s+.

结论:

应该将要比较的变量化为目标字段的类型 TIMESTAMP 后再进行比较.