MySQL between and 边界测试

看到有一些博客说 between and 有不含边界的情况，于是测试了一把，记录如下。先说结论：between and 不存在不含边界的情况

1. between and 是包含边界的

id between 3 and 5 等价于 id >=3 and <=5;

mysql> select * from t1;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | siri01 |  200 |
|  2 | siri02 |   30 |
|  3 | siri20 |   40 |
|  4 | siri31 |   22 |
|  5 | siri04 |   60 |
|  7 | siri30 |   80 |
|  8 | siri43 |  100 |
|  9 | siri40 |   25 |
| 10 | siri15 |   20 |
| 15 | siri06 |  100 |
| 20 | siri89 |  400 |
+----+--------+------+
mysql> select * from t1 where id between 3 and 5;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  3 | siri20 |   40 |
|  4 | siri31 |   22 |
|  5 | siri04 |   60 |
+----+--------+------+
3 rows in set (0.00 sec)
mysql> select * from t1 where id not between 3 and 5;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | siri01 |  200 |
|  2 | siri02 |   30 |
|  7 | siri30 |   80 |
|  8 | siri43 |  100 |
|  9 | siri40 |   25 |
| 10 | siri15 |   20 |
| 15 | siri06 |  100 |
| 20 | siri89 |  400 |
+----+--------+------+
8 rows in set (0.00 sec)

2. between and ，datetime 类型的字段

mysql> create table t3(id bigint primary key auto_increment,date datetime);
mysql> select * from t3;
+----+---------------------+
| id | date                |
+----+---------------------+
|  1 | 2021-06-16 03:00:00 |
|  2 | 2021-06-15 00:00:00 |
|  3 | 2021-06-14 00:00:00 |
|  4 | 2021-06-13 01:00:00 |
|  5 | 2021-06-12 09:00:00 |
+----+---------------------+
mysql> select * from t3 where date between '2021-06-13' and '2021-06-15';
+----+---------------------+
| id | date                |
+----+---------------------+
|  2 | 2021-06-15 00:00:00 |
|  3 | 2021-06-14 00:00:00 |
|  4 | 2021-06-13 01:00:00 |
+----+---------------------+
MySQL 对日期的查询默认是 00:00:00, 上面的查询实际上是  between '2021-06-13 00:00:00' and '2021-06-15 00:00:00'
 
mysql> select * from t3 where date between '2021-06-13' and '2021-06-16';
+----+---------------------+
| id | date                |
+----+---------------------+
|  2 | 2021-06-15 00:00:00 |
|  3 | 2021-06-14 00:00:00 |
|  4 | 2021-06-13 01:00:00 |
+----+---------------------+
 
虽然有一条 2021-06-16 的记录，但是不满足 00:00:00 的查询条件。并不是不包含边界。这里容易造成一种不含边界的错觉。
像 datatinme 的字段查询，查询日期应 cast('2021-06-13' data) 转换一下类型。