文章目录
一、前言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怎么办**=========/9/9 11:07更新 ========**/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
,谢谢了!
完成功能很简单,但是想要弄明白点,就势必要付出时间去测试了。博主这边测试的也不够多,欢迎各位同僚前来探讨,哈哈。
=========/9/9 11:07更新 ========
/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
如果觉得《mysql在已有主键的表中新增自增长字段》对你有帮助,请点赞、收藏,并留下你的观点哦!