在 mySQL 中的整个表中搜索字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11939751/
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
Search a whole table in mySQL for a string
提问by VoltzRoad
I'm trying to search a whole table in mySQL for a string.
我正在尝试在 mySQL 中的整个表中搜索一个字符串。
I want to search all fields and all entrees of a table, returning each full entry that contains the specified text.
我想搜索表的所有字段和所有主条目，返回包含指定文本的每个完整条目。
I can't figure out how to search multiple fields easily; here are the details:
我不知道如何轻松搜索多个字段；以下是详细信息：
The table is "clients". It has about 30 fields and 800 entries, too much to show all at once in a browser. I would like to search for a name (i.e. "Mary"), but it could be in the shipping_name fieldor the billing_name field, or the emailfield, etc.
该表是“客户”。它有大约 30 个字段和 800 个条目，太多了，无法在浏览器中一次全部显示。我想搜索一个名字（即“玛丽”），但它可能在shipping_name field或billing_name field、 或email字段等中。
I would like to search all fields for any entries that contain the string "Mary". This is what I think should work but doesn't:
我想在所有字段中搜索包含字符串“Mary”的任何条目。这是我认为应该起作用但不起作用的方法：
SELECT * FROM `clients` IN 'Mary'
采纳答案by Artem Pyanykh
Try something like this:
尝试这样的事情：
SELECT * FROM clients WHERE CONCAT(field1, '', field2, '', fieldn) LIKE "%Mary%"
You may want to see SQL docs for additional information on string operators and regular expressions.
您可能希望查看 SQL 文档以获取有关字符串运算符和正则表达式的其他信息。
Edit: There may be some issues with NULL fields, so just in case you may want to use IFNULL(field_i, '')instead of just field_i
编辑：NULL 字段可能存在一些问题，所以以防万一您可能想要使用IFNULL(field_i, '')而不是仅仅使用field_i
Case sensitivity: You can use case insensitive collation or something like this:
区分大小写：您可以使用不区分大小写的排序规则或类似的东西：
... WHERE LOWER(CONCAT(...)) LIKE LOWER("%Mary%")
Just search all field: I believe there is no way to make an SQL-query that will search through all field without explicitly declaring field to search in. The reason is there is a theory of relational databases and strict rules for manipulating relational data (something like relational algebra or codd algebra; these are what SQL is from), and theory doesn't allow things such as "just search all fields". Of course actual behaviour depends on vendor's concrete realisation. But in common case it is not possible. To make sure, check SELECToperator syntax (WHEREsection, to be precise).
只搜索所有字段：我相信没有办法让 SQL 查询在没有明确声明要搜索的字段的情况下搜索所有字段。原因是存在关系数据库理论和操作关系数据的严格规则（某些东西）像关系代数或 codd 代数；这些是 SQL 的来源），理论不允许诸如“只搜索所有字段”之类的事情。当然，实际行为取决于供应商的具体实现。但在一般情况下，这是不可能的。为了确保，检查SELECT运算符语法（WHERE节，准确地说）。
回答by Francis P
Identify all the fields that could be related to your search and then use a query like:
确定可能与您的搜索相关的所有字段，然后使用如下查询：
SELECT * FROM clients
WHERE field1 LIKE '%Mary%'
OR field2 LIKE '%Mary%'
OR field3 LIKE '%Mary%'
OR field4 LIKE '%Mary%'
....
(do that for each field you want to check)
Using LIKE '%Mary%'instead of = 'Mary'will look for the fields that contains someCaracters + 'Mary' + someCaracters.
使用LIKE '%Mary%'代替= 'Mary'将查找包含 someCaracters + 'Mary' + someCaracters 的字段。
回答by Soner
In addition to pattern matchingwith 'like' keyword. You can also perform search by using fulltextfeature as below;
除了使用“like”关键字进行模式匹配之外。您还可以使用全文功能进行搜索，如下所示；
SELECT * FROM clients WHERE MATCH (shipping_name, billing_name, email) AGAINST ('mary')
回答by gonzo
If you are just looking for some text and don't need a result set for programming purposes, you could install HeidiSQLfor free (I'm using v9.2.0.4947).
如果您只是在寻找一些文本并且不需要用于编程目的的结果集，您可以免费安装 HeidiSQL（我使用的是 v9.2.0.4947）。
Right click any database or table and select "Find text on server".
右键单击任何数据库或表，然后选择“在服务器上查找文本”。
All the matches are shown in a separate tab for each table - very nice.
所有匹配项都显示在每个表的单独选项卡中 - 非常好。
Frighteningly useful and saved me hours. Forget messing about with lengthy queries!!
非常有用，节省了我几个小时。忘记搞乱冗长的查询！！
回答by Arpan Dubey
A PHP Based Solution for search entire table ! Search string is $string . This is generic and will work with all the tables with any number of fields
用于搜索整个表格的基于 PHP 的解决方案！搜索字符串是 $string 。这是通用的，适用于具有任意数量字段的所有表
$sql="SELECT * from client_wireless";
$sql_query=mysql_query($sql);
$logicStr="WHERE ";
$count=mysql_num_fields($sql_query);
for($i=0 ; $i < mysql_num_fields($sql_query) ; $i++){
if($i == ($count-1) )
$logicStr=$logicStr."".mysql_field_name($sql_query,$i)." LIKE '%".$string."%' ";
else
$logicStr=$logicStr."".mysql_field_name($sql_query,$i)." LIKE '%".$string."%' OR ";
}
// start the search in all the fields and when a match is found, go on printing it .
$sql="SELECT * from client_wireless ".$logicStr;
//echo $sql;
$query=mysql_query($sql);
回答by James Smith
If you're using Sublime, you can easily generate hundreds or thousands of lines using Text Pastryin conjunction with multiple line selection and Emmet.
如果您使用 Sublime，您可以使用Text Pastry结合多行选择和Emmet轻松生成数百或数千行。
So in my case I set the document type to html, then typed div*249, hit tab and Emmet creates 249 empty divs. Then using multiple selection I typed col_id_in each one and triggered Text Pastry to insert an incremental id number. Then with multiple selection again you can delete the divmarkup and replace it with the MySQL syntax.
所以在我的例子中，我将文档类型设置为 html，然后输入div*249，点击选项卡，Emmet 创建 249 个空 div。然后使用多项选择，我输入col_id_每个选项并触发 Text Pastry 插入一个增量 ID 号。然后再次进行多项选择，您可以删除div标记并将其替换为 MySQL 语法。
回答by user7957410
for specific requirement the following will work for search:
对于特定要求，以下内容适用于搜索：
select * from table_name where (column_name1='%var1%' or column_name2='var2' or column_name='%var3%') and column_name='var';
if you want to query for searching data from the database this will work perfectly.
如果您想从数据库中查询搜索数据，这将非常有效。
回答by user7957410
Try this code,
试试这个代码，
SELECT
*
FROM
`customers`
WHERE
(
CONVERT
(`customer_code` USING utf8mb4) LIKE '%Mary%'
OR
CONVERT(`customer_name` USING utf8mb4) LIKE '%Mary%'
OR
CONVERT(`email_id` USING utf8mb4) LIKE '%Mary%'
OR
CONVERT(`address1` USING utf8mb4) LIKE '%Mary%'
OR
CONVERT(`report_sorting` USING utf8mb4) LIKE '%Mary%'
)
This is help to solve your problem mysql version 5.7.21
这有助于解决您的问题 mysql version 5.7.21
