I create a tool to leech users for my xenforo forum but I have a mistake with 2 users, such as sample below.
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);
In the above example, N'BoyBuon1994' and N'BoyBuồn1994' are interpreted as the same, but they are not. Someone help me.
You should use different collation
ALTER TABLE xf_user
MODIFY COLUMN `username` varchar(50) COLLATE utf8_bin DEFAULT NULL;