mySQL:索引表上的utf8字符集和重复键错误 [英] mySQL: utf8 charset on index table and duplicate key error

问题描述

当使用以utf8编码的varchar字段作为主键时，我曾期待过mySQL的奇怪行为.对于在我的开发环境中不相等的字符串，它会因重复的键错误而失败.

I have expected a strange behavior of mySQL when using a varchar field encoded in utf8 as primary key. It fails with duplicate key error for strings which aren't equal in my development environment.

一个简短的例子:

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ß');

失败，错误:Duplicate entry 'daß' for key 'PRIMARY'.

我正在使用默认配置在ubuntu 13.10上运行mySQL 5.5.35.

I am running mySQL 5.5.35 on ubuntu 13.10 with default configuration.

在另一个mySQL Server(版本5.0.95)上，相同的查询没有失败.是因为mySQL版本，还是有配置选项来设置索引表的编码?

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

在尝试从生产服务器将mySQL转储导入到开发环境时遇到了这个问题.

I run into this issue while trying to import a mySQL dump from productive server into my development environment.

推荐答案

根据此bug的讨论，使用德语字符时应使用排序规则utf8_unicode_ci:

You should use the collation utf8_unicode_ci when you use German characters, according to the discussion in this bug: Bug #39816 German collation under utf8_unicode_ci is incorrect.

尽管有该错误的标题，我还是在5.6.15上对其进行了测试，并且您的测试用例仍然有效，而默认的Unicode排序规则不起作用:

Despite the title of that bug, I just tested this on 5.6.15, and your test case works, whereas the default unicode collation does not work:

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:我建议您在开发环境中使用与生产环境相同的所有软件版本，或者至少共享相同的主要版本.如果您在5.5中进行开发，然后尝试部署到5.0，则势必会遇到其他不兼容的情况.

PS: I recommend that you use a development environment the same versions of all software as your production environment, or at least share the same major version. You're bound to run into other incompatibilities if you develop in 5.5 and then try to deploy to 5.0.

这篇关于mySQL:索引表上的utf8字符集和重复键错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！