SQL中使用between遇到的小坑

用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会执行一些隐式的类型转换，所以有可能会导致预期外的结果。