Menu
Does MySQL automatically casting\converting the string to numeric value?
How does that conversion works?
Given that units.id is of bigint type, how this query will be interpreted?
SELECT table.*
FROM table
WHERE id='text'
581
Some conversions occur implicitly. For example, MySQL automatically converts strings to numbers as necessary, and vice versa. It is also possible to convert a number to a string explicitly using the CAST () function.
MySQL CAST function examples. In the following example, MySQL converts a string into an integer implicitly before doing calculation: SELECT ( 1 + '1' )/ 2; Try It Out. To explicitly convert a string into an integer, you use the CAST () function as the following statement: SELECT ( 1 + CAST ( '1' AS UNSIGNED ))/ 2;
The syntax of the MySQL CAST () function is as follows: CAST (expression AS TYPE); The CAST () function converts a value of any type into a value that has a specified type. The target type can be any one of the following types: BINARY, CHAR, DATE, DATETIME, TIME, DECIMAL, SIGNED, UNSIGNED . The CAST () function is often used to return a value ...
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 strings to numbers as necessary, and vice versa. mysql> SELECT 1+'1'; -> 2 mysql> SELECT CONCAT(2,' test'); -> '2 test'.
The answers to your first three questions are: yes, yes, and no.
When the string 'text' is converted to a number, it becomes the value 0.
The documentation that describes type conversion is here.
For your query:
SELECT table.*
FROM table
WHERE id='text';
The rule is captured by this excerpt from the documentation:
In all other cases, the arguments are compared as floating-point (real) numbers.
In other words, this is actually equivalent to:
WHERE id = 0.0
MySQL by default treats 1 and '1' the same however you can change that by setting the MySQL behavior to Strict mode.
set @@GLOBAL.sql_mode = "STRICT_ALL_TABLES";
set @@SESSION.sql_mode = "STRICT_ALL_TABLES";
or you can set these variables in your my.cnf file to be permanent in sql_mode = ''. This way MySQL will throw an error if an incorrect type is used.
Read http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html for more details
33
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
