Mysql查询在表的所有列中搜索一个字符串 [英] Mysql query search a string in all columns of a table

问题描述

我想知道如何安装尽可能可移植的 SQL 以查询特定短语的表的所有列，例如:

I wonder how I can mount a SQL as portable as possible to query for all columns of a table for a specific phrase, like:

表格

ID | Name           | text      | Date       | Author  | Status
1  | Augusto Weiand | Test text | 2010-01-01 | Deividi | 1

查询

SELECT * 
FROM table 
WHERE columns LIKE '%augusto%2010%text%"

我没有提供足够的细节，对不起，我喜欢做一个动态的 SQL，我不需要用AND"或OR"来指定列，因为在 Postgres 中是可以做到的:

I did not put enough detail, excuse me, I like to make a dynamic SQL, where I do not need to specify the columns with 'AND' or 'OR', as it is possible to do in Postgres:

Select * 
From table 
Where table::text ~~ '%augusto%2010%text%'

推荐答案

以下是连接动态 SQL 中的值的方法:

Here is how you would concatenate the values in dynamic SQL:

set @Pattern = '%augusto%';

select @q := concat('select * from Table1 ',
                   'where concat(', group_concat(column_name), ', "") like "', @Pattern, '"'
                   )
from information_schema.columns c
where table_name = 'Table1';

prepare st from @q;
execute st;

deallocate prepare st;

当然，动态 SQL 并不是特别便携.这个想法适用于大多数数据库.代码看起来会有所不同.

Of course, dynamic SQL is not particularly portable. The idea would work in most databases. The code would look different.

在此处进行测试和工作.

最后，您可以使用变量替换(这是更好的方法)来做到这一点:

And finally, you can do this with variable substitution (which is the better approach):

select @q := concat('select * from Table1 ',
                   'where concat(', group_concat(column_name), ', "") like ?'
                   )
from information_schema.columns c
where table_name = 'Table1';

set @p = '%augusto%';

prepare st from @q;
execute st using @p;

deallocate prepare st;

也测试过(;-).

这篇关于Mysql查询在表的所有列中搜索一个字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！