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'
MySQL by default treats 1 and '1' the same however you can change that by setting the MySQL behavior to Strict mode.
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 detailsThe answers to your first three questions are: yes, yes, and no.
When the string
'text'
is converted to a number, it becomes the value0
.The documentation that describes type conversion is here.
For your query:
The rule is captured by this excerpt from the documentation:
In other words, this is actually equivalent to:
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