选择与整数匹配的字符串开头数字 [英] Select matches beginning numbers of string on integer

问题描述

我有这张桌子:

CREATE TABLE `test` (
    `ID` int(11) NOT NULL auto_increment,
    `text` varchar(250) collate utf8_unicode_ci default NULL,
    PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

内容:

ID     text  
1      70-and-some-text  
70     blub

然后我执行此查询:

SELECT
    *
FROM
    test
WHERE
    ID = '70-and-some-text'
OR
    text = '70-and-some-text'

并得到两行结果.似乎MySQL尝试根据ID匹配尽可能多的数字，然后停止.我想我在某个地方读过，但找不到了.

and get both lines as a result. It seems that MySQL tries to match as many numbers against ID and then stops. I think I read that somewhere, but couldn't find it anymore.

非常感谢您的回答和提示.提前致谢.而且请客气，我是新手.告诉我，如果我做错了什么.我非常尊重你们.

Answer and hints are highly appreciated. Thanks in advance. And be kind, I'm new. Tell me, if I did something wrong. I have a lot of respect for you guys.

斯蒂芬

=================

==================

感谢Tomalak，我现在知道问题的根源.有解决此问题的最佳做法吗?我控制文本"，所以我可以使用"_70-some-text"或字符串.但是MySQL中的解决方案将是最纯净的".

Thanks to Tomalak, I know now where the problem comes from. Any best practices for dealing with this? I control the 'text', so I could go for '_70-and-some-text' or the the string with PHP. But a solution in MySQL would be the "purest".

推荐答案

此行

ID = '70-and-some-text'

使MySQL将右侧操作数转换为INT，因为该列的类型为INT.转换显然会产生数字70.您可以将任何数据类型以字符串形式传递到MySQL查询中，MySQL会透明地进行必要的类型转换，就像这种情况.

makes MySQL convert the right-hand-side operand to an INT, since the column is of type INT. conversion obviously results in the number 70. You can pass any data type into a MySQL query in form of a string, MySQL does the necessary type-conversion transparently, like in this case.

这篇关于选择与整数匹配的字符串开头数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！