MySQL LIKE无法与“â"一起使用 [英] MySQL LIKE doesn't work with "â"

问题描述

我正在Windows 2012R2上运行MySQL 5.6.26，在mysql.ini中，所有内容都配置为使用UTF-8 Unicode(utf8).

I am running a MySQL 5.6.26 on Windows 2012R2, in mysql.ini everything is configured to use UTF-8 Unicode (utf8).

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
[mysql]
default-character-set=utf8
[client]
default-character-set=utf8

我使用此表:

CREATE TABLE IF NOT EXISTS `wcf1_guildtool_playerdata` (
  `charID` int(11) NOT NULL,
  `charname` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `realmname` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `class` int(11) DEFAULT NULL,
  `race` int(11) DEFAULT NULL,
  `gender` int(11) NOT NULL,
  `level` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

当我执行以下SQL命令时:

When I perform the following SQL command:

SELECT * FROM wcf1_guildtool_playerdata WHERE charname LIKE 'Veneanâr'

数据库向我返回此数据:

the database returns me this data:

118 Veneanar Forscherliga 3 5 0 56

118 Veneanar Forscherliga 3 5 0 56

但是它不应该返回任何内容，因为没有Veneanâr"的条目.

But it should return nothing, because there is no entry for "Veneanâr".

我的PHP脚本(使用PDO语句)在PHPMyAdmin中甚至在mysql命令行中都会出现此问题.

This problem occurs in my PHP Script (using PDO Statement) in PHPMyAdmin and even in the mysql command line.

我的PHP脚本检查字符名称是否已经存在并执行更新，否则将创建一个新条目.我必须创建"Veneanar"和Veneanâr"，但是MySQL声称已经存在Veneanâr"(不正确)，脚本用错误的数据更新了该条目.

My PHP script checks if a charname already exists and performs an update, if not it creates a new entry. I have to create "Veneanar" and "Veneanâr" but while MySQL claims there is already an "Veneanâr" (what is not true) the script updates the entry with wrong data.

我不明白为什么会这样.我的mysql配置有什么问题吗?

I cannot understand why this happens. Is there any problem with my mysql configuration?

推荐答案

我相信在您的情况下，您只需从列定义中删除COLLATE并为整个表设置COLLATE=utf8_bin:

I believe in your case you can just remove COLLATE from columns definition and set COLLATE=utf8_bin for whole table:

CREATE TABLE IF NOT EXISTS `wcf1_guildtool_playerdata` (
  `charID` int(11) NOT NULL,
  `charname` varchar(20) NOT NULL,
  `realmname` varchar(25)  NOT NULL,
  `class` int(11) DEFAULT NULL,
  `race` int(11) DEFAULT NULL,
  `gender` int(11) NOT NULL,
  `level` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

在这里使用表定义和其他查询: http://sqlfiddle.com/#!9/40907/3

play with table definition and different queries here: http://sqlfiddle.com/#!9/40907/3

这篇关于MySQL LIKE无法与“â"一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！