MySQL DataType--当整数列遇到小数

最新推荐文章于 2025-01-07 11:55:58 发布

weixin_30539625

最新推荐文章于 2025-01-07 11:55:58 发布

阅读量217

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/gaogao67/p/11010412.html

本文通过一系列SQL查询测试，揭示了整数类型字段在进行浮点数比较时的潜在问题，展示了不同查询条件如何影响结果集，并对比了使用不同索引时的查询结果差异。

初始化数据：

## 创建测试表
CREATE TABLE `tb002` (
  `c1` int(11) NOT NULL AUTO_INCREMENT,
  `c2` int(11) DEFAULT NULL,
  `c3` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`c1`),
  KEY `IDX_C2` (`c2`)
);

## 插入测试数据
insert into tb002(c2,c3) select 1428,'1428';
insert into tb002(c2,c3) select 1429,'1429';
insert into tb002(c2,c3) select 1430,'1430';

查看当前表中数据：

## 查看表中数据
SELECT * FROM tb002;

+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  1 | 1428 | 1428 |
|  2 | 1429 | 1429 |
|  3 | 1430 | 1430 |
+----+------+------+

测试1：

## 测试1
select * from tb002 where c2=1429.5;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  3 | 1430 | 1430 |
+----+------+------+


## 测试2
select * from tb002 where c2<1429.5;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  1 | 1428 | 1428 |
|  2 | 1429 | 1429 |
+----+------+------+


## 测试3
select * from tb002 where c2<=1429.5;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  1 | 1428 | 1428 |
|  2 | 1429 | 1429 |
+----+------+------+


## 测试4:
select * from tb002 where c2=1429.5 or c2<1429.5;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  1 | 1428 | 1428 |
|  2 | 1429 | 1429 |
+----+------+------+


## 测试5:
select * from tb002 where c2=1429.5 union select * from tb002 where c2<1429.5;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  3 | 1430 | 1430 |
|  1 | 1428 | 1428 |
|  2 | 1429 | 1429 |
+----+------+------+

上面测试中发现：

1、where c2=1429.5 or c2<1429.5 与 where c2<=1429.5 和 where c2=1429.5查询结果相同

2、where c2<=1429.5的查询结果中不包含where c2=1429.5的查询结果

测试2：

## 查询1
select * from tb002 force index(IDX_C2) where c2=1429.5;
+----+------+------+
| c1 | c2   | c3   |
+----+------+------+
|  3 | 1430 | 1430 |
+----+------+------+


## 查询2
select * from tb002 force index(primary) where c2=1429.5;
Empty set (0.00 sec)