在mySQL中为字符串搜索整个表。

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

“客户”表。它有大约30个字段和800个条目，太多了，无法在浏览器中同时显示。我想搜索一个名字(例如。但是可以是shipping_name字段或billing_name字段，或电子邮件字段，等等。

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'

7 个解决方案

#1

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 SELECT operator syntax (WHERE section, to be precise).

只搜索所有字段:我认为，如果不显式地声明要搜索的字段，就不可能生成一个sql查询来搜索所有字段。原因是有关系数据库理论和处理关系数据的严格规则(比如关系代数或codd代数);这就是SQL的来源)，而且理论上不允许“只搜索所有字段”之类的东西。当然，实际行为取决于供应商的具体实现。但在一般情况下是不可能的。要确保这一点，请检查SELECT操作符语法(确切地说，是WHERE部分)。

#2

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.

使用'%Mary%'而不是= 'Mary'将查找包含一些分子+ 'Mary' +一些分子的字段。

#3

In addition to pattern matching with 'like' keyword. You can also perform search by using fulltext feature as below;

除了模式匹配与“like”关键字。您还可以使用fulltext特性来执行搜索;

SELECT * FROM clients WHERE MATCH (shipping_name, billing_name, email) AGAINST ('mary')

#4

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);

#5

If you are just looking for some text and don't need a result set for programming purposes, you could install HeidiSQL for 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!!

非常有用，节省了我很多时间。不要再纠结于冗长的查询了!

#6

If you're using Sublime, you can easily generate hundreds or thousands of lines using Text Pastry in conjunction with multiple line selection and Emmet.

如果您使用的是Sublime，您可以使用文本点阵结合多行选择和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 div markup and replace it with the MySQL syntax.

在我的例子中，我将文档类型设置为html，然后输入div*249，点击tab, Emmet创建了249个空div。然后使用多个选择，在每个选项中键入col_id_，并触发文本点心插入增量id号。然后再使用多个选择，您可以删除div标记，并用MySQL语法替换它。

#7

for specific requirement the following will work for search:

具体要求如下:

select * form table_name where (column_name1='%var1%' or column_name2='var2' or column_name='%var3%') and column_name='var';

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

如果您想要查询从数据库中搜索数据，这将非常有效。

#1