今天 给开发更新了表结构后，遇到了这样的问题：
mysql> INSERT INTO `sg_medal_action` (`action_name`,`action_code`,`status`) VALUES ('121212','testCode',2);
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
问题重现：
修改前，表的结构如下：
mysql> show create table sg_medal_action\G
*************************** 1. row ***************************
Table: sg_medal_action
Create Table: CREATE TABLE `sg_medal_action` (
`action_id` int(10) unsigned NOT NULL,
`action_code` char(20) NOT NULL,
`action_name` varchar(80) NOT NULL,
`status` tinyint(4) unsigned DEFAULT '1',
PRIMARY KEY (`action_id`,`action_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
表的状态信息如下：
mysql> show table status like 'sg_medal_action'\G
*************************** 1. row ***************************
Name: sg_medal_action
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 7
Avg_row_length: 2340
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2011-11-03 16:36:12
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
表里面的内容：
mysql> select * from sg_medal_action;
+-----------+---------------+--------------------+--------+
| action_id | action_code | action_name | status |
+-----------+---------------+--------------------+--------+
| 1 | buyAddr | 购买线索数量 | 1 |
| 2 | fans | 粉丝数 | 1 |
| 3 | header | 上传头像 | 1 |
| 4 | login | 登录 | 1 |
| 5 | mark | 锚点数 | 1 |
| 6 | showGoods | 晒货数量 | 1 |
| 7 | showGoodsLove | 晒货喜欢数 | 1 |
+-----------+---------------+--------------------+--------+
7 rows in set (0.00 sec)
修改表结构语句如下：
alter table sg_medal_action drop primary key;
alter table sg_medal_action add primary key(action_id);
执行完以上操作后，表结构、表状态信息分别如下：
mysql> show create table sg_medal_action\G
*************************** 1. row ***************************
Table: sg_medal_action
Create Table: CREATE TABLE `sg_medal_action` (
`action_id` int(10) unsigned NOT NULL,
`action_code` char(20) NOT NULL,
`action_name` varchar(80) NOT NULL,
`status` tinyint(4) unsigned DEFAULT '1',
PRIMARY KEY (`action_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show table status like 'sg_medal_action'\G
*************************** 1. row ***************************
Name: sg_medal_action
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 7
Avg_row_length: 2340
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: NULL
Create_time: 2011-11-03 16:42:45
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
mysql>
执行 insert 操作，如下：
mysql> INSERT INTO `sg_medal_action` (`action_name`,`action_code`,`status`) VALUES ('121212','testCode',2);
Query OK, 1 row affected, 1 warning (0.00 sec)
插入操作执行成功，查看表中记录情况如下：
mysql> select * from sg_medal_action;
+-----------+---------------+--------------------+--------+
| action_id | action_code | action_name | status |
+-----------+---------------+--------------------+--------+
| 0 | testCode | 121212 | 2 |
| 1 | buyAddr | 购买线索数量 | 1 |
| 2 | fans | 粉丝数 | 1 |
| 3 | header | 上传头像 | 1 |
| 4 | login | 登录 | 1 |
| 5 | mark | 锚点数 | 1 |
| 6 | showGoods | 晒货数量 | 1 |
| 7 | showGoodsLove | 晒货喜欢数 | 1 |
+-----------+---------------+--------------------+--------+
8 rows in set (0.00 sec)
再执行一次插入操作，报错如下：
mysql> INSERT INTO `sg_medal_action` (`action_name`,`action_code`,`status`) VALUES ('345','Code',2);
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
这说明每次往表中插入记录的PRIMARY值均为0。
解决问题思路：
1、老的表结构，使用的复合索引——“PRIMARY KEY (`action_id`,`action_code`)”，没有指定 auto_increment 的值；
2、删除原来主键，重新创建新主键时（alter table sg_medal_action add primary key(action_id);
），也未指定 auto_increment的值，所以在创建了新主键后，再看表 sg_medal_action 的状态时， Auto_increment 一栏的值仍为 NULL；
3、mysql 不支持 “alter table sg_medal_action add auto_increment primary key(action_id);”这样的语句，请详看 alter 的语法 http://dev.mysql.com/doc/refman/5.0/en/alter-table.html ；
4、解决问题
mysql> alter table sg_medal_action modify column action_id int unsigned auto_increment;
Query OK, 7 rows affected (0.06 sec)
Records: 7 Duplicates: 0 Warnings: 0
执行了此操作后，再查看 sg_medal_action 表结构及状态信息，如下：
mysql> show create table sg_medal_action\G
*************************** 1. row ***************************
Table: sg_medal_action
Create Table: CREATE TABLE `sg_medal_action` (
`action_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`action_code` char(20) NOT NULL,
`action_name` varchar(80) NOT NULL,
`status` tinyint(4) unsigned DEFAULT '1',
PRIMARY KEY (`action_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> show table status like 'sg_medal_action'\G
*************************** 1. row ***************************
Name: sg_medal_action
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 8
Avg_row_length: 2048
Data_length: 16384
Max_data_length: 0
Index_length: 0
Data_free: 0
Auto_increment: 8
Create_time: 2011-11-03 16:25:58
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
再执行insert操作，如下：
mysql> INSERT INTO `sg_medal_action` (`action_name`,`action_code`,`status`) VALUES ('121212','testCode',2);
Query OK, 1 row affected (0.00 sec)
查询其结果：
mysql> select * from sg_medal_action;
+-----------+---------------+--------------------+--------+
| action_id | action_code | action_name | status |
+-----------+---------------+--------------------+--------+
| 1 | buyAddr | 购买线索数量 | 1 |
| 2 | fans | 粉丝数 | 1 |
| 3 | header | 上传头像 | 1 |
| 4 | login | 登录 | 1 |
| 5 | mark | 锚点数 | 1 |
| 6 | showGoods | 晒货数量 | 1 |
| 7 | showGoodsLove | 晒货喜欢数 | 1 |
| 8 | testCode | 121212 | 2 |
+-----------+---------------+--------------------+--------+
8 rows in set (0.00 sec)
再插入一行：
mysql> INSERT INTO `sg_medal_action` (`action_name`,`action_code`,`status`) VALUES ('345','Code',2);
Query OK, 1 row affected (0.01 sec)
查询结果：
mysql> select * from sg_medal_action;
+-----------+---------------+--------------------+--------+
| action_id | action_code | action_name | status |
+-----------+---------------+--------------------+--------+
| 1 | buyAddr | 购买线索数量 | 1 |
| 2 | fans | 粉丝数 | 1 |
| 3 | header | 上传头像 | 1 |
| 4 | login | 登录 | 1 |
| 5 | mark | 锚点数 | 1 |
| 6 | showGoods | 晒货数量 | 1 |
| 7 | showGoodsLove | 晒货喜欢数 | 1 |
| 8 | testCode | 121212 | 2 |
| 9 | Code | 345 | 2 |
+-----------+---------------+--------------------+--------+
9 rows in set (0.00 sec)
OK，问题 处理到此结束。
总结下：
auto_increment 与 primary key 如果不一起存在，则做 insert 操作时，必须自己指定 primary key 列的值；
小测试如下：
mysql> create table dd (i int primary key,b char(10));
Query OK, 0 rows affected (0.01 sec)
mysql> show create table dd\G
*************************** 1. row ***************************
Table: dd
Create Table: CREATE TABLE `dd` (
`i` int(11) NOT NULL,
`b` char(10) DEFAULT NULL,
PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into dd(b) values('dkf');
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from dd;
+---+------+
| i | b |
+---+------+
| 0 | dkf |
+---+------+
1 row in set (0.00 sec)
mysql> insert into dd(b) values('lll');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
mysql> select * from dd;
+---+------+
| i | b |
+---+------+
| 0 | dkf |
+---+------+
1 row in set (0.00 sec)
mysql> insert into dd values(1,'lll');
Query OK, 1 row affected (0.01 sec)
mysql> select * from dd;
+---+------+
| i | b |
+---+------+
| 0 | dkf |
| 1 | lll |
+---+------+
2 rows in set (0.00 sec)
mysql> insert into dd(b) values('lll');
ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
另外，请参阅下InnoDB引擎下update操作对atuo_increment影响的文章 http://bugs.mysql.com/bug.php?id=38839
本文转自奔跑在路上博客51CTO博客，原文链接http://blog.51cto.com/qiangsh/1626439如需转载请自行联系原作者
qianghong000
