MySQL:给原有表主键添加 AUTO_INCREMENT

迷南。 2022-07-17 00:41 112阅读 0赞

一：表结构及数据如下

CREATE TABLE `tt` (
      `id` int(10) NOT NULL ,
      `username` varchar(255) DEFAULT NULL,
      `password` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=innodb DEFAULT CHARSET=utf8;
    
    insert into tt values(0,'fyl','123qwe'),(1,'fff','12qw'),(3,'qwe','3edcw');
    
    (root:CS:3308) [test]> select * from tt;
    +----+----------+----------+
    | id | username | password |
    +----+----------+----------+
    |  0 | fyl      | 123qwe   | 
    |  1 | fff      | 12qw     | 
    |  3 | qwe      | 3edcw    | 
    +----+----------+----------+

现在要为表添加 AUTO\_INCREMENT

修改主键，需要先删除主键在添加
    ALTER TABLE tt DROP PRIMARY KEY;
    ALTER TABLE tt CHANGE COLUMN id id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;
    报错：
    ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'
    
    解决办法：
    (root:CS:3308) [test]> delete from tt where id=0;
    Query OK, 1 row affected (0.00 sec)
    (root:CS:3308) [test]> ALTER TABLE tt CHANGE COLUMN id id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    (root:CS:3308) [test]> select * from tt;
    +----+----------+----------+
    | id | username | password |
    +----+----------+----------+
    |  1 | fff      | 12qw     | 
    |  3 | qwe      | 3edcw    | 
    +----+----------+----------+
    2 rows in set (0.00 sec)

原因是：

NO_AUTO_VALUE_ON_ZERO影响AUTO_INCREMENT列的处理。一般情况，你可以向该列插入NULL或0生成下一个序列号。NO_AUTO_VALUE_ON_ZERO禁用0，因此只有NULL可以生成下一个序列号。原有数据id=0，添加自增属性会把id=0变为id=1.

二：实验如下：

(root:CS:3308) [test]> select * from tt;
    +----+----------+----------+
    | id | username | password |
    +----+----------+----------+
    |  0 | fyl      | 123qwe   | 
    |  3 | qwe      | 3edcw    | 
    +----+----------+----------+
    2 rows in set (0.00 sec)
    
    (root:CS:3308) [test]> ALTER TABLE tt DROP PRIMARY KEY;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    (root:CS:3308) [test]> ALTER TABLE tt CHANGE COLUMN id id int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY;
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    
    (root:CS:3308) [test]> select * from tt;
    +----+----------+----------+
    | id | username | password |
    +----+----------+----------+
    |  1 | fyl      | 123qwe   | 
    |  3 | qwe      | 3edcw    | 
    +----+----------+----------+
    2 rows in set (0.00 sec)

username=fyl的id值  由0变成了1

三：删除主键时，有可能报错

ERROR 1025 (HY000): Error on rename of '.\course_design\#sql-898_3' to '.\course_design\course' (errno: 150)
    [root@zp1 ~]# perror 150
    MySQL error code 150: Foreign key constraint is incorrectly formed
    原因：有外键引用到该主键
    
    解决办法：
    根据以下sql找到引用该主键的外键
    SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME ='course';
    找到外键后，可删除外键(不建议使用外键，具体情况具体分析)
    ALTER TABLE course  DROP FOREIGN KEY `member_list_ibfk_1`;