MySQL mysql匹配字符串与表中字符串的开头
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9667243/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysql match string with start of string in table
提问by Woody
I realise that it would be a lot easier if I could modify the table when it was created, but assuming I can't, I have a table that is such as:
我意识到如果我可以在创建表时修改它会容易得多，但假设我不能，我有一个表，例如：
abcd
abde
abdf
abff
bbsdf
bcggs
... snip large amount
zza
The values in the table are not fixed length. I have a string to match such as abffagpokejfkjs . If it was the other way round, I could do
表中的值不是固定长度。我有一个要匹配的字符串，例如 abffagpokejfkjs 。如果反过来，我可以做
SELECT * from table where value like 'abff%'
but I need to select the value that matches the start of a string that is provided.
但我需要选择与提供的字符串开头匹配的值。
Is there a quick way of doing that, or does it need an itteration through the table to find a match?
有没有一种快速的方法可以做到这一点，还是需要通过表格进行迭代才能找到匹配项？
回答by Mark Byers
Try this:
尝试这个：
SELECT col1, col2 -- etc...
FROM your_table
WHERE 'abffagpokejfkjs' LIKE CONCAT(value, '%')
Note that this will not use an index effectively so it will be slow if you have a lot of records.
请注意，这不会有效地使用索引，因此如果您有很多记录，它会很慢。
Also note that some characters in value(e.g. %) may be interpreted by LIKE as having a special meaning, which may undesirable.
另请注意，value(eg %)中的某些字符可能会被 LIKE 解释为具有特殊含义，这可能是不受欢迎的。
回答by ToolmakerSteve
LIKEcan be avoided, by truncating the comparison string to each value's length:
LIKE可以通过将比较字符串截断为每个值的长度来避免：
... WHERE LEFT('abffagpokejfkjs', LENGTH(value)) = value
