CODEWITHSUNDEEP
mysqlsql
digout
digout

Reputation: 4252

Unknown MYSQL behaviour on selecting INT using a comma separated string

Say I have a table titles like this:

id (INT)   | title (VARCHAR)
-----------------------------
1          | Some title
2          | Another title
3          | Yep another

I accidentally discovered if I use the query:

SELECT * FROM `titles` WHERE `id` = '1,2,3'

Mysql returns me the row with id = 1

Maybe I missed this whilst learning about Mysql. Is this something that's been supported across many version of Mysql?

Upvotes: 0

Views: 58

Answers (1)

Shadow
Shadow

Reputation: 34232

Since id is a numeric column, mysql silently converts the string '1,2,3' to a number. During the conversion mysql stops at the first character that cannot be interpreted as part of a number, in this particular case the comma after the 1. So, only the character 1 will be used during the conversion.

mysql> select 1 = "1,2,3";
+-------------+
| 1 = "1,2,3" |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select 1 = ",1,2,3"; -- <- does not start with a number
+--------------+
| 1 = ",1,2,3" |
+--------------+
|            0 |
+--------------+
1 row in set, 1 warning (0.00 sec)

Upvotes: 3

Related Questions