Illegal mix of collations for operation 'like' while searching with Ignited-Datatables

最新推荐文章于 2024-04-10 21:22:39 发布

转载最新推荐文章于 2024-04-10 21:22:39 发布 · 284 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/SofuBlue/p/8079101.html

文章标签：

#数据库 #php #开发工具

本文探讨了在使用Ignited-Datatables进行数据库搜索时遇到的MySQL非法混合字符集错误，特别是在输入非拉丁字符时出现的问题。文章提供了几种解决方案，包括避免在LIKE操作中使用DATETIME字段、调整字段类型和使用特定的字符集比较。

Stack Overflow

Join Stack Overflow to learn, share knowledge, and build your career.

Illegal mix of collations for operation 'like' while searching with Ignited-Datatables

Ask Question

up vote 9down vote favorite

I have successfully implemented Ignited-Datatables. However, while searching with database when typing "non-latin" characters like "İ,ş,ğ,.."

POST http://vproject.dev/module/user/ign_listing 500 (Internal Server Error)

Details are:

Illegal mix of collations for operation 'like' while searching
... (u.id_user LIKE '%Ä°%' OR u.first_name LIKE '%Ä°%' OR u.last_name LIKE '%Ä°%' OR ue.email LIKE '%Ä°%' OR u.last_login LIKE '%Ä°%' ) ...

%Ä°% part changes according to the non-latin character you typed.

Any idea for solving this?

edited Jun 17 '14 at 13:45

Raidri

13.2k63447

asked Sep 5 '13 at 6:22

YahyaE

668617

One option is to use convert(datefield using utf-8)... – bbonev Jun 6 at 7:47

add a comment

3 Answers

active oldest votes

up vote 13down voteaccepted

I figured out the problem. It seems it is DATETIME fields that causes the problem.

.. ue.last_login '%ayşenur%'

gives error for Illegal mix of collations for operation 'like'. When I remove LIKE partials DATETIME fields, there are no error any more. I hope this helps.

answered Sep 6 '13 at 6:10

YahyaE

668617

Merhaba. did you fix this issue without removing DATETIME? – Rashad Sep 4 '14 at 8:23

No, LIKE and DATETIME incompitablity is a MySQL problem. Hope to be fixed in later versions. – YahyaESep 4 '14 at 11:37

As I understand, the problem is on JSON UNICODE DECODES. So, in PHP 5.4 it should work. Did you try it at the latest PHP version? – Rashad Sep 5 '14 at 11:42

@RiKo You may be right. The question is one year old, so I did not try with the latest version. Let me and community know if you try and make it work. Thanks – YahyaE Sep 6 '14 at 8:33

@RiKo Did you try with the latest version? – YahyaE Oct 6 '14 at 17:38

show 2 more comments

up vote 3down vote

Try the following:

u.id_user LIKE '%Ä°%' OR ... OR ... '%Ä°%' COLLATE utf8_bin

Refer to MySQL Unicode Character Sets

Also you can refer to MySQL _bin and binary Collations for more information on utf8_bin:

Nonbinary strings (as stored in the CHAR, VARCHAR, and TEXT data types) have a character set and collation. A given character set can have several collations, each of which defines a particular sorting and comparison order for the characters in the set. One of these is the binary collation for the character set, indicated by a _bin suffix in the collation name. For example, latin1 and utf8 have binary collations named latin1_bin and utf8_bin.

edited Sep 5 '13 at 6:46

answered Sep 5 '13 at 6:39

doitlikejustin

5,24522955

Thanks for the knowledge but it didn't work. I think it is something about how the library handles the post data.– YahyaE Sep 5 '13 at 8:18

Why don't you try var_dump($this->input->post()); in your controller to see exactly what CI is doing to those chars. – doitlikejustin Sep 5 '13 at 14:57

I did. It is where the problem is. I am stacked with this, how come post is not utf-8? – YahyaE Sep 6 '13 at 3:16

The POST seems UTF-8, it is okay. Something wrong with Ignited-Datatables_ – YahyaE Sep 6 '13 at 5:03

Further reference [link] (github.com/IgnitedDatatables/Ignited-Datatables/issues/38) – YahyaE Sep 6 '13 at 5:05

add a comment

up vote 1down vote

i know that this is far too late, but, here my workaround.

SELECT * FROM (SELECT DATE_FORMAT(some_date,'%d/%m/%Y') AS some_date FROM some_table)tb1
WHERE some_date LIKE '% $some_variable %'

datetime/date column gives error for Illegal mix of collations for operation 'like', therefore, by converting it, as another table entity, previous column type will be replace with varchar type.

also, make sure to convert any column before convert it to temporary table, to make matching process more easier.

answered Aug 17 '16 at 4:15