MySQL Implicit Casts Broke an UPDATE

• 2023/07/04

MySQL implicit casts can bite updates

Sometimes we store comma-separated IDs in a text column (not ideal, but it happens). Say we have item rows with tag_id_list defined as VARCHAR, and we want all items that only have tag 1. Because MySQL silently casts types, the following works:

SELECT *
FROM item
WHERE tag_id_list = 1;

Strictly speaking it should be:

SELECT *
FROM item
WHERE tag_id_list = '1';

The gotcha arises when updating. Suppose we want to append tag 2 to every item whose list is just 1:

UPDATE item
SET tag_id_list = '1,2'
WHERE tag_id_list = 1;

This throws an error. You must treat the WHERE clause as a string:

UPDATE item
SET tag_id_list = '1,2'
WHERE tag_id_list = '1';

Implicit casting, in both databases and programming languages, can produce surprises if you are not mindful of types. Know your language and avoid unintended conversions.