"Smart" columns in MySQL

Although the other answer does give a solution, I believe that I did not want to normalize the table to get the result, you just want the facilitator to mount the query . Changing the modeling because of this would be absurd.

In the comments asked about the type of columns. I do not know what fancy solutions are thinking but I doubt it's legal to use.

The only way I can see this is to use a data dictionary .

This is an old technique that almost everyone ignores, for one reason or another. Almost everything I do in database is on top of a data dictionary, so it gives me ease and flexibility.

In it you will have information about how the database is structured. So it controls the access to the data. If you need to change the bank structure, the change will be made in the dictionary and an application will apply the changes from the modification made in the dictionary.

Among the many advantages, it can also be used to assemble your queries as you wish.

In this case the columns would have a form of grouping or labeling that indicates that these columns have a specific meaning, when adding a new column to this table in the data dictionary with this tag , its code you will know that to generate the query you will need to get all columns with the tag and you will immediately be using it.

Making the data dictionary system is not something simple and if it is just for this, it is also absurd. But there is no simple solution.

I will not go into more detail about the data dictionary that is not the focus of the question, but anyone who wants higher applications should learn to work with one.

You could create another table to make it more dynamic. For example:

CREATE TABLE IF NOT EXISTS 'sua_tabela' (
  'id_tabela' int(11) NOT NULL PRIMARY KEY
   /*Os outros campos da tabela vai aqui*/
) 

The second table would only be to create the filters so it would be a relationship table:

CREATE TABLE IF NOT EXISTS 'filtros' (
   'id_filtro' int(5) NOT NULL PRIMARY KEY,
   'id_tabela' int(5) NOT NULL PRIMARY KEY,
   'filtro' varchar(100) NOT NULL,
   FOREIGN KEY (id_tabela) REFERENCES sua_tabela(id_tabela) 
) 

and the select would look like this:

select filtro from filtros f inner join sua_tabela t on f.id_tabela = t.id_tabela where t.id_tabela = 1 /*O ID do databela qeu você deseja ver os filtros)*/

So you can search for all the filters that are in the X table. This way of putting multiple filters in a single table is not very useful because you will have this problem.

I recommend you study a little normalization, this helps you create a better and more dynamic database.

In SQL there is the "wildcard" that is * which means all or in Portuguese, everything . That is, if you make the query:

SELECT * FROM filtros

You're saying:

  

Select everything from the filtros table.

Regardless of whether you add or remove fields, the query will always fetch all fields from the table.

In queries without relationships this is fine, but in cases of relationships with JOIN this should be a precaution:

Imagine the table

Categories

| ID    | Nome    |
|-------|---------|
| 1     | Eletro  |
| 2     | Sport   |

Products

| ID    | Nome    | CatID |
|-------|---------|-------|
| 1     | Ferro   | 1     |
| 2     | Tênis   | 2     |
| 3     | Geladei | 1     |

Then the query would look like:

SELECT * 
FROM produtos prd
    INNER JOIN categorias cat ON prd.CatID = cat.ID

Note that both tables have 2 equal fields ID and Nome , so which field will return in the query?

In these cases it is good to look for * of only one table:

SELECT prd.*, cat.nome as categoria_nome 
FROM produtos prd
    INNER JOIN categorias cat ON prd.CatID = cat.ID

So we already have the category ID in the CatID field of the product table and the name of the categories with the alias categoria_nome .