mysql在已有主键的表中新增自增长字段

小咪咪 2024-04-18 15:19 140阅读 0赞

### 文章目录 ###

*   *  一、前言
     *   *  1、目标需求：
         *  2、数据表结构如下：
     *  二、给数据表加上自增长且唯一索引字段(无数据)
     *   *  1、给数据表新增自增字段的前提
         *  2、给没有主键的表新增自增长字段
         *  3、给已有主键的表新增自增长字段
         *  4、关于重新更改字段的属性问题
         *  5、查看数据库结构
     *  三、给数据表加上自增长且唯一索引字段(有数据)
     *   *  1、表中新增3行数据，并按照上面方法进行设置
         *  2、试试varchar类型字段
         *   *  （1）先varchar()类型，后续改为int类型：
             *  （2）改为普通的key
             *  （3）查看表结构
         *  3、使用默认的索引设置
     *  四、其他问题
     *   *  1、使用尽可能简洁的sql
         *   *  （1）一句sql试试
             *  （2）两句sql试试
         *  2、设置之后，所有表的autoincrememt都是从下一条记录开始增长的。
         *  3、如果表中有数据，设置自增长和unique怎么办
     *  \*\*=========2019/9/9 11:07更新 ========\*\*
     *  2019/9/9 11:07更新：
     *   *  查看表结构：

## 一、前言 ##

如标题所示，给数据表加自增长字段是很简单的事情，但是如果这个表已经有主键，并且存在数据的情况下，怎么给数据表加自增长主键呢？下面咱们来详细说一下这个东东。

### 1、目标需求： ###

（1）数据表没有id字段，但是有个char类型的字段作为主键使用。
    （2）表中没有其他自增长的字段
    （3）但是根据需求，需要重新加上id字段，新加的字段不用是主键，但是这个字段必须是自增长且是唯一字段。

### 2、数据表结构如下： ###

