mysql - 2个不同的UTF-8字符串上的MySQL UNIQUE错误？

我为我的xenforo论坛创建了一个工具来挖掘用户，但是我有两个用户的错误，例如下面的示例。

CREATE TABLE IF NOT EXISTS `xf_user` (
  `user_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(120) NOT NULL,
  `gender` enum('','male','female') NOT NULL DEFAULT '' COMMENT 'Leave empty for ''unspecified''',
  `custom_title` varchar(50) NOT NULL DEFAULT '',
  `language_id` int(10) unsigned NOT NULL,
  `style_id` int(10) unsigned NOT NULL COMMENT '0 = use system default',
  `timezone` varchar(50) NOT NULL COMMENT 'Example: ''Europe/London''',
  `visible` tinyint(3) unsigned NOT NULL DEFAULT '1' COMMENT 'Show browsing activity to others',
  `user_group_id` int(10) unsigned NOT NULL,
  `secondary_group_ids` varbinary(255) NOT NULL,
  `display_style_group_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'User group ID that provides user styling',
  `permission_combination_id` int(10) unsigned NOT NULL,
  `message_count` int(10) unsigned NOT NULL DEFAULT '0',
  `conversations_unread` smallint(5) unsigned NOT NULL DEFAULT '0',
  `register_date` int(10) unsigned NOT NULL DEFAULT '0',
  `last_activity` int(10) unsigned NOT NULL DEFAULT '0',
  `trophy_points` int(10) unsigned NOT NULL DEFAULT '0',
  `alerts_unread` smallint(5) unsigned NOT NULL DEFAULT '0',
  `avatar_date` int(10) unsigned NOT NULL DEFAULT '0',
  `avatar_width` smallint(5) unsigned NOT NULL DEFAULT '0',
  `avatar_height` smallint(5) unsigned NOT NULL DEFAULT '0',
  `gravatar` varchar(120) NOT NULL DEFAULT '' COMMENT 'If specified, this is an email address corresponding to the user''s ''Gravatar''',
  `user_state` enum('valid','email_confirm','email_confirm_edit','moderated','email_bounce') NOT NULL DEFAULT 'valid',
  `is_moderator` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `is_admin` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `is_banned` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `like_count` int(10) unsigned NOT NULL DEFAULT '0',
  `warning_points` int(10) unsigned NOT NULL DEFAULT '0',
  `is_staff` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `username` (`username`),
  KEY `email` (`email`),
  KEY `user_state` (`user_state`),
  KEY `last_activity` (`last_activity`),
  KEY `message_count` (`message_count`),
  KEY `trophy_points` (`trophy_points`),
  KEY `like_count` (`like_count`),
  KEY `register_date` (`register_date`),
  KEY `staff_username` (`is_staff`,`username`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

INSERT INTO xf_user (`username`, `email`, `gender`, `custom_title`, `language_id`, `style_id`, `timezone`, `visible`, `user_group_id`, `secondary_group_ids`, `display_style_group_id`, `permission_combination_id`, `message_count`, `conversations_unread`, `register_date`, `last_activity`, `trophy_points`, `alerts_unread`, `avatar_date`, `avatar_width`, `avatar_height`, `gravatar`, `user_state`, `is_moderator`, `is_admin`, `is_banned`, `like_count`, `warning_points`, `is_staff`) VALUES
(N'BoyBuon1994', 'user608@email', 'male', '', 2, 0, 'Asia/Bangkok', 1, 2, '', 2, 2, 0, 0, 1403776680, 1403776680, 0, 0, 0, 0, 0, '', 'valid', 0, 0, 0, 0, 0, 0),
(N'BoyBuồn1994', 'user635@email', 'male', '', 2, 0, 'Asia/Bangkok', 1, 2, '', 2, 2, 0, 0, 1403776680, 1403776680, 0, 0, 0, 0, 0, '', 'valid', 0, 0, 0, 0, 0, 0);

在上面的例子中，N'BoyBuon1994'和N'BoyBuồn1994'被解释为相同，但它们不是。有人帮我。

最佳答案

您应该使用不同的排序规则

ALTER TABLE xf_user
MODIFY COLUMN `username` varchar(50) COLLATE utf8_bin DEFAULT NULL;

从http://forums.mysql.com/read.php?22,394026,394256