Geek Answers Handbook

MySQL: utf8 charset in index table and repeated key error

I was expecting strange mySQL behavior when using the varchar field encoded in utf8 as the primary key. It does not work with a repeating key error for strings that are not equal in my development environment.

A brief example:

SET NAMES 'utf8'; CREATE TABLE `test` ( `id` varchar(5) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test` (`id`) VALUES ('das'), ('daß');

Error with error: Duplicate entry 'daß' for key 'PRIMARY'.

I am running mySQL 5.5.35 on ubuntu 13.10 with the default setting.

On another mySQL server (version 5.0.95), the same queries did not work. Is this because of the mySQL version or is there a configuration option to set the encoding of index tables?

I encountered this problem when trying to import a mySQL dump from a productive server into a development environment.

+7
source share
3 answers

You should use the utf8_unicode_ci collation when you use German characters, as discussed in this error: Error # 39816 The German collation under utf8_unicode_ci is incorrect .

Despite the name of this error, I just tested it on 5.6.15, and your test case works, while unicode sorting doesn't work by default:

 CREATE TABLE `test` ( `id` varchar(5) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE=utf8_unicode_ci; INSERT INTO `test` (`id`) VALUES ('das'), ('daß');

PS: I recommend that you use the development environment with the same versions of all software as your production environment, or at least use the same major version. You must encounter other incompatibilities if you are running version 5.5 and then trying to deploy to 5.0.

+1
source

the problem is that the database did not recreate daß , it was converted as das , and then it becomes a double entry for id, whereas this is just a test table

why not create a column id with autoincrenebt and a different column name where you have such values.

 SET NAMES 'utf8'; CREATE TABLE `test` ( `id` int(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, `name` varchar(5) ) ENGINE=MYISAM DEFAULT CHARSET=utf8; INSERT INTO `test` (`name`) VALUES ('das'), ('daß');

CLOCK DEMO HERE

There was a problem with your question here with German charcters.

+1
source

utf8_general_mysql500_ci and utf8_general_ci differ depending on whether ß treated as s or not.

To "fix" incompatibilities, the utf8_general_mysql500_ci collation was added to MySQL 5.1.62 / 5.5.21 / 5.6.5.

More history: http://mysql.rjweb.org/doc.php/charcoll#german_sharp_s_

0
source

More articles:


All Articles