我有这个表定义：
mysql> DESCRIBE watched;
+---------+--------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------+------+-----+---------+----------------+
| id | int(6) | NO | PRI | NULL | auto_increment |
| stream | int(3) | NO | | 0 | |
| month | date | NO | | NULL | |
| watched | int(6) | NO | UNI | 1 | |
+---------+--------+------+-----+---------+----------------+
这是里面的数据：
mysql> SELECT * FROM watched;
+----+--------+------------+---------+
| id | stream | month | watched |
+----+--------+------------+---------+
| 1 | 11 | 2017-10-01 | 2 |
| 2 | 11 | 2017-10-01 | 1 |
+----+--------+------------+---------+
2 rows in set (0.00 sec)
当我运行此查询时：
INSERT INTO watched (stream, month) VALUES (11, DATE_FORMAT(NOW(), "%Y-%m-01")) ON DUPLICATE KEY UPDATE watched=watched+1;
我收到此错误：
ERROR 1062 (23000): Duplicate entry '2' for key 'watched'
id设置为PRIMARY和AUTO_INCREMENT并将watched设置为UNIQUE，以便我可以在sql语句watched = watched + 1中增加或减少watched的值
但是我不知道如何设置UNIQUE监视字段以将值增加或减少一个...
编辑：
这是表结构：
mysql> SHOW CREATE TABLE watched;
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| watched | CREATE TABLE `watched` (
`id` int(6) NOT NULL AUTO_INCREMENT,
`stream` int(3) NOT NULL DEFAULT '0',
`month` date NOT NULL,
`watched` int(6) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`),
UNIQUE KEY `watched` (`watched`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 |
+---------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
最佳答案
我只需要按照迈克尔的建议从watched.watched中删除UNIQUE，使用：
ALTER TABLE watched DROP INDEX watched
如果未插入新记录，则为流创建UNIQUE，好像找到了一样，增加counter + 1：
ALTER TABLE watched ADD UNIQUE (stream);
现在错误消失了，计数器是+1，一切都很好...上帝保佑MySQL :)
关于mysql - MySQL错误1062(23000): key “已监视”的条目“2”重复吗？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/46548666/