选择与整数匹配的字符串开头数字 [英] 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屋!