用between关键词的时候遇到的小坑,确切的说自己理解错了的小问题。
首先新建一个表:
CREATE TABLE `t_test_between` (
`id` int(11) NOT NULL,
`date1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`date2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
INSERT INTO `t_test_between` VALUES (1, '2023-10-07', '2023-10-08');
SET FOREIGN_KEY_CHECKS = 1;
然后我们得到一个很简单的表:
id | date1 | date2 |
---|---|---|
1 | 2023-10-07 | 2023-10-08 |
然后执行下面两个sql:
select * from t_test_between where '2023-10-08' BETWEEN date1 and date2;
select * from t_test_between where '2023-10-08 00:00:00.0' BETWEEN date1 and date2;
运行的结果是不一样的。。
结果1:
id | date1 | date2 |
---|---|---|
1 | 2023-10-07 | 2023-10-08 |
结果2:
id | date1 | date2 |
---|---|---|
排查原因的时候我一直纠结于 '2023-10-08'
和'2023-10-08 00:00:00.0'
转出来的时间戳应该是一模一样的,为什么一个会在两个时间之间一个没包含呢?
其实原因很简单,因为
where '2023-10-08' BETWEEN date1 and date2;
其中的date1,date2是varchar的值,sql会按照varchar对应的字典排序来决定顺序,而不是通过把’2023-10-08’转成时间和后面两个时间比较。
作为对比,我们新建一个表:t_test_between2
CREATE TABLE `t_test_between2` (
`id` int(11) NOT NULL,
`date1` datetime NULL DEFAULT NULL,
`date2` datetime NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;
INSERT INTO `t_test_between2` VALUES (1, '2023-10-07 00:00:00', '2023-10-08 00:00:00');
SET FOREIGN_KEY_CHECKS = 1;
一样我们得到了新的表:
id | date1 | date2 |
---|---|---|
1 | 2023-10-07 00:00:00 | 2023-10-08 00:00:00 |
同样执行下面两个sql:
select * from t_test_between2 where '2023-10-08' BETWEEN date1 and date2;
select * from t_test_between2 where '2023-10-08 00:00:00.0' BETWEEN date1 and date2;
这会结果就是一样的了。因为后面两个date都是datetime格式,所以**'2023-10-08'
** 会被转为日期类型,并具有时间部分为午夜的隐式值。因此,它等同于 '2023-10-08 00:00:00.0'
。
总结一下,在使用between
关键词的时候需要对查询范围的两个值的类型有判断,因为sql会执行一些隐式的类型转换,所以有可能会导致预期外的结果。