CODEWITHSUNDEEP
mysqltype-conversioncasing
yossi
yossi

Reputation: 3164

MySQL automatically cast/convert a string to a number?

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'

Upvotes: 12

Views: 15033

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

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

Upvotes: 13

Elfentech
Elfentech

Reputation: 747

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

Upvotes: 1

Sam
Sam

Reputation: 2761

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

Upvotes: 5

Related Questions