Compare integer with string in MySQL

Lets say we have a table with the following structure:

    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age TINYINT,
    PRIMARY KEY (id)

Let us also insert some sample data to have something to play with:

INSERT INTO people (first_name, last_name, age) 
('Giorgos', 'Betsos', 49),
('Tim', 'Smith', 35),
('Bob', 'No Age', 0);

Note that there is no need to specify id value in the INSERT as this is an AUTO INCREMENT key. Also, no age is available for 'Bob', so we choose to represent this fact with a 0 value.

If we run this query:

FROM people
WHERE age = 'old';

we get:

id first_name last_name age
3 Bob No age 0

Click here for a live demo.

So, one record is returned despite there is no record with age='old' in the table. How can this be possible?

We start to realize what is going on here if we execute simple query:

SELECT 'old' + 0

This seems not to make sense. However the query executes and returns a value of 0. This is because of implicit type conversion. String value 'old' is converted to 0 and then added to 0 and thus 0 is returned.

The same thing happens with our query. 'old' is converted to 0 when compared with a field of type int.

1 comment:

  1. Amazing post.Thanks for your details and explanations..I want more information from your side.Thank you

    Guest posting sites


Featured Post

Store the result of a select query into an array variable

Popular Posts