MySQL strange behavior when comparing comma-separated string with number

Question

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%'

See also Is storing a delimited list in a database column really that bad? — Bill Karwin
– Bill Karwin, Commented Sep 10, 2018 at 17:44

Salman Arshad · Accepted Answer · 2018-09-10 14:15:22Z

5

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.

edited Sep 10, 2018 at 14:15

answered Sep 10, 2018 at 13:41

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joel Coehoorn Over a year ago

It's worse even than this, because a conversion like this also breaks any index that might have existed on the string column, meaning the database has to attempt this conversion and compare on every row in the table.

Salman Arshad Over a year ago

@JoelCoehoorn as a programmer you must make sure that you are comparing apples to apples.

Johan Mouritsen Over a year ago

This makes sense, thank you. it converted 10,14,25 into 10 to be able to compare it. The closest documentation i have found if anyone is interested is: dev.mysql.com/doc/refman/8.0/en/type-conversion.html

Collectives™ on Stack Overflow

MySQL strange behavior when comparing comma-separated string with number

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related