mysql字符数转整数,mysql自动转换字符串为整数-优快云博客

my question is a general question:

doctrine treats

$entitity->getTest()->clear();

into this sql query:

DELETE FROM test WHERE test_id = '6'

-> test_id is an integer in the database, mysql converts automatic the value so it works.

but the right query would be :

DELETE FROM test WHERE test_id = 6

I found some Diskussions here:

stackoverflow.com/questions/21762075/mysql-automatically-cast-convert-a-string-to-a-number

code.openark.org/blog/mysql/implicit-casting-you-dont-want-to-see-around

mysql doc says:

"The following rules describe how conversion occurs for comparison operations" (..) In all other cases, the arguments are compared as floating-point (real) numbers.

The Documentation also tells an issue with it:

Comparisons that use floating-point numbers (or values that are converted to floating-point numbers) are approximate because such numbers are inexact. This might lead to results that appear inconsistent

So why doctrine orm and also dbal acts so? Is this not a problem with tables with integers? only with bigint?

Also look here:

-> There it tells that this is not a Problem.

So my question is: Do a query with where int_val = '1' (string) is no big deal or can this be dangerous. if this is dangerous, haste doctrine a design issue here?

解决方案When an operator is used with operands of different types, type conversion occurs to make the operands compatible. Some conversions occur implicitly. For example, MySQL automatically converts numbers to strings as necessary, and vice versa. Reference

This also applies when selecting/inserting/updating, using the column type as reference.

So, if you do SELECT * FROM users WHERE balance = '1234' and the type of the column balance is DECIMAL (6,2) the following is implied.

mysql> SELECT CONVERT ('1234', DECIMAL (6,2));

+---------------------------------+

| CONVERT ('1234', DECIMAL (6,2)) |

+---------------------------------+

| 1234.00 |

+---------------------------------+

1 row in set (0.00 sec)

You'll always have a default value for those values who can't be converted.

mysql> SELECT CONVERT ('test', DECIMAL (6,2));

+---------------------------------+