84669 person learning
152542 person learning
20005 person learning
5487 person learning
7821 person learning
359900 person learning
3350 person learning
180660 person learning
48569 person learning
18603 person learning
40936 person learning
1549 person learning
1183 person learning
32909 person learning
Does MySQL automatically convert strings to numeric values?
How does this conversion work?
Given thatunits.idis of type bigint, how will this query be interpreted?
units.id
SELECT table.* FROM table WHERE id='text'
By default, MySQL treats 1 and "1" the same, but you can change this 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 the my.cnf file as permanent variables insql_mode = ''. This way, MySQL will throw an error if an incorrect type is used. Readhttp://dev.mysql.com/doc/ refman/5.0/en/server-sql-mode.htmlfor more details
sql_mode = ''
The answers to the first three questions are: yes, yes, and no.
When the string'text'is converted to a number, it becomes the value0.
'text'
0
Documentation describing type conversions is locatedhere.
For your query:
SELECT table.* FROM table WHERE id='text';
The rule is captured via a document excerpt:
In other words, this is actually equivalent to:
WHERE id = 0.0
By default, MySQL treats 1 and "1" the same, but you can change this by setting the MySQL behavior to strict mode.
Or you can set these variables in the my.cnf file as permanent variables in
sql_mode = ''
. This way, MySQL will throw an error if an incorrect type is used. Readhttp://dev.mysql.com/doc/ refman/5.0/en/server-sql-mode.htmlfor more detailsThe answers to the first three questions are: yes, yes, and no.
When the string
'text'
is converted to a number, it becomes the value0
.Documentation describing type conversions is locatedhere.
For your query:
The rule is captured via a document excerpt:
In other words, this is actually equivalent to: