Mysql处理varchar作为int错误？ [英] Mysql treating varchar as int bug?

问题描述

我只是运行这个查询：

UPDATE mytable SET mycol='text' WHERE mycol=0;

重点是列 mycol a varchar ，但我还是作为一个 int 。
令我惊讶的是，where子句在表的所有行上都为true，为空。

The point being that the column mycol is a varchar, yet i treated is as an int as well. To my surprise, the where clause evaluated to true on ALL rows of the table, empty or not.

因此，为什么非空字符串比较等于一个整数零？

So, why does a nonempty string compare equal to an integer zero?

推荐答案

这是MySQL中非常有据可查的行为。

This is very well documented behavior in MySQL.

当在调用数字的上下文中遇到字符串时，MySQL将字符串转换为数字，以字符串开头的数字开头。当没有数字时停止。因此，没有数字的字符串变为0。

When a string is encountered in a context that calls for a number, MySQL converts the string to a number, starting with digits at the beginning of the string. It stops when there are no digits. So, a string with no digits becomes 0.

如果要进行正确的比较，请使用单引号：

If you want a proper comparison, use single quotes:

UPDATE mytable SET mycol='text' WHERE mycol = '0';

这里是确切的报价：

到数字上下文中的数字值，通常
除了使用字符串值为
之外，不必进行任何操作，尽管它是一个数字：

To cast a string to a numeric value in numeric context, you normally do not have to do anything other than to use the string value as though it were a number:

mysql> SELECT 1 +'1';
- > 2

mysql> SELECT 1+'1'; -> 2

这篇关于Mysql处理varchar作为int错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！