mysql查询隐式类型转换_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 的隐式转换，程序严格按数据的本来类型来处理，会少很多不必要的麻烦。