blocks|key|499584|text|确定可能与您的搜索相关的所有字段，然后使用如下查询：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|499585|SELECT+*+FROM+clients
WHERE+field1+LIKE+'%25Mary%25'
+++OR+field2+LIKE+'%25Mary%25'
+++OR+field3+LIKE+'%25Mary%25'
+++OR+field4+LIKE+'%25Mary%25'
+++....
+++(do+that+for+each+field+you+want+to+check)|code-block|syntax|javascript|499586|使用LIKE+'%25Mary%25'而不是=+'Mary'将查找包含someCaracters+%2B+'Mary‘%2B+someCaracters的字段。|offset|length|style|CODE|499587|entityMap^0|0|0|2|D|I|8|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]|$I|T|J|U|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|N|$]]

Identify all the fields that could be related to your search and then use a query like:

<pre><code>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)
</code></pre>

Using <code>LIKE '%Mary%'</code> instead of <code>= 'Mary'</code> will look for the fields that contains someCaracters + 'Mary' + someCaracters.

blocks|key|283101|text|除了带有“like”关键字的pattern+matching之外。您也可以使用fulltext功能进行搜索，如下所示；|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|283102|SELECT+*+FROM+clients+WHERE+MATCH+(shipping_name,+billing_name,+email)+AGAINST+('mary')|code-block|syntax|javascript|283103|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html|1|http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html^0|E|G|0|13|8|1|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]|$A|W|B|X|1|Y]]|C|$]]|$1|D|3|E|5|F|7|Z|8|@]|9|@]|C|$G|H]]|$1|I|3|-4|5|6|7|10|8|@]|9|@]|C|$]]]|J|$K|$5|L|M|N|C|$O|P]]|Q|$5|L|M|N|C|$O|R]]]]

In addition to <a href="http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html" rel="noreferrer">pattern matching</a> with 'like' keyword. You can also perform search
by using <a href="http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html" rel="noreferrer">fulltext</a> feature as below;

<pre><code>SELECT * FROM clients WHERE MATCH (shipping_name, billing_name, email) AGAINST ('mary')
</code></pre>

blocks|key|283179|text|如果您只是在寻找一些文本，并且不需要用于编程的结果集，那么您可以免费使用install+HeidiSQL+(我使用的是v9.2.0.4947)。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|283180|右键单击任意数据库或表，然后选择“在服务器上查找文本”。|283181|所有的匹配都显示在每个表的单独选项卡中-非常好。|283182|非常有用，为我节省了几个小时。忘掉冗长的查询吧！|283183|entityMap|0|LINK|mutability|MUTABLE|url|http://www.heidisql.com/^0|10|G|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]]|C|$]]|$1|D|3|E|5|6|7|V|8|@]|9|@]|C|$]]|$1|F|3|G|5|6|7|W|8|@]|9|@]|C|$]]|$1|H|3|I|5|6|7|X|8|@]|9|@]|C|$]]|$1|J|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|K|$L|$5|M|N|O|C|$P|Q]]]]

If you are just looking for some text and don't need a result set for programming purposes, you could <a href="http://www.heidisql.com/" rel="noreferrer">install HeidiSQL</a> for free (I'm using 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!!

