[SOLVED] MySQL Update query using equals (=) and LIKE with alphanumeric criteria value

MySQL Update query using equals (=) and LIKE with alphanumeric criteria value

Recently noticed that a working UPDATE query has no longer same results with where clause that contains alphanumeric values leading with number, appreciate any help.

UPDATE query:

UPDATE `user` SET last_name = 'newTest' where `identifier` = '123abc';

ISSUE: This query updates all records with identifier begins with 123...

Fixed temporary the issue by using LIKE (see below):

UPDATE `user` SET last_name = 'newTest' where `identifier` LIKE '123abc';

But afraid there is some other similar cases in my code that could causes unacceptable data loss for clients.

EDIT: query with the issue is when I update another identifier that has the exact number leading other identifiers:

UPDATE `user` SET last_name = 'newTest' where `identifier` = 123;

Tech. versions used: php 7, mysql 5.6, RedBean 5, OS Alpine 3.11

Solution

The problem is that you compare a string value and a numeric value:

where `identifier` = 123

so Mysql does an implicit conversion of the column identifier to numeric and since the values 123abc or 123... start with 123, they are all converted to 123 and the condition returns TRUE for all the values that start with 123.
You can see this behavior here.
What you want is string comparison.
So change to:

where `identifier` = '123...'

This way you do a string comparison.