为什么当值大于一定数量的数字时，结果会有所不同

Question

我很抱歉，在尝试所有情况之前我匆忙发送了出去，并向所有人道歉！按照你的回答，我试图推翻我以前的想法。随着数字的变化，我感到非常困惑。我还没有测试过有多少数字是正常的，但我相信这个例子应该能够说明。只返回与要查询的位数相同的结果，但这里有两种情况。其他数字相同吗？我还没有一个接一个地试过。

CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(20) CHARACTER SET latin1 DEFAULT NULL,
`simid` varchar(50) CHARACTER SET latin1 NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
INSERT INTO `user` (`id`, `name`, `simuid`)
VALUES
(1, 'ccc', '6441760021201308940'),
(3, 'bb', '6441760021201308941'),
(4, 'bb', '6441760021201308942');

select * from user where simuid = 6441760021201308940;
result：
1 ccc 6441760021201308940
3 bb 6441760021201308941
4 bb 6441760021201308942

but ：select * from user where simuid = '6441760021201308940';
result：
1 ccc 6441760021201308940
mysql> select * from user;
+------+------+------------------------+
| id   | name | simid                  |
+------+------+------------------------+
|    5 | ccc  | 6441760021201307159    |
|    6 | ccc  | 6441760021201307160    |
|    7 | ccc  | 6441760021201307001    |
|    8 | ccc  | 6441760021201307000    |
|    9 | qqq  | 6441760021201308159    |
|   10 | sss  | 6441760021201308160    |
|   12 | jjj  | 641760021201308162     |
|   13 | sss  | 6441760021201308161    |
|   14 | jjj  | 6417600212013081620    |
|   15 | sss  | 64417600212013081610   |
|   21 | zzz  | 6441760021201308970    |
|   51 | ccc  | 6441760021201308940    |
|   52 | ddd  | 6441760021201308945    |
|   53 | bb   | 6441760021201308941    |
|   54 | bb   | 6441760021201308942    |
|   55 | eee  | 6441760021201308946    |
|  100 | sss  | 6441760021201308161000 |
|  101 | jjj  | 641760021201308162000  |
|  200 | sss  | 6441760021201308168000 |
|  201 | jjj  | 6441760021201308169000 |
| 1000 | aaa  | 64410                  |
| 1001 | bbb  | 64411                  |
+------+------+------------------------+
22 rows in set (0.00 sec)
select * from user where simid = 6441760021201307159;
+----+------+---------------------+
| id | name | simid               |
+----+------+---------------------+
|  5 | ccc  | 6441760021201307159 |
|  6 | ccc  | 6441760021201307160 |
|  9 | qqq  | 6441760021201308159 |
| 10 | sss  | 6441760021201308160 |
+----+------+---------------------+
mysql> select * from user where simid = 6441760021201308160000;
+-----+------+------------------------+
| id  | name | simid                  |
+-----+------+------------------------+
| 100 | sss  | 6441760021201308161000 |
| 200 | sss  | 6441760021201308168000 |
| 201 | jjj  | 6441760021201308169000 |
+-----+------+------------------------+
3 rows in set (0.01 sec)
mysql> select * from user where simid = 6441760021201308160;
+----+------+---------------------+
| id | name | simid               |
+----+------+---------------------+
|  5 | ccc  | 6441760021201307159 |
|  6 | ccc  | 6441760021201307160 |
|  9 | qqq  | 6441760021201308159 |
| 10 | sss  | 6441760021201308160 |
+----+------+---------------------+
4 rows in set (0.01 sec)
mysql> select * from user where simid = 6441760021201308161;
+----+------+---------------------+
| id | name | simid               |
+----+------+---------------------+
| 13 | sss  | 6441760021201308161 |
| 21 | zzz  | 6441760021201308970 |
| 51 | ccc  | 6441760021201308940 |
| 52 | ddd  | 6441760021201308945 |
| 53 | bb   | 6441760021201308941 |
| 54 | bb   | 6441760021201308942 |
| 55 | eee  | 6441760021201308946 |
+----+------+---------------------+
7 rows in set (0.00 sec)

Answer 1

您正在尝试将名为"simuid"的列(varchar(与一个数字(即6441760021201308940(进行比较。这可能会导致这种意想不到的行为。如果要将数字与varchar进行比较，则必须首先将simuid列值强制转换为数字，即UNSIGNED 64位类型。示例：

select * from user where CAST(simuid AS UNSIGNED) = 6441760021201308940;

或者更好的是，由于UNSIGNED类型长度的限制：

select * from user where simuid = CAST(6441760021201308940 AS char);

Answer 2

这是因为当你进行数字比较时，你实际上是在进行

select * from user where stringtoint(simuid) = 6441760021201308940

但是数字数据类型没有足够的精度来存储与您的值一样大的值。因此，真正发生的事情更像

select * from user where truncate(stringtoint(simuid)) = truncate(6441760021201308940)

因此，所有与您要查找的数字大致相同的数字都将被截断为相同的值，因此被认为是相等的