blocks|key|282915|text|一个基于PHP的搜索整个表的解决方案！搜索字符串为$string。这是通用的，适用于具有任意数量字段的所有表|type|unstyled|depth|inlineStyleRanges|entityRanges|data|282916|$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%2B%2B){
+if($i+==+($count-1)+)
$logicStr=$logicStr."".mysql_field_name($sql_query,$i)."+LIKE+'%25".$string."%25'+";
else
$logicStr=$logicStr."".mysql_field_name($sql_query,$i)."+LIKE+'%25".$string."%25'+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);|code-block|syntax|javascript|282917|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>$sql="SELECT * from client_wireless";
$sql_query=mysql_query($sql);
$logicStr="WHERE ";
$count=mysql_num_fields($sql_query);
for($i=0 ; $i &lt; 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);
</code></pre>

blocks|key|499766|text|如果您正在使用Sublime，您可以结合使用Text+Pastry和多行选择和Emmet轻松地生成成百上千行。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|499767|因此，在我的示例中，我将文档类型设置为html，然后输入div*249，然后按tab，Emmet创建了249个空div。然后，使用多项选择，我在每个选项中键入col_id_，并触发Text+Pastry以插入一个递增的id号。然后，通过再次选择多个选项，您可以删除div标记并用MySQL语法替换它。|style|CODE|499768|entityMap|0|LINK|mutability|MUTABLE|url|https://github.com/duydao/Text-Pastry#number-sequence|1|https://github.com/sergeche/emmet-sublime^0|M|B|0|13|5|1|0|S|7|27|7|3O|3|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@$A|S|B|T|1|U]|$A|V|B|W|1|X]]|C|$]]|$1|D|3|E|5|6|7|Y|8|@$A|Z|B|10|F|G]|$A|11|B|12|F|G]|$A|13|B|14|F|G]]|9|@]|C|$]]|$1|H|3|-4|5|6|7|15|8|@]|9|@]|C|$]]]|I|$J|$5|K|L|M|C|$N|O]]|P|$5|K|L|M|C|$N|Q]]]]

If you're using Sublime, you can easily generate hundreds or thousands of lines using <a href="https://github.com/duydao/Text-Pastry#number-sequence" rel="nofollow">Text Pastry</a> in conjunction with multiple line selection and <a href="https://github.com/sergeche/emmet-sublime" rel="nofollow">Emmet</a>.

So in my case I set the document type to html, then typed <code>div*249</code>, hit tab and Emmet creates 249 empty divs. Then using multiple selection I typed <code>col_id_</code> in each one and triggered Text Pastry to insert an incremental id number. Then with multiple selection again you can delete the <code>div</code> markup and replace it with the MySQL syntax.

blocks|key|283268|text|对于特定的要求，以下内容将适用于搜索：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|283269|select+*+from+table_name+where+(column_name1='%25var1%25'+or+column_name2='var2'+or+column_name='%25var3%25')+and+column_name='var';|code-block|syntax|javascript|283270|如果你想从数据库中查询搜索数据，这将是完美的。|283271|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

for specific requirement the following will work for search:

<pre><code>select * from table_name where (column_name1='%var1%' or column_name2='var2' or column_name='%var3%') and column_name='var';
</code></pre>

if you want to query for searching data from the database this will work perfectly.

blocks|key|283310|text|试试这段代码，|type|unstyled|depth|inlineStyleRanges|entityRanges|data|283311|SELECT+
+++++++*+
FROM+
+++++++`customers`+
WHERE+
+++++++(
++++++++++CONVERT+
+++++++++++++++(`customer_code`+USING+utf8mb4)+LIKE+'%25Mary%25'+
++++++++++OR+
++++++++++CONVERT(`customer_name`+USING+utf8mb4)+LIKE+'%25Mary%25'+
++++++++++OR+
++++++++++CONVERT(`email_id`+USING+utf8mb4)+LIKE+'%25Mary%25'+
++++++++++OR+
++++++++++CONVERT(`address1`+USING+utf8mb4)+LIKE+'%25Mary%25'+
++++++++++OR
++++++++++CONVERT(`report_sorting`+USING+utf8mb4)+LIKE+'%25Mary%25'
+++++++)|code-block|syntax|javascript|283312|这有助于解决您的问题mysql版本5.7.21|283313|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

Try this code,

<pre><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%'
 )
</code></pre>

This is help to solve your problem mysql version 5.7.21

I'm trying to search a whole table in mySQL for a string. 

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 <code>shipping_name field</code> or the <code>billing_name field</code>, or the <code>email</code> field, etc. 

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:

<pre><code>SELECT * FROM `clients` IN 'Mary'
</code></pre>

Search a whole table in mySQL for a string

我正尝试在mySQL中的整个表中搜索字符串。我想搜索表的所有字段和所有主菜，返回包含指定文本的每个完整条目。我不知道如何轻松地搜索多个字段；以下是详细信息：该表是“客户端”。它有大约30个字段和800个条目，太多了，无法在浏览器中一次显示所有内容。我想搜索一个名称(即"Mary")，但它可以在shipping_name field或billing_name field中，或email字段中，等等。

问在mySQL中的整个表中搜索字符串
EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在mySQL中的整个表中搜索字符串EN

回答 7

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在mySQL中的整个表中搜索字符串
EN