What difficulties can arise when executing a query with the specified number (string) in the integer field in MySQL

Question

In SQL, you should not specify integers, because if you specify it will be a string.

But I wonder what problems / complications may arise if I do this?

For instance:
SELECT * FROM table WHERE id = 1 (Fix)
vs
SELECT * FROM table WHERE id = '1' (False)

Ps Did not find duplicates on this. Correct me if there is

Answer 1

This is an interesting question, and I spent a lot of time studying the possible result (for mysql).

So far I have managed to find only 1.5 flaws:

• Firstly, you will get strange results if you perform a math or a comparison operation on the BIGINT value, if one of the operands is sent as a string in the request - due to the fact that in this case both operands will be cast for floats and, thus, lose accuracy. Here is a demo code. Just run these queries and check out the results, which are quite confusing:

   create table bint(i bigint); insert into bint values (18014398509481984); update bint set i=i+'1'; update bint set i=i+1 update bint set i=i+'1' 

  But for a simple selection or updating of BIGINT values, it is still not a problem for them to be quoted in a query or anchored as strings in a prepared statement.

• Secondly, I consider only half the problem, because I still can’t find good evidence. But database administrators insist that there are some mystical queries that are so complex that the optimizer can be corrupted by the wrong data type and choose the wrong execution plan. However, in my 15 years of experience I was not lucky to find him. I will put the maximum reward for an answer that can provide reproducible evidence, and not just a story about the good old days.

So you can say that for regular queries with ordinary data types there is no difference.

The only syntax part of the query does not allow string operands to be a LIMIT clause: a LIMIT '1' will result in a syntax error.

However, using the prepared statement, if you bind the LIMIT parameter as a string, it will do everything in order:

 $stmt = $mysqli->prepare("SELECT value FROM table LIMIT ?"); $stmt->bind_param("s", $limit); 

will go without errors.