我完全没有想法，所以也许其他人可以回答我的问题。我们有一台来自 Percona 的 MySQL 5.5 服务器，流量很大。该应用程序是在 PHP 中编写的，始终掌握。我们同时有 4 个奴隶，我们只从中读取。基本上就是标准的主从配置。上周发生了所有从机上的复制都被破坏了，所以我检查了数据库有什么问题。我发现的基本上是我的问题，这是怎么发生的：其中一个表中的唯一键列（不是主键）在两行中具有相同的值。我试图找出这种情况是否不止一次发生，但没有。它只发生过一次，但我会理解为什么或怎么会发生这种情况。为了更好地理解，这里有一些来自我们数据库的真实数据：
show create table registeredUsers;
| registeredUsers | CREATE TABLE `registeredUsers` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`userId` varchar(32) NOT NULL,
`client` varchar(200) NOT NULL,
`osVersion` varchar(10) NOT NULL,
`deviceGroup` varchar(50) NOT NULL,
`registrationDate` datetime NOT NULL,
`lastAction` datetime NOT NULL,
`cultureLanguage` varchar(2) NOT NULL,
`cultureRegion` varchar(2) NOT NULL,
`cultureCode` varchar(5) NOT NULL DEFAULT 'de-de',
`lastPush` datetime DEFAULT NULL,
`pushToken` mediumtext,
`permaToken` varchar(74) DEFAULT NULL,
`accessCount` int(11) NOT NULL DEFAULT '0',
`access` varchar(1) NOT NULL DEFAULT '1',
`provider` varchar(255) NOT NULL,
`providerTld` varchar(5) NOT NULL,
`environment` tinyint(1) DEFAULT '0',
`udidMd5` varchar(32) NOT NULL,
`development` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `userId_2` (`userId`),
UNIQUE KEY `permaAccessToken_2` (`permaToken`),
KEY `client` (`client`),
KEY `lastAction` (`lastAction`),
KEY `deviceGroup` (`deviceGroup`),
KEY `osVersion` (`osVersion`),
KEY `cultureCode` (`cultureCode`),
KEY `udidMd5` (`udidMd5`)
) ENGINE=InnoDB AUTO_INCREMENT=38466378 DEFAULT CHARSET=utf8 |
有问题的列是 userId，它是唯一的。这是一个查询，显示我们有 2 行具有相同的值：
mysql> select userId, count(userId) as ct from registeredUsers group by userId having ct;
+----------------------------------+----+
| userId | ct |
+----------------------------------+----+
| 748ec561dbc733452bfd697076787ef9 | 2 |
+----------------------------------+----+
1 row in set (3.53 sec)
我什至无法重现，所以如果有人对这种情况有解释，那就太好了。
提前谢谢你，塔马斯
更新 按照这里的要求是整理的结果：
mysql> SHOW FULL COLUMNS FROM registeredUsers;
+------------------+---------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+------------------+---------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
| id | bigint(20) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | |
| userId | varchar(32) | utf8_general_ci | NO | UNI | NULL | | select,insert,update,references | |
| client | varchar(200) | utf8_general_ci | NO | MUL | NULL | | select,insert,update,references | |
| osVersion | varchar(10) | utf8_general_ci | NO | MUL | NULL | | select,insert,update,references | |
| deviceGroup | varchar(50) | utf8_general_ci | NO | MUL | NULL | | select,insert,update,references | |
| registrationDate | datetime | NULL | NO | | NULL | | select,insert,update,references | |
| lastAction | datetime | NULL | NO | MUL | NULL | | select,insert,update,references | |
| cultureLanguage | varchar(2) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
| cultureRegion | varchar(2) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
| cultureCode | varchar(5) | utf8_general_ci | NO | MUL | de-de | | select,insert,update,references | |
| lastPush | datetime | NULL | YES | | NULL | | select,insert,update,references | |
| pushToken | mediumtext | utf8_general_ci | YES | | NULL | | select,insert,update,references | |
| permaToken | varchar(74) | utf8_general_ci | YES | UNI | NULL | | select,insert,update,references | |
| accessCount | int(11) | NULL | NO | | 0 | | select,insert,update,references | |
| access | varchar(1) | utf8_general_ci | NO | | 1 | | select,insert,update,references | |
| provider | varchar(255) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
| providerTld | varchar(5) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
| environment | tinyint(1) | NULL | YES | | 0 | | select,insert,update,references | |
| udidMd5 | varchar(32) | utf8_general_ci | NO | MUL | NULL | | select,insert,update,references | |
| development | tinyint(1) | NULL | YES | | 0 | | select,insert,update,references | |
+------------------+---------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+
20 rows in set (0.00 sec)
更新 2
mysql> SELECT id FROM registeredUsers WHERE userid = '748ec561dbc733452bfd697076787ef9' ORDER BY id DESC ;
+----------+
| id |
+----------+
| 38245456 |
+----------+
1 row in set (0.00 sec)
mysql> select userId from registeredUsers where id in (38245456, 38245457);
+----------------------------------+
| userId |
+----------------------------------+
| 748ec561dbc733452bfd697076787ef9 |
| 748ec561dbc733452bfd697076787ef9 |
+----------------------------------+
2 rows in set (0.00 sec)
mysql> select id, userId from registeredUsers where id in (38245456, 38245457);
+----------+----------------------------------+
| id | userId |
+----------+----------------------------------+
| 38245456 | 748ec561dbc733452bfd697076787ef9 |
| 38245457 | 748ec561dbc733452bfd697076787ef9 |
+----------+----------------------------------+
2 rows in set (0.00 sec)
mysql> select id, userId from registeredUsers where userId = '748ec561dbc733452bfd697076787ef9';
+----------+----------------------------------+
| id | userId |
+----------+----------------------------------+
| 38245456 | 748ec561dbc733452bfd697076787ef9 |
+----------+----------------------------------+
1 row in set (0.00 sec)
更新 3 可以肯定的是，这两个字符串是相同的，这里是一个返回所有 2 行的查询。（感谢它的建议）
SELECT id FROM registeredUsers WHERE userid >= '748ec561dbc733452bfd697076787ef9' LIMIT 2;
+----------+
| id |
+----------+
| 38245456 |
| 38245457 |
+----------+
2 rows in set (0.00 sec)