MySQL LIKE doesn't work with "â"

Question

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

I use this table:

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;

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

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

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

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.

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

Possible duplicate of [How to conduct an Accent Sensitive search in MySql](http://stackoverflow.com/questions/500826/how-to-conduct-an-accent-sensitive-search-in-mysql) — Phil Ross, Oct 08 '15 at 18:55
`init_connect` is _not_ performed if you connect as `root`. Your application should have its own login. — Rick James, Oct 26 '15 at 02:02
thanks for the hint. But I never use root (even in phpmyadmin) and all my applications have their own login. — Sylvanas Garde, Oct 26 '15 at 17:23

score 3 · Answer 1 · answered Oct 08 '15 at 18:55

3

MySQL treats âåä as a. See documentation:

http://dev.mysql.com/doc/refman/5.1/en/charset-collation-effect.html

http://dev.mysql.com/doc/refman/5.1/en/charset-unicode-sets.html

To make a query that is strict you can do the following:

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

answered Oct 08 '15 at 18:55

Fabian Mossberg

238
4
11

Thank you for this hint. I've changed the table collation to utf8_bin, this is easier then change the SQL queries in my code (I am using a framework solution) – Sylvanas Garde Oct 08 '15 at 19:29
Well, if you want it to be exact you could just do `WHERE charname = 'Veneanâr'` instead of LIKE and you don't have to worry. Using utf8_bin might have other implications you haven't considered. See http://stackoverflow.com/questions/10929836/utf8-bin-vs-utf-unicode-ci – Fabian Mossberg Oct 08 '15 at 20:09

score 1 · Accepted Answer · answered Oct 08 '15 at 19:05

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;

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

I have changed the charname column to utf8-bin and it works! Thank you! — Sylvanas Garde, Oct 08 '15 at 19:32

score 0 · Answer 3 · answered Oct 08 '15 at 18:52

Can you not use something like:

SELECT * FROM wcf1_guildtool_playerdata WHERE charname LIKE 'Venean'+CHAR(131)+'r'

?

And why are you using LIKE here anyway?

It would make more sense:

..WHERE charname LIKE '%Venean'+CHAR(131)+'r'

Or, better yet: You can look into the use of HTMLEntities with PHP to properly encode your page and SQL query.

Ref. http://www.asciitable.com/

Ref. http://php.net/manual/en/function.htmlspecialchars.php

Ref. http://php.net/manual/en/function.htmlentities.php

Hope this helps!!

MySQL LIKE doesn't work with "â"

3 Answers3

Linked