从int列查询字符串? [英] Querying a string from int column?

问题描述

我有一张桌子:

CREATE TABLE `ids` (
    id int(11) not null auto_increment,
    PRIMARY KEY (id)
);

它包含一些ID:111、112、113、114等.

It contains some IDs: 111, 112, 113, 114 etc.

我进行了查询:

SELECT * FROM `ids` WHERE id = '112abcdefg'

我什么都没想到，但是得到的结果是ID为112的行.似乎MySQL悄悄地将我的字符串转换为整数，然后将其与列值进行比较.

I expected nothing but I've got a result, a row with ID of 112. Seems that MySQL quietly converted my string to integer and then compared it against column values.

如何更改查询，以便从id列查询相同的字符串不会产生预期的结果? MySQL中有严格的比较修饰符吗?

How can I change the query so that querying the same string from id column will give no results as I expect? Is there a strict comparison modifier in MySQL?

推荐答案

一种选择是将CAST从112到CHAR以获得正确的匹配:

One option is to CAST the 112 to CHAR to get a proper match:

WHERE CAST(id AS CHAR(12)) = '112abcdefg'

CHAR中的12是猜测；对于您最大的id，它应该足够大.

The 12 in CHAR is a guess; it should be large enough for your biggest id.

这可能会扼杀优化的任何机会，因此另一种选择(尽管我不确定100％可以肯定)是使用BINARY比较.我已经尝试了一些不同的值，并且有效:

That will probably kill any chance of optimization, so another option (though one I'm not 100% sure of) is to use a BINARY comparison. I've tried this with a few different values and it works:

WHERE BINARY id = '112abcdefg'

这篇关于从int列查询字符串?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！