MySQL 在mysql中使用单个关键字的单个where条件搜索表的所有列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12550368/
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 all columns of a table using a single where condition with single keyword in mysql
提问by R J.
I have a table which consists of 64 different fields. i am going to search with a single keyword in it, Results should match the keyword from any field. Give some suggestions.
我有一个包含 64 个不同字段的表。我将在其中使用单个关键字进行搜索，结果应与任何字段中的关键字匹配。给一些建议。
回答by Buttle Butkus
SELECT * FROM `some_table`
WHERE
CONCAT_WS('|',`column1`,`column2`,`column3`,`column4`,`column64`) # single condition, many columns
LIKE '%VT%'
Voila.
瞧。
The '|' separator, by the way, is to prevent you finding coincidental matches where, e.g., column1 ends in 'V' and column2 starts with 'T', which would give you a false positive in a search for "VT".
'|' 顺便说一下，分隔符是为了防止您找到巧合的匹配，例如，第 1 列以“V”结尾，第 2 列以“T”开头，这会使您在搜索“VT”时出现误报。
I'm not sure if the above method is faster than the ORmethod (I would guess they're the same speed) , but it definitely involves less typing if you're writing the query by hand.
我不确定上述方法是否比方法更快OR（我猜它们的速度相同），但如果您手动编写查询，它肯定会减少输入。
回答by NullPoiиteя
you can use the where with multiple condition with OR
您可以使用具有多个条件的 where OR
like
喜欢
where
name = 'expected'
OR rate ='expected'
OR country ='expected'
回答by tdlm
I can't see a way around your query being simple but long:
我看不到您的查询简单但很长的方法：
SET @term = "Somesearch";
SELECT id, title FROM sometable WHERE
col1 LIKE '%@term%' OR
col2 LIKE '%@term%' OR
col3 LIKE '%@term%' ...;
Instead of using a MySQL variable, you can just use a language-specific variable but for the sake of examples, I thought I'd stick with MySQL itself.
您可以只使用特定于语言的变量，而不是使用 MySQL 变量，但为了举例，我想我会坚持使用 MySQL 本身。
The "..." is where you'd place the other 61 columns/fields.
“...”是您放置其他 61 列/字段的位置。
回答by processoriented
Another possibility would be to use FOR XML to get all columns to print to a single field... like this:
另一种可能性是使用 FOR XML 将所有列打印到单个字段......像这样：
SELECT c.*
FROM (
SELECT a.*
,( SELECT *
FROM table_to_search b
WHERE a.KeyField = b.KeyField
FOR XML RAW) AS `Full_Text_Record`
FROM table_to_search a) c
WHERE c.`Full_Text_Record` LIKE '%Search_string%'
Might take a while to run if it is a particularly large table, but it should brute force you to find out if that string exists in any given table.
如果它是一个特别大的表，可能需要一段时间才能运行，但它应该会强制您找出该字符串是否存在于任何给定的表中。
回答by instanceOfObject
Simplest solution would be to use multiple ORs.
最简单的解决方案是使用多个ORs.
select * from TAB where col1 like "%VAR%" OR col2 like "%VAR%" OR......col64 like "%VAR%";
You can use likeor =as per the requirement, but it will require to change your query every time you add a new column.
您可以根据要求使用like或=，但每次添加新列时都需要更改查询。
As an alternative, you can take SQLDump for that tableand then searchthat file.
作为替代方案，您可以先获取该文件SQLDump for that table，然后再获取search该文件。
With some Googling,
通过一些谷歌搜索，
See if this project is useful - http://code.google.com/p/anywhereindb/. Searches all the fields and praised by many.
Try to use the information from
information_schematable. Look for all the columns in the table. Now, try to form your query using this information.
看看这个项目是否有用 - http://code.google.com/p/anywhereindb/。搜索所有领域，并受到许多人的好评。
尝试使用
information_schema表中的信息。查找表中的所有列。现在，尝试使用此信息形成您的查询。
回答by codingbiz
If you can translate this SQL Server syntax to MySQL
如果您可以将此 SQL Server 语法转换为 MySQL
WHERE
name = @keyword OR
country = @keyword OR
department = @keyword OR
@keyword IS NULL -- match all when search text is empty
回答by user2569747
You could write one query that will generate a query for every column in your table. In the example below the schema ("owner") is 'DEV_USER' The table with your 64 fields is called 'CUSTOMER_INFO' The criteria in the search is any column with a value of 'VT' in it:
您可以编写一个查询，为表中的每一列生成一个查询。在下面的示例中，架构（“所有者”）为“DEV_USER” 包含 64 个字段的表称为“CUSTOMER_INFO” 搜索条件是其中值为“VT”的任何列：
select 'SELECT ' || COLUMN_NAME || ' FROM CUSTOMER_INFO
WHERE ' || COLUMN_NAME || q'# LIKE '%VT%';#'
FROM ALL_TAB_COLS
WHERE OWNER = 'DEV_USER'
AND TABLE_NAME = 'CUSTOMER_INFO';
This one query will generate a query for each field for you. The results of running the above would be;
这个查询将为您生成每个字段的查询。运行上面的结果将是;
SELECT ADDRESS_1 FROM CUSTOMER_INFO
WHERE ADDRESS_1 LIKE '%VT%';
SELECT ADDRESS_2 FROM CUSTOMER_INFO
WHERE ADDRESS_2 LIKE '%VT%';
SELECT CITY FROM CUSTOMER_ADDRESSES_QASB
WHERE CITY LIKE '%VT%';
SELECT STATE_PROVINCE FROM CUSTOMER_INFO
WHERE STATE_PROVINCE LIKE '%VT%';
SELECT ZIP_POSTAL_CODE FROM CUSTOMER_INFO
WHERE ZIP_POSTAL_CODE LIKE '%VT%';
WHERE LATITUDE LIKE '%VT%';
... and so on for each column in the table
Then you just paste those queries that were generated from your first query into another tab and run them.
然后，您只需将第一个查询生成的那些查询粘贴到另一个选项卡中并运行它们。
Hope that helps. :-)
希望有帮助。:-)
回答by Barmar
You can use dynamic SQL to generate and execute a query that searches all the columns.
您可以使用动态 SQL 生成并执行搜索所有列的查询。
DELIMITER $$
CREATE PROCEDURE searchAllCols(inDB VARCHAR(64), inTable VARCHAR(64), search VARCHAR(32))
BEGIN
SET @matches = (
SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '` LIKE "%', search, '%"') SEPARATOR ' OR ')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = inTable and table_schema = inDB);
SET @query = CONCAT('SELECT * FROM `', inDB, '`.`', inTable, '` WHERE ', @matches);
PREPARE stmt FROM @query;
EXECUTE stmt;
END
$$
DELIMITER ;
CALL searchAllCols('table_to_search', 'searchString');
