mysql自动转换字符串为整数 [英] mysql autocasting string to integer

问题描述

我的问题是一个一般性问题：

doctrine对待

$ entitity - > getTest（） - > clear（）;

到此sql查询中：

  DELETE FROM test WHERE test_id ='6'
  

- > test_id是数据库中的一个整数，mysql自动转换值，使其工作。
，但正确的查询将是：

  DELETE FROM test WHERE test_id = 6 
  
 
 
 
我在这里发现了一些Diskussions：
 
 
 
 stackoverflow.com/questions/21762075/ mysql-automatically-cast-convert-a-string-to-a-number 
 
 
 
 code.openark.org/blog/mysql/implicit-casting-you-dont- 
 
 
 
 mysql doc说：
 
 
 
  http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html  
 
 
以下规则描述了如何进行比较操作的转换（..）在所有其他情况下，作为浮点数（实数）。
 
 
文档还说明了一个问题：
 
 
使用浮点数（或转换为浮点数的值）的比较是近似值，因为这些数字不准确。这可能会导致结果不一致
 
 
那么为什么doctrine orm和dbal会这么做？这是不是与整数表的问题？只有bigint？ 
 
 
 
另请参阅这里：
 
 
 
  http://www.cubrid.org/cubrid_implicit_type_conversion  
 
 
 
  - >它告诉我这不是一个问题。 / p> 
 
 
所以我的问题是：做一个查询，其中int_val ='1'（字符串）没有什么大不了，或者这可能是危险的。 
解决方案
 
 
 
当使用运算符时，不同类型的操作数，发生类型转换以使操作数兼容。有些转换会隐式发生。例如，MySQL根据需要自动将数字转换为字符串，反之亦然。 参考 
 
  blockquote> 
 
 
这也适用于选择/插入/更新时使用列类型作为引用。
 
 
 
  SELECT * FROM users WHERE balance ='1234'并且列 balance 的类型为 DECIMAL（6,2）下面是隐含的。
  mysql> SELECT CONVERT（'1234'，DECIMAL（6,2））; 
 + --------------------------------- + 
 | CONVERT（'1234'，DECIMAL（6,2））| 
 + --------------------------------- + 
 | 1234.00 | 
 + --------------------------------- + 
集合中的1行（0.00秒）
  
对于无法转换的值，您将始终具有默认值。 p> 
 
 
  mysql> SELECT CONVERT（'test'，DECIMAL（6,2））; 
 + --------------------------------- + 
 | CONVERT（'test'，DECIMAL（6,2））| 
 + --------------------------------- + 
 | 0.00 | 
 + --------------------------------- + 
集合中的1行，1个警告（0.00秒）
  
所以没有，没有漏洞（至少在MySQL端）。 p> 
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:

http://dev.mysql.com/doc/refman/5.5/en/type-conversion.html

  
"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:

http://www.cubrid.org/cubrid_implicit_type_conversion

-> 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));
+---------------------------------+
| CONVERT ('test', DECIMAL (6,2)) |
+---------------------------------+
|                            0.00 |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)
So no, there's no vulnerability involved (at least at MySQL side).

                        
这篇关于mysql自动转换字符串为整数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！