回忆之城
生命在于折腾
posts - 510,comments - 9,trackbacks - 0
 今天 给开发更新了表结构后,遇到了这样的问题:
  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

  参阅:http://www.phwinfo.com/forum/comp-databases-mysql/317343-error-1062-23000-duplicate-entry-0-key-1-a.html
posted on 2013-01-17 15:14 回忆之城 阅读(6075) 评论(0)  编辑 收藏 引用 所属分类: 测试技术相关
只有注册用户登录后才能发表评论。