CODEWITHSUNDEEP
mysqlsqlcharacter-encoding
Geoff Hardy
Geoff Hardy

Reputation: 57

MySQL collation operation with a LIKE issue

I have a collation issue. It is affecting 3 columns of this table, creation_date, product_id and lastmodified.

I have changed the columns to be utf8mb4 but they don't take it. Please see below.

CREATE TABLE `users` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `creation_date` datetime DEFAULT NULL,
  `product_id` int(32) DEFAULT NULL,
  `lastmodified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=121 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

The queries:

select * from users u where u.name like '%philėp%'
No errors, 1 row.

select * from users u where u.creation_date like '%philėp%'
Illegal mix of collations for operation 'like'

MySQL system variables:

show variables like '%character_set%';
character_set_client    utf8
character_set_connection    utf8
character_set_database  utf8
character_set_filesystem    binary
character_set_results   utf8
character_set_server    utf8mb4
character_set_system    utf8

It does work when I manually force MySQL to convert the column in the statement.

select * from users u where CONVERT(u.creation_date USING utf8mb4) like '%philėp%'
No errors; 0 rows;

is it not utf8mb4 format already?

Would appreciate any help.

Upvotes: 2

Views: 1113

Answers (1)

Craig Taub
Craig Taub

Reputation: 4179

This is my understanding.

A DATETIME does not have collation.

Similar to how an INT doesn't due to the fact it is a numerical value

But if you query (or insert) to a DATETIME you are using a string which has been formatted in such a way. This means it's possible for an implicit conversion between the string in your query and the DATETIME value in the database.

It is this implicit conversion which I think causes the problems here.

Additionally you are using creation_date with underscore and lastmodified without. This should really be both with underscore or both without. It's not making much difference with the query but helps maintain your database standards.

Upvotes: 3

Related Questions