MySQL automatically convert/convert string to number?

Question

Does MySQL automatically convert strings to numeric values?

How does this conversion work?

• '1234'=1234 ?
• '1abc' = 1 ?
• 'Text' = 1 ?

Given that units.id is of type bigint, how will this query be interpreted?

SELECT table.* 
FROM table 
WHERE id='text'

Answer 1

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

Answer 2

The answers to the first three questions are: yes, yes, and no.

When the string 'text' is converted to a number, it becomes the value 0.

Documentation describing type conversions is located here .

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