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

初始化数据：

## 创建测试表
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)

上面测试中发现：

1、强制走不同索引会导致查询结果不同(两次查询过程中无任何数据变化，使用RR事务隔离级别)