mysql primary重复_mysql：键'PRIMARY'和奇怪的ID行为重复条目'0'

简而言之：不知何故，行被赋予ID 0。当发生这种情况时，即使这些插入实际上没有与ID 0冲突，首先不会发生)。

虽然它被大量读取并且插入非常严重(高达〜300k行/分钟)，但该表格从不更新。插入的唯一方法是导致如下所示的INSERT INTO查询的方法。没有外键或类似的东西。

a)WTF？ b)如何解决？

谢谢！

$ mysql --version

mysql Ver 14.14 Distrib 5.1.30, for apple-darwin9.4.0 (i386) using readline 5.1

$ mysql

mysql> SHOW CREATE TABLE visitations \G

*************************** 1. row ***************************

Table: visitations

Create Table: CREATE TABLE `visitations` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`scraping_id` int(11) NOT NULL,

`site_id` int(11) NOT NULL,

`visited` tinyint(1) NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `index_visitations_on_scraping_id_and_site_id` (`scraping_id`,`site_id`),

KEY `index_visitations_on_site_id` (`site_id`)

) ENGINE=InnoDB AUTO_INCREMENT=23525407 DEFAULT CHARSET=latin1

1 row in set (0.00 sec)

mysql> show triggers;

Empty set (0.04 sec)

mysql> INSERT INTO `visitations` (`scraping_id`,`site_id`,`visited`) VALUES (647,196,0),(647,51679,0),(647,13689,0),(647,85739,1),(647,4388,0),(647,100346,0),(647,1245,0),[snip];

ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'

mysql> SELECT * FROM `visitations` WHERE (`scraping_id`,`site_id`,`visited`) IN ((647,196,0),(647,51679,0),(647,13689,0),(647,85739,1),(647,4388,0),(647,100346,0),(647,1245,0),[snip]);

Empty set (1 min 27.43 sec)

mysql> select * from visitations where id = 0;

+----+-------------+---------+---------+

| id | scraping_id | site_id | visited |

+----+-------------+---------+---------+

| 0 | 645 | 46177 | 0 |

+----+-------------+---------+---------+

1 row in set (0.00 sec)

mysql> delete from visitations where id < 363;

Query OK, 363 rows affected (0.11 sec)

mysql> select * from visitations where id = 0;

Empty set (0.00 sec)

mysql> INSERT INTO `visitations` (`scraping_id`,`site_id`,`visited`) VALUES (647,196,0),(647,51679,0),(647,13689,0),(647,85739,1),(647,4388,0),(647,100346,0),(647,1245,0),[snip];

Query OK, 500 rows affected (0.23 sec)

Records: 500 Duplicates: 0 Warnings: 0

mysql> select * from visitations where id = 0;

Empty set (0.00 sec)

mysql> INSERT INTO `visitations` (`scraping_id`,`site_id`,`visited`) VALUES (647,196,0),(647,51679,0),(647,13689,0),(647,85739,1),(647,4388,0),(647,100346,0),(647,1245,0),[snip];

ERROR 1062 (23000): Duplicate entry '647-196' for key 'index_visitations_on_scraping_id_and_site_id'

2010-03-05

Sai