MySQL between and 边界测试
2024-09-07 06:34:23
看到有一些博客说 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) 转换一下类型。
最新文章
- web前端的春天 or 噩梦
- 分区里的inode号是0号和1号的block
- 搭建LNAMP环境（六）- PHP7源码安装MongoDB和MongoDB拓展
- OVER 分析函数
- English Training Material - 03
- RPC、SQL、NFS属于OSI的哪一层
- Spark(Hive) SQL数据类型使用详解(Python)
- 用VS2013+VELT-0.1.4进行海思平台 Linux内核 的开发
- DirectX (13) 粒子系统
- 将控制台信息显示在前台页面的js插件
- 基于以太坊开发的类似58同城的DApp开发与应用案例
- javascript的介绍，实现和输出以及语法-javascript学习之旅（1）
- Service Worker
- spring boot项目基本结构
- Ubuntu 16.04交换Ctrl和Caps
- Codeforces Round #536 (Div. 2)--1106D - Lunar New Year and a Wander
- bzoj5421:收藏家
- Linux TCP/IP调优-Linux内核参数注释
- PHP断点调试工具Xdebug的安装
- vuex到底是个啥