Does MySQL automatically casting\converting the string to numeric value?
How does that conversion works?
- '1234'=1234 ?
- '1abc' = 1 ?
- 'text' = 1 ?
Given that units.id
is of bigint type, how this query will be interpreted?
SELECT table.*
FROM table
WHERE id='text'
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
Like any DBMS, it does the implicit conversion.
For other type, according to the MySQL documentation, you have to use the convert/cast function
http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
Here is an example from SO:
Convert text into number in MySQL query