MySQL在使用长类型数字过滤varchar类型时返回额外记录

2021-09-16 00:00:00 precision mysql bigint varchar

一个简单的表格:

CREATE TABLE `tbl_type_test` (  
  `uid` varchar(31) NOT NULL DEFAULT '0',
  `value` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

和一些记录:

'6011656821812318924', 'a'
'6011656821812318925', 'b'
'6011656821812318926', 'c'

当我执行下面的SQL时，会返回3条记录

when I execute the following SQL, 3 records will return

select * from tbl_type_test where uid = 6011656821812318924;

这也将返回 3 条记录.奇怪.

and this will return 3 records, too. Weird.

select * from tbl_type_test where uid = 6011656821812318900;

如果我将数字更改为字符串类型，如预期的那样，只会返回 1 条记录:

if I change the number to string type, as expected, only 1 record will return:

select * from tbl_type_test where uid = '6011656821812318924';

我认为查询中的数字类型和长度是原因，但我不知道确切的原因.

I think the number type and length in the query is the reason, but I don't known the exact.

任何评论将不胜感激.

推荐答案

在所有其他情况下，参数作为浮点(实数)数进行比较. - https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

例如

drop procedure if exists p; delimiter $$ create procedure p (inval float, inval2 float, inval3 float) select inval,inval2,inval3; call p(6011656821812318924,6011656821812318925,6011656821812318926); +------------+------------+------------+ | inval | inval2 | inval3 | +------------+------------+------------+ | 6.01166e18 | 6.01166e18 | 6.01166e18 | +------------+------------+------------+ 1 row in set (0.00 sec)

相关文章