Search code examples
mysqlsqlstringnumberscomparison

MySQL strange behavior when comparing comma-separated string with number

I am experiencing some weird behavior with MySQL. Basically I have a table like this:

ID     string
1      14 
2      10,14,25

Why does this query pull id 2?

SELECT * FROM exampletable where string = 10

Surely it should be looking for an exact match, because this only pulls id 1:

SELECT * FROM exampletable where string = 14

I am aware of FIND_IN_SET, I just find it odd that the first query even pulls anything. Its behaving like this query:

SELECT * FROM exampletable where string LIKE '10%'
Solution

  • When you compare a numeric and a string value, MySQL will attempt to convert the string to number and match. Number like strings are also parsed. This we have:

    SELECT '10,14,25'      =   1     -- 0
SELECT '10,14,25'      =  10     -- 1
SELECT 'FOOBAR'        =   1     -- 0
SELECT 'FOOBAR'        =   0     -- 1
SELECT '123.456'       = 123     -- 0
SELECT '123.456FOOBAR' = 123.456 -- 1

    The behavior is documented here (in your example it is the last rule):

    ...

    If one of the arguments is a decimal value, comparison depends on the other argument. The arguments are compared as decimal values if the other argument is a decimal or integer value, or as floating-point values if the other argument is a floating-point value.

    In all other cases, the arguments are compared as floating-point (real) numbers.