Mysql Like +通配符vs等于运算符 [英] Mysql Like + Wild Card vs Equals Operator

问题描述

我最近刚刚修复了一些代码中的错误，并希望有人可以向我解释错误发生的原因.

I recently just fixed a bug in some of my code and was hoping someone could explain to me why the bug occurred.

我有这样一个查询:

SELECT * FROM my_table WHERE my_field=13

出乎意料的是，这返回的行中my_field等于13或13a.修复很简单，我将查询更改为:

Unexpectedly, this was returning rows where my_field was equal to either 13 or 13a. The fix was simple, I changed the query to:

SELECT * FROM my_table WHERE my_field='13'

我的问题是，应该是这样吗?我一直以为要返回类似的字段，您可以使用类似以下内容的方法:

My question is, is this supposed to be the case? I've always thought that to return a similar field, you would use something like:

SELECT * FROM my_table WHERE my_field LIKE '13%'

LIKE +通配符与没有引号的equals运算符有什么区别?

What is the difference between LIKE + a Wild Card vs an equals operator with no quotes?

推荐答案

此语句返回my_field = '13a'的行:

SELECT * FROM my_table WHERE my_field=13

因为MySQL在比较期间执行了从字符串到数字的类型转换，将'13a'转换为13. 此文档页面中的更多内容.

Because MySQL performs type conversion from string to number during the comparison, turning '13a' to 13. More on that in this documentation page.

添加引号会将整数转换为字符串，因此MySQL仅执行字符串比较.显然，'13'不能等于'13a'.

Adding quotes turns the integer to a string, so MySQL only performs string comparison. Obviously, '13' cannot be equal to '13a'.

LIKE子句始终执行字符串比较(除非其中一个操作数为NULL，在这种情况下结果为NULL).

The LIKE clause always performs string comparison (unless either one of the operands is NULL, in which case the result is NULL).

这篇关于Mysql Like +通配符vs等于运算符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！