MySQL where in integer vs. where in string

Question

Example Table

• Create table
• Insert records

SQL

-- create table
CREATE TABLE tests (id int AUTO_INCREMENT, foo varchar(255), PRIMARY KEY (id));
-- Insert records
INSERT INTO tests (foo) VALUES ('36'), ('36A'), ('36B'), ('36C'), ('baz');

Run:

SELECT * FROM tests where foo in ('36');

Result:

+----+------+
| id | foo  |
+----+------+
|  1 | 36   |
+----+------+

Then run: SELECT * FROM tests where foo in (36);

Result:

+----+------+
| id | foo  |
+----+------+
|  1 | 36   |
|  2 | 36A  |
|  3 | 36B  |
|  4 | 36C  |
+----+------+

Question

1. Why does passing 36 (as integer) not return the one identical record as passing '36' (as string) does?
2. Passing 3 results in an empty set. So I don't quite understand the logic.

Answer 1

In MySQL, there's a sort of implicit conversion between numbers and strings.

If you do a comparison between an integer and string, the string is converted to an integer. To convert a string to an integer, MySQL reads the leading numeric digits and ignores the rest. So '36A' has an integer value 36. '36B' also has an integer value 36. If there are no leading digits in the string, the integer value defaults to 0.

In your case, you compare the string values on a few rows to the integer 36, which means it converts each string value to the numeric portion first.

See https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html