Compare integer with string in MySQL

Lets say we have a table with the following structure:

CREATE TABLE people 
(
    id INT NOT NULL AUTO_INCREMENT,
    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) 
VALUES
('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:

SELECT *
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.

How to fix 'Illegal mix of collations' issue

You might have come across the following error in MySQL:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='

after executing a SQL SELECT statement as simple as:

SELECT *
FROM MyPersons
WHERE name NOT IN (SELECT name FROM MyUsers);

You can view an on-line demo of this case here:

We can understand the origins of this error if we check the MySQL on-line manual:

A character set is a set of symbols and encodings. A collation is a set of rules for comparing characters in a character set.

So, what causes the problem in our case is that despite the two tables, MyPersons, MyUsers, share the same character set, their collations are not the same. Due to this fact MySQL is unable to perform a comparison between the fields called name.

Let us have a look at how the two tables are created:

CREATE TABLE MyPersons (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_general_ci
);

CREATE TABLE MyUsers ( 
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci
);

To solve the problem we can override the collation of table MyUsers using the COLLATE clause:

SELECT *
FROM MyPersons 
WHERE name NOT IN (SELECT name COLLATE utf8_general_ci
                   FROM MyUsers);