用于搜索所有字段的 MYSQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3797906/
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 query for searching through ALL the fields?
提问by siliconpi
As dumb as it sounds, is there a way to do something like this:
听起来很愚蠢，有没有办法做这样的事情：
select row_id from mytable where * like '%searched_text%';
By * here I mean "all the fields" in the table, instead of me specifying them one by one...
这里的 * 是指表格中的“所有字段”，而不是我一一指定...
采纳答案by Wikeno
It is not possible with one query.
一个查询是不可能的。
However when you do:
但是，当您这样做时：
DESCRIBE table_name;
you get the field names which you can generate the query from.
您将获得可以从中生成查询的字段名称。
Search in all fields from every table of a MySQL databaseMay be useful.
在 MySQL 数据库的每个表的所有字段中搜索可能很有用。
回答by K. Kilian Lindberg
Here's a solution combined with some PHP to search all fields in a specifictable.
这是一个结合一些 PHP 的解决方案，用于搜索特定表中的所有字段。
include("db_con.php");
//search all fields
$searchphrase = "banan";
$table = "apa303";
$sql_search = "select * from ".$table." where ";
$sql_search_fields = Array();
$sql = "SHOW COLUMNS FROM ".$table;
$rs = mysql_query($sql);
while($r = mysql_fetch_array($rs)){
$colum = $r[0];
$sql_search_fields[] = $colum." like('%".$searchphrase."%')";
}
$sql_search .= implode(" OR ", $sql_search_fields);
$rs2 = mysql_query($sql_search);
$out = mysql_num_rows($rs2)."\n";
echo "Number of search hits in $table " . $out;
回答by Pablo Santa Cruz
Yes, it's called Full Text Search.
是的，它被称为全文搜索。
You can use MySQL's built-in Full Text Search, or use a separate product to do you text indexing such as Apache's Lucene(my personal favorite).
您可以使用 MySQL 的内置全文搜索，或者使用单独的产品来进行文本索引，例如Apache 的 Lucene（我个人最喜欢的）。
回答by Naveen Web Solutions
I was looking something like this to search in all the fields of a table. Though my table having less data so I opted 'Kilian Lindberg' answer and make the PDO function using his idea. In this function we can sent the 'search string' and 'Table name' in parameter and it will return the SQL string which we can use further as per our requirement. Thought in big tables it may slowdown the process.
我正在寻找这样的东西来搜索表的所有字段。尽管我的表格数据较少，所以我选择了“Kilian Lindberg”答案并使用他的想法制作 PDO 功能。在这个函数中，我们可以在参数中发送“搜索字符串”和“表名称”，它将返回我们可以根据需要进一步使用的 SQL 字符串。认为在大表中它可能会减慢进程。
function columnsTable($Search, $Table){
include("PDO_conn_detail.php"); /* your PDO mysql connection file */
$srchSQLstr = "SELECT * FROM $Table WHERE ";
$tableFields = Array();
$colSQL = $conn->prepare("SHOW COLUMNS FROM $Table");
$colSQL->execute();
while ($result_colSQL = $colSQL->fetch(PDO::FETCH_ASSOC)){
$tableFields[] = $result_colSQL[Field]." LIKE ('%".$Search."%')";
}
$srchSQLstr .= implode(" OR ", $tableFields);
return $srchSQLstr;
}
回答by Sahil Jangra
Use like this
像这样使用
select row_id from mytable where '%searched_text%' IN ('column1','column2','column3','column4','column5');
回答by Partizan
This solution make it possible by Ms Sql Server via Linked server
此解决方案使 Ms Sql Server 通过链接服务器成为可能
DECLARE @searchTxt VARCHAR(100) = 'searching text'
DECLARE curr CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
select * from openquery (BITRIX, 'SELECT table_name, column_name FROM information_schema.columns where table_schema = ''bitrix_50'' and data_type = ''varchar'' and table_name not like ''b_mail%''')
DECLARE @tName VARCHAR(100), @cName VARCHAR(100), @sql NVARCHAR(4000), @cnt INT
OPEN curr
FETCH NEXT FROM curr INTO @tName, @cName
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @sql = 'select @cnt=cnt from openquery (BITRIX, ''SELECT count(*) cnt FROM ' + @tName + ' WHERE ' + @cName + ' LIKE ''''%' + @searchTxt + '%'''''')'
BEGIN TRY
EXECUTE sp_executesql @sql, N'@cnt int OUTPUT', @cnt=@cnt OUTPUT
IF (@cnt>0)
PRINT @sql
END TRY
BEGIN CATCH
PRINT N'Error on: ' + @sql
END CATCH
FETCH NEXT FROM curr INTO @tName, @cName
END
CLOSE curr
DEALLOCATE curr
回答by Thato Pebane
He is a complete solution,,is the modification of the above solution,It worked for me, Thank you.
他是一个完整的解决方案，是对上述解决方案的修改，对我有用，谢谢。
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title></title>
</head>
<body>
<?php
include('config.php');
$searchphrase = "adsa";
$table = "tenders";
$sql_search = "select * from " . $table . " where ";
$sql_search_fields = Array();
$sql = "SHOW COLUMNS FROM " . $table;
$rs = mysql_query($sql);
while ($r = mysql_fetch_array($rs)) {
$colum = $r[0];
$sql_search_fields[] = $colum . " like('%" . $searchphrase . "%')";
}
$sql_search .= implode(" OR ", $sql_search_fields);
$rs2 = mysql_query($sql_search);
$out = mysql_num_rows($rs2) . "\n";
echo "Number of search hits in $table " . $out."<br />";
while ($results = mysql_fetch_array($rs2)){
print $results[0]."<br />";
}
?>
</body>
</html>
