MYSQL InnoDB 唯一键不能正常工作？

Question

我有一个非常奇怪的问题，我不知道是什么导致了它。任何建议将不胜感激。这是表结构和查询:

CREATE TABLE `big_test` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT,
  `application_id` int(11) unsigned NOT NULL DEFAULT '0',
  `account_id` int(11) unsigned NOT NULL DEFAULT '0',
  `dev_id` char(128) NOT NULL DEFAULT '',
  `gid` char(255) NOT NULL DEFAULT '',
  `name` char(20) NOT NULL DEFAULT '',
  `age` int(11) unsigned NOT NULL DEFAULT '0',
  `image` longblob NOT NULL,
  `updatedon` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `eik` bigint(11) unsigned NOT NULL DEFAULT '1',
  `me` int(2) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `gid` (`gid`,`application_id`,`account_id`),
  KEY `dev_id` (`dev_id`),
  KEY `account_id` (`account_id`)
) ENGINE=InnoDB AUTO_INCREMENT=35796304 DEFAULT CHARSET=utf8


mysql> SELECT gid, application_id, account_id FROM big_test WHERE account_id=14811 AND gid='TEST773475616236';
+-------------------------------------------------------------+----------------+------------+
| gid              | application_id | account_id |
+-------------------------------------------------------------+----------------+------------+
| TEST773475616236 |           1655 |      14811 |
| TEST773475616236 |           1655 |      14811 |
+-------------------------------------------------------------+----------------+------------+
2 rows in set (0.00 sec)

mysql> SELECT gid, application_id, account_id FROM big_test WHERE account_id=14811 AND gid='TEST773475616236' AND application_id=1655;
+-------------------------------------------------------------+----------------+------------+
| gid              | application_id | account_id |
+-------------------------------------------------------------+----------------+------------+
| TEST773475616236 |           1655 |      14811 |
+-------------------------------------------------------------+----------------+------------+
1 row in set (0.00 sec)

mysql> SELECT gid, application_id, account_id FROM big_test WHERE account_id=14811 AND gid='TEST773475616236' AND application_id<>1655;
Empty set (0.00 sec)

mysql> 

所有用于插入/更新操作的查询都是“REPLACE”。例如:

mysql> REPLACE INTO big_test SET gid='TEST773475616236', application_id=1655, account_id=14811, name='Charlie';

那怎么可能，UNIQUE KEY 不起作用？

Answer 1

我不确定您运行的环境，但“NAME”是 MySQL 中的保留字。我在其他平台上遇到了问题，您需要将其反勾，或在替换语句中将其更改为 big_test.name。

-詹姆斯