MySQL SQL - 查询以查找字符串是否包含列中的部分值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29389293/
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
SQL - Query to find if a string contains part of the value in Column
提问by user2354254
I am trying to write a Query to find if a string contains part of the value in Column (Not to confuse with the query to find if a column contains part of a string). Say for example I have a column in a table with values ABC,XYZ. If I give search string ABCDEFG then I want the row with ABC to be displayed. If my search string is XYZDSDS then the row with value XYZ should be displayed
我正在尝试编写一个查询来查找字符串是否包含列中的部分值（不要与查询混淆以查找列是否包含字符串的一部分）。举例来说，我在表中有一个列，其值为 ABC、XYZ。如果我提供搜索字符串 ABCDEFG，那么我希望显示带有 ABC 的行。如果我的搜索字符串是 XYZDSDS，那么应该显示值为 XYZ 的行
回答by Turophile
The answer would be "use LIKE".
答案是“使用 LIKE”。
See the documentation: https://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
请参阅文档：https: //dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html
You can do WHERE 'string' LIKE CONCAT(column , '%')
你可以做 WHERE 'string' LIKE CONCAT(column , '%')
Thus the query becomes:
因此查询变为：
select * from t1 where 'ABCDEFG' LIKE CONCAT(column1,'%');
If you need to match anywhere in the string:
如果您需要匹配字符串中的任何位置：
select * from t1 where 'ABCDEFG' LIKE CONCAT('%',column1,'%');
Here you can see it working in a fiddle: http://sqlfiddle.com/#!9/d1596/4
回答by Aheho
Select * from table where @param like '%' + col + '%'
回答by Gordon Linoff
First, you appear to be storing lists of things in a column. This is the wrong approach to storing values in the database. You should have a junction table, with one row per entity and value -- that is, a separate row for ABCand XYZin your example. SQL has a great data structure for storing lists. It is called a "table", not a "string".
首先，您似乎将事物列表存储在列中。这是在数据库中存储值的错误方法。您应该有一个联结表，每个实体和值占一行——也就是说，在您的示例中为ABC和单独一行XYZ。SQL 有一个很好的数据结构来存储列表。它被称为“表”，而不是“字符串”。
If you are stuck with such a format and using MySQL, there is a function that can help:
如果您坚持使用这种格式并使用 MySQL，那么有一个功能可以提供帮助：
where find_in_set('ABC', col)
MySQL treats a comma delimited string as a "set" and offers this function. However, this function cannot use indexes, so it is not particularly efficient. Did I mention that you should use a junction table instead?
MySQL 将逗号分隔的字符串视为“集合”并提供此功能。但是这个函数不能使用索引，所以效率不是特别高。我有没有提到你应该使用联结表？