| redeem_code | CREATE TABLE `redeem_code` (
      `code` char(10) NOT NULL,
      `gift_id` int(10) unsigned NOT NULL DEFAULT '0',
      `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
      PRIMARY KEY (`code`) USING BTREE,
      KEY `idx_gift_id_user_id` (`gift_id`,`user_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

## 二、给数据表加上自增长且唯一索引字段(无数据) ##

### 1、给数据表新增自增字段的前提 ###

但如果表中已经有了主键，我们要添加其他字段的属性为自增长，那么必须满足条:

（1）这个字段是int类型
    （2）这个字段带有索引key，最好是unique，不过普通的key也是可以的
    （3）这个字段不能有默认值（因为默认字段是相同的，会影响到unique key的添加）

### 2、给没有主键的表新增自增长字段 ###

如果表中没有主键也没有自增长的时候，可以一句话加上去：

alter table redeem_code change id id int not null auto_increment primary key;

但是如果表已经创建成功，并且设置完主键之后，那么这句话就不会有什么作用，反而会报错，报错信息大概是：数据表中不能存在多个主键。

### 3、给已有主键的表新增自增长字段 ###

这边博主本来是想一句`sql`就完事的，但根据上面咱们查到的结果，新增的字段必须是有索引的，而索引和新增字段写在一个`sql`,目前博主只会一种写法，就是在新增字段的时候添加`unique key`，其他的写法还不知道。因此这边是分句执行的，结果如下：

alter table redeem_code add id int(10) not Null;   //不要设置default 0，会报错：Invalid default value for ‘id’
    alter table redeem_code add unique idx_id(`id`);   //设置唯一索引，方便加自增长属性
    alter table redeem_code modify column id int(10) not null AUTO_INCREMENT;  //这句执行成功

### 4、关于重新更改字段的属性问题 ###

细心的同学应该注意到了，上面最后设置自增长属性的时候，博主对字段的其他属性进行了重新定义，这是为什么呢？

注意：alter table redeem_code modify column id AUTO_INCREMENT;   //直接执行这句会报错

直接设置`id`字段的类型为自增长，`mysql`会报错，具体的报错信息忘记保存了，不过在博主的不断尝试下，发现重新定义字段属性的时候，是可以设置自增长的。

**推测原因：**  
      推测是因为对于字段加自增长的时候，对于`mysql`的表结构是有影响的。我们都知道一个数据表中只能有一个自增长列，虽然这个表中有主键`code`，但这个`code`是个随机值，`mysql`每次存储数据，并不会顺序写入数据页，而是随机写入，然后还要不断的移动表数据，适应插入的`LRU`算法，从而造成性能的浪费。但是如果这个时候，表中有自增长的字段，猜测`mysql`是会依据这个字段和主键共同进行数据的定位的，所以原来这个字段的属性结构要重新定义，并且也务必要保证自增长的字段是`Int`类型的。（**PS:如果大佬们有更权威的说法，欢迎一起讨论！**）

### 5、查看数据库结构 ###

------------------------------------------------------------------------------------+
    | redeem_code | CREATE TABLE `redeem_code` (
      `code` char(10) NOT NULL,
      `gift_id` int(10) unsigned NOT NULL DEFAULT '0',
      `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
      `id` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`code`) USING BTREE,
      UNIQUE KEY `idx_id` (`id`),
      KEY `idx_gift_id_user_id` (`gift_id`,`user_id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

OK，这里看到我们已经成功设置新字段`id`为自增长且是`unique key`的了。但是设置成功的前提是这个表中没有其他数据。下面我们试一下当有数据的情况下，怎么设置自增长字段。

## 三、给数据表加上自增长且唯一索引字段(有数据) ##

### 1、表中新增3行数据，并按照上面方法进行设置 ###

+---------+---------+---------+----+
    | code    | gift_id | user_id | id |
    +---------+---------+---------+----+
    | 111111  |      11 |     111 |  0 |
    | 2222222 |      22 |     222 |  0 |
    | 333333  |      33 |     333 |  0 |
    +---------+---------+---------+----+

执行到添加`unique KEY`的时候就报错了，因为是`Int`类型，而且在我们没有设置默认值的情况下，默认加进去的`id`都是`0`，所以重复了，不符合添加`unique key`的条件。

### 2、试试varchar类型字段 ###

#### （1）先varchar()类型，后续改为int类型： ####

alter table redeem_code add id varchar(10) not Null default '1'; 
    alter table redeem_code add unique idx_id(`id`);  //Duplicate entry '' for key 'idx_id'  
    	还是不行，毕竟多条数据的这个字段的值是相同的。试试普通的index试试

#### （2）改为普通的key ####

alter table redeem_code add id varchar(10) not Null default '1'; 
    alter table redeem_code add index idx_id(`id`);   //普通的key是可以的
     alter table redeem_code modify column id int(10) not null AUTO_INCREMENT;   //成功了，

#### （3）查看表结构 ####

| redeem_code | CREATE TABLE `redeem_code` (
      `code` char(10) NOT NULL,
      `gift_id` int(10) unsigned NOT NULL DEFAULT '0',
      `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
      `id` int(10) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`code`) USING BTREE,
      KEY `idx_gift_id_user_id` (`gift_id`,`user_id`) USING BTREE,
      KEY `idx_id` (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT |

主要就是key的类型变了，代表普通的key还是可以的。

### 3、使用默认的索引设置 ###

alter table redeem_code add id varchar(10) not Null default '1';
    alter table redeem_code change id id int(10) not null auto_increment key;

**报错：** `Multiple primary key defined` //如果不用`unique key`的话，默认是选用`pramary key`，所以就主键重复了。

## 四、其他问题 ##

### 1、使用尽可能简洁的sql ###

#### （1）一句sql试试 ####

alter table redeem_code change  id int(10) not null auto_increment  key;

报错： Unknown column ‘id’ in ‘redeem\_code’  
原因：缺少id字段导致的，下面加上id字段就好了。

#### （2）两句sql试试 ####

alter table redeem_code add id varchar(10) not Null default '1'; 
    alter table redeem_code change id id int(10) not null auto_increment unique key;

答案是可以的。。，就是不能放在一句执行完了，必须要分开两句

### 2、设置之后，所有表的autoincrememt都是从下一条记录开始增长的。 ###

这个问题在网上查了挺久的，如果表中无数据或者数据不是很重要（量少）的话，可以先导出来，然后重新插入生成自增长的值。但如果数据量很大怎么办呢？

**个人觉得可行的方案：**

**（1）** 自动脚本程序，通过程序依次为数据表插入自增的值，但为了防止影响业务，最好是选择夜深人静数据库压力小的时候，可以考虑主从，先更新主库，业务放在从库上。

**（2）** 如果想让原来的自动增长就得复制现有表的结构(无`id`），添加`id`并加上`AUTO_INCREMENT`，然后通过循环，添加n条空记录，然后对应先前表的id，依次插入数据。如果跟其他表有关联就比较麻烦了

### 3、如果表中有数据，设置自增长和unique怎么办 ###

其实从上面的测试来看，我们发现在有数据的情况下，分步设置自增长和`unique`是会报错的，因为在设置新字段的时候，会给每行数据都加一个默认的值，而这些默认值是相同的，所以设置`unique key`的时候就会报错。

**建议方案：**

（1）先设置自增长和普通的key
    （2）使用程序把新增字段的值都按照顺序添加进去
    （3）当字段的值各不相同的时候，再修改为unique key

当然，这个方案看起来笨笨的，归根结底，还是因为不能把这些`sql`都总结到一句上去，如果有大佬看到这篇博客，那么请告知怎么一句话设置这个`sql`，谢谢了！

完成功能很简单，但是想要弄明白点，就势必要付出时间去测试了。博主这边测试的也不够多，欢迎各位同僚前来探讨，哈哈。

## **=========2019/9/9 11:07更新 ========** ##

## 2019/9/9 11:07更新： ##

大佬的力量果然是惊人的，上午经过另一位大佬的指点，终于得到一条sql就实现了咱们的需求，如下：

ALTER TABLE `redeem_code` ADD COLUMN `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT AFTER `user_id`,ADD UNIQUE INDEX `unq_id`(`id`);

### 查看表结构： ###

| redeem_code | CREATE TABLE `redeem_code` (
      `code` char(10) NOT NULL,
      `gift_id` int(10) unsigned NOT NULL DEFAULT '0',
      `user_id` bigint(20) unsigned NOT NULL DEFAULT '0',
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`code`) USING BTREE,
      UNIQUE KEY `unq_id` (`id`),
      KEY `idx_gift_id_user_id` (`gift_id`,`user_id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

经过测试，不管是有没有数据，都可以使用这条`sql`，直接就加进去了，更重要的是，加进去id字段之后，表中的`id`是会自己从`1`开始填充的，这样的话就解决了困境。表数据显示如下：

+-------+---------+---------+----+
    | code  | gift_id | user_id | id |
    +-------+---------+---------+----+
    | 11111 |       0 |       0 |  1 |
    | 2222  |      22 |       1 |  2 |
    +-------+---------+---------+----+

表中原来是有两条数据的，现在新增的id都是自增长的，完美解决了问题。这样比着咱们上面分开写sql不知道高明了多少，哈哈，学到了。本来觉得上面也没有必要存在了，但是毕竟是探索过程，还是留下吧。

**end**