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 后再进行比较.
