[ci3.1.0+php5.6.17] Illegal mix of collations for operation 'like'
请教WAMP 2.5 的中文报错应该如何解决？在网上搜索好久也没找到解决方案。
错误信息如下
Error Number: 1271
Illegal mix of collations for operation 'like'
SELECT COUNT(*) AS `numrows` FROM `ci_test` WHERE ( `projname` LIKE '%中文%' ESCAPE '!' OR `pno` LIKE '%中文%' ESCAPE '!' OR `pname` LIKE '%中文%' ESCAPE '!' OR `pid` LIKE '%中文%' ESCAPE '!' OR `cost` LIKE '%中文%' ESCAPE '!' OR `borrowername` LIKE '%中文%' ESCAPE '!' OR `status` LIKE '%中文%' ESCAPE '!' OR `status` LIKE '%中文%' ESCAPE '!' OR `plan_return_date` LIKE '%中文%' ESCAPE '!' OR `remark` LIKE '%中文%' ESCAPE '!' )
Filename: D:/wamp/www/dev/system/database/DB_driver.php
Line Number: 691
Mysql控制台查询结果如下，修改为utf8之后也不起作用
===========================
mysql> show variables like "%character%"; show variables like "%collation%";
+--------------------------+-----------------------------------------------+
| Variable_name | Value |
+--------------------------+-----------------------------------------------+
| character_set_client | big5 |
| character_set_connection | big5 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | big5 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | d:\wamp\bin\mysql\mysql5.6.17\share\charsets\ |
+--------------------------+-----------------------------------------------+
8 rows in set (0.00 sec)
+----------------------+-------------------+
| Variable_name | Value |
+----------------------+-------------------+
| collation_connection | big5_chinese_ci |
| collation_database | latin1_swedish_ci |
| collation_server | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)
===========================
当前是Win7 繁中系统 connection在用big5，數據庫、頁面及程式是否用亦是用big5？
big5或是utf-8無所謂，但必須要一致！
几天没进论坛，UI竟然改版了。简洁好看。
这个问题解决，还是自己不太熟练的原因吧。
1. 用表单辅助函数 form_open或设置accept-charset="utf-8"
<form method="post" accept-charset="utf-8" action="http://example.com/index.php/email/send">
2. mysql日期格式(date)的字段不加进模糊搜索。<-<- 这个坑是我一个一个字段排除出来的 燃雲 发表于 2016-10-7 15:25
connection在用big5，數據庫、頁面及程式是否用亦是用big5？
big5或是utf-8無所謂，但必須要一致！
感謝回覆，我在form添加accept-charset="utf-8"，然後把模糊搜尋date相關字段移除後就修復了。:lol
页:
[1]