mysql – Why the "quotes" in INT data?

Question:

In MySQL Workbench , when we do not insert a record by query , but manually, when giving the APPLY , the query is displayed before execution.

Example of a query composed by MySQL Workbench :

INSERT INTO `nomedobanco`.`tabela` (`id`, `resumo`, `descricao`, `grupo`, `solicitante`, `atendente`, `status`) 
VALUES ('171', 'Teste', 'Testando', '7', '1', '2', 'N');

How it would also work:

INSERT INTO `nomedobanco`.`tabela` (`id`, `resumo`, `descricao`, `grupo`, `solicitante`, `atendente`, `status`) 
VALUES (171, 'Teste', 'Testando', 7, 1, 2, 'N');

Doubts

  • Why does MySQL Workbench put quotes around INT values?
    • Does it not identify the type of fields and ends up entering everything "as a string"?

Answer:

First of all, this is not unique to the Workbench . I used other tools and SqlYog for example does exactly the same thing.

This is to optimize query generation. As MySql does not claim to receive integer values ​​in quotes, these programs use this trick to not need to check the column type to place or not the value in quotes.

But be careful when using this device. In the case of INSERT and UPDATE SET , that is, assignments, I don't see much of a problem. But in WHERE you may have some problems related to query performance. This is because, when comparing text with number, MySql performs a type conversion, so in these cases, the ideal is to use numbers without quotes, if compared to numbers.

Note that the Workbench also places numeric values ​​in quotation marks in comparisons.

On INSERT it probably also performs this conversion, but as the amount of data is usually small it becomes irrelevant (not true for all cases).

Scroll to Top
AllEscort