mysql – COLLATION’utf8_general_ci’对CHARACTER SET’二进制’无效?

MysqL> SELECT LOCATE("n","München") COLLATE utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'binary'

我该如何摆脱这个错误

我已经尝试过的(复制和粘贴):

$MysqL -u admin -p $DATABASE
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MysqL monitor.  Commands end with ; or \g.
Your MysqL connection id is 2
Server version: 5.1.69 Source distribution

Copyright (c) 2000,2013,Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MysqL> SELECT LOCATE("n","München") COLLATE utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'binary'
MysqL> SET NAMES utf8;
Query OK,0 rows affected (0.00 sec)

MysqL> SELECT LOCATE("n","München") COLLATE utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'binary'
MysqL> SELECT LOCATE(_utf8"n",_utf8"München") COLLATE utf8_general_ci;
ERROR 1253 (42000): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'binary'
MysqL> SHOW VARIABLES LIKE "character_set_database";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| character_set_database | utf8  |
+------------------------+-------+
1 row in set (0.00 sec)
最佳答案
可能服务器已使用二进制的默认字符集进行编译,因此字符串文字正在被解释为,或者客户端设置为在与服务器通信时使用二进制模式.您可以通过调用SET NAMES utf8来更改客户端和连接字符集(但如果您的sql语句是从PHP发出的,则不建议这样做,因为PHP将有自己的命令来设置连接字符集).请参阅MysqL参考手册中的Connection Character Sets and Collations.

或者,您可以使用“introductionrs”明确指定LOCATE函数中用于字符串文字的字符集,例如:

LOCATE(_utf8"n",_utf8"München")

有关详细信息,请参见参考手册第Character String Literal Character Set and Collation页.

相关文章

昨天的考试过程中,有个考点的服务器蓝屏重启后发现Mysql启动不了(5.6.45 x32版本,使用innoDB),重装后...
整数类型 标准 SQL 中支持 INTEGER 和 SMALLINT 这两种类型,MySQL 数据库除了支持这两种类型以外,还扩...
一条 SQL 查询语句结构如下: SELECT DISTINCT <select_list> FROM <left_table&...
数据备份 1. 备份数据库 使用 mysqldump 命令可以将数据库中的数据备份成一个文本文件,表的结构和数据...
概述 在实际工作中,在关系数据库(MySQL、PostgreSQL)的单表数据量上亿后,往往会出现查询和分析变慢...
概述 触发器是 MySQL 的数据库对象之一,不需要程序调用或手工启动,而是由事件来触发、激活,从而实现...