mysql - 使用 where id=201511051505538905 对于 char 类型字段查询的坑？

Question

用 char 类型存储大数字时，如果查询 sql 直接用 id=201511051505538905 这种不标准的方式查询时，会因为具体 id 值的不同，出现查询不到的情况。 正确做法是加上单引号'，便能正确查询到

请问背后的实质原因是什么？

Answer 1

Thanks to @fengyunzhanjin and @苏生Buhuo for their answers. I excerpted the following explanation from the mysql official website, which can answer my doubts very well:

Comparisons that use floating-point numbers (or values ​​that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent:
Example given on the official website As follows:

mysql> SELECT '18015376320243458' = 18015376320243458;
        -> 1
mysql> SELECT '18015376320243459' = 18015376320243459;
        -> 0

https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html

Answer 2

id=201511051505538905
Its data type is int
and the data type of your id is string. Of course it cannot be found, but you can convert it to int
cast(id as int)

Answer 3

where id=201511051505538905 id is of char type and if you use numbers to compare, mysql will convert the string into a number, which is 0 201511051505538905 != 0