MySQL归类操作与LIKE问题 [英] MySQL collation operation with a LIKE issue

问题描述

我有一个排序规则问题。它正在影响此表的3列，creation_date，product_id和lastmodified。

我已将列更改为utf8mb4，但他们不接受。

  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 
  pre> 
 
 
查询：
  select * from users u .name like'％philėp％'
没有错误，1行。 
 
 select * from users u其中u.creation_date喜欢'％philėp％'
操作的非法混合排序'like'
  
 MySQL系统变量：
  '; 
 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 
  
 
 
 
当我手动强制MySQL转换语句中的列时，它会工作。
  select * from users u其中CONVERT（u.creation_date USING utf8mb4）like'％philėp％'
没有错误; 0行; 
  
是不是utf8mb4格式？
 
 
 
 
 
 
 
 
解决方案
这是我的理解。 > A  DATETIME 没有排序规则。
 
 
 
类似于 INT 是一个数字值
 
 
 
但如果您查询（或插入）到 DATETIME ，则表示您使用的格式化字符串。这意味着可以在查询中的字符串和数据库中的 DATETIME 值之间进行隐式转换。
 
 
 
此外，您正在使用 creation_date 加下划线，这是隐含的和 lastmodified 。这应该是 下划线或都没有。它与查询没有什么区别，但有助于维护数据库标准。
 
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.
解决方案
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.

                        
这篇关于MySQL归类操作与LIKE问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！