問題描述

一個簡單的表格:

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'

當我執(zhí)行下面的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;

如果我將數(shù)字更改為字符串類型，如預期的那樣，只會返回 1 條記錄:

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

select * from tbl_type_test where uid = '6011656821812318924';

我認為查詢中的數(shù)字類型和長度是原因，但我不知道確切的原因.

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

任何評論將不勝感激.

推薦答案

在所有其他情況下，參數(shù)作為浮點(實數(shù))數(shù)進行比較. - 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)

這篇關于MySQL在使用長類型數(shù)字過濾varchar類型時返回額外記錄的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！