mysql的隐式的数据类型转换

mysql> select * from orders; +----+---------------------------+ | id | order_id | +----+---------------------------+ | 1 | 0300020140124034500255929 | | 2 | 0300020140124034500268504 | | 3 | 0300020140124034500648644 | | 4 | 0300020140124034500268XXX | | 5 | 0300020140124034500200000 | | 6 | 0300020140124034500800000 | +----+---------------------------+ 6 rows in set (0.00 sec) mysql> select * from orders where order_id=0300020140124034500255929; +----+---------------------------+ | id | order_id | +----+---------------------------+ | 1 | 0300020140124034500255929 | | 2 | 0300020140124034500268504 | | 3 | 0300020140124034500648644 | | 5 | 0300020140124034500200000 | | 6 | 0300020140124034500800000 | +----+---------------------------+ 5 rows in set, 1 warning (0.00 sec)

先看order表数据，order_id是varchar类型，执行select的查询条件没有加单引号，这时候查询出来6条结果，为什么会出来这么多结果，按上面的写法mysql会把上面的条件order_id=0300020140124034500255929看做数字，但是看做什么数字不是很清楚

mysql> show warnings; +---------+------+---------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '0300020140124034500268XXX' | +---------+------+---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from doubles; +----+----------------------+ | id | order_id | +----+----------------------+ | 1 | 3.000201401240345e23 | | 2 | 3.000201401240345e23 | | 3 | 3.000201401240345e23 | +----+----------------------+

从warning上来看是转为double了，把order_id的类型变为double发现存储的结果都变为科学计数法了，这样就找到原因了，经过mysql隐式转换的结果会有精度丢失，造成了上面的查询不准确

所以建议大家不要借助 MySQL 的隐式转换，程序严格按数据的本来类型来处理，会少很多不必要的麻烦。

查看更多关于mysql的隐式的数据类型转换的详细内容...