mysql where match_mysql where exact match

最新推荐文章于 2023-03-06 20:37:47 发布

weixin_39601657

最新推荐文章于 2023-03-06 20:37:47 发布

阅读量192

点赞数

文章标签： mysql where match

本文链接：https://blog.csdn.net/weixin_39601657/article/details/113470418

版权

在 MySQL 查询中，当尝试使用字符串与 INT 字段进行精确匹配时，可能会遇到类型转换问题导致不预期的结果。问题在于 MySQL 会尝试将字符串转换为整数进行比较。解决方法包括显式将 ID 转换为字符串或者使用 LIKE 操作符进行模糊匹配。确保在查询前验证输入的 ID 是否为纯数字，避免混合数字和字母的比较，以保证 INT 字段的精确匹配。

摘要由CSDN通过智能技术生成

问题

When i write this query

SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id = 260;

return

title city

--------------- ------

Butterfly world Mohali

and when i write

SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id = '260abcxyz';

return

title city

--------------- ------

Butterfly world Mohali

see the second one is wrong. i want to get the value only IF ID EXACTLY MATCH ... how to solve this. thanks for your help.

回答1:

You could convert the id to a string so the comparison will be done exactly. You could use LIKE to cause an implicit conversion

SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id LIKE '260abcxyz';

or alternative, you can perform the cast explicitly

SELECT cd.title, cd.city FROM coupon_detail cd WHERE CAST(cd.id AS CHAR) = '260abcxyz';

However, if all your IDs are integers, it's probably more appropriate to check these values before you try to query the database. (If the ID you're querying with isn't a number, there can't be a match anyway.)

回答2:

This is happening because of Type Conversion in MySql. My sql treats '260abcxyz' as an integer in your query and AFAIK because first char is number MySql casts it to a number and it becomes only 260 and that's why you are getting result. If you write character first like 'abcxyz260' it will successes the comparison.

More explanation available here: Type Conversion in MySql

As a solution: you should take care that only numbers are passed in comparison and not combination of string and number.

回答3:

Well, you should not use quote if the id is INT. The way MySQL does is if you use String to compare a INT, it uses just the valid initial integer from start.

see this:

mysql> desc coupon_detail;

+-------+--------------+------+-----+---------+-------+

+-------+--------------+------+-----+---------+-------+

| id | int(11) | YES | | NULL | |

+-------+--------------+------+-----+---------+-------+

3 rows in set (0.04 sec)

mysql> SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id = 'ff260';

Empty set, 1 warning (0.00 sec)

mysql> SELECT cd.title, cd.city FROM coupon_detail cd WHERE cd.id = '260ff';

+-----------------+--------+

| title | city |