sql - Mysql query search a string in all columns of a table

Question

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:

Table

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

Query

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

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%'

Answer 1

It's doable, although I strongly suggest you look into full-text search for efficiency;

To avoid looking for all patterns in all fields one by one, you can just concat and search in that;

SELECT *
FROM (SELECT id,CONCAT(name,'|',text,'|',date,'|',author,'|',status) txt
      FROM Table1) a
WHERE txt LIKE '%augusto%'
  AND txt LIKE '%2010%'
  AND txt LIKE '%text%';

Note that no indexing will help you here, since you're searching in a calculated column. On the other hand, since you're searching with a leading wildcard %searchterm, you won't get much help from indexes even if searching field by field :)

An SQLfiddle to test with.