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;

然后我们得到一个很简单的表：

然后执行下面两个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：

结果2:

排查缘由的时候我一直纠结于 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;

一样我们得到了新的表：

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