"Smart" columns in Mysql

Question

There is a way for me to assign something to a column in Mysql in order to perform the query I select them?

Practical example of this: let’s say I only need to show on the screen all filters on website (filters are table columns), I can do so:

SELECT filtro_1,filtro_2,filtro_3 FROM filtros

But if the table changes (like adding a new filter, now the filtro_4), this line above would not bring the results I expected, having to redo it in order to also show the last filter (filtro_4).

Anyway, how do I make this "dynamic"? How can I select column markup instead of column name? If not, if possible, make a suggestion.

Answer 1

Although another answer give a solution, I believe I did not want to normalize the table to get the result, you just want the facilitator to assemble the query. Altering the modeling because of this would be absurd.

In the comments asked about the type of the columns. I do not know what fancy solutions are thinking but I doubt it is something nice to use.

Data dictionary

The only way I see of doing this is to use one data dictionary. I’m not saying you should.

This is an old technique that almost everyone ignores, for one reason or another. Almost everything (complex) 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. This is how it controls access to the data. If you need to change the structure of the database, the change will be made in the dictionary and an application will apply the changes starting from the modification made in the dictionary.

Among the numerous advantages it can also be used to assemble their darlings as you wish.

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

Making the data dictionary system is not something simple and if it is only for this too is nonsense. But there is no simple solution.

I won’t go into more detail about the data dictionary which is not the focus of the question, but who wants superior applications should learn to work with a.

Answer 2

In SQL there is the "joker" which is the * which means all or in Portuguese, all. That is, if you do the consultation:

SELECT * FROM filtros

You’re saying:

Select everything from the table filtros.

Whether you add or remove fields, the query will always bring all fields from the table.

In relationship-free appointments this is great, but in relationship-related cases JOIN That should take a precaution:

Imagine the tables

Categories

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

Products

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

Then the consultation would be:

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

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

In such cases it is good to seek the * only from a 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 field CatID of the product table and the name of the categories with the alias categoria_nome.

Answer 3

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 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 filters that are in table X. This way of putting multiple filters in a single table is not very useful as you will have this your problem.

Aconcelho you study a little normalization, this helps you to create a better and more dynamic database.