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

mysql 专栏收录该内容
61 篇文章 11 订阅

一、前言

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

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

  • 3
    点赞
  • 12
    评论
  • 5
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值