MySQL collation operation with a LIKE issue

Question

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.

Answer 1

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.