如何为MySQL主键添加字段？

AsiaYe

发布于 2021-11-09 11:20:12

6.8K00

代码可运行

文章被收录于专栏：DBA随笔DBA随笔

运行总次数：0

代码可运行

如何为MySQL主键添加字段？

今天有个同事问了个不常见的问题，就是修改主键字段，给既定的主键添加一个字段应该用什么操作。说实话，这类操作，一般是不会发生在线上的，因为线上的表的主键，通常情况下，会建议业务用自增id值，因为自增id值既满足了主键的唯一性，又可以防止过多的数据页分裂操作，而且它的范围比较广，占用的字节数量也比较少，是一个比较合适的主键角色。

但实际的情况是，业务使用业务字段做主键，而且要给这个主键加个字段，好在这张表的数据量不多，这里我做了一个模拟

23:12:51> create table test(
    -> task_id int not null auto_increment,
    -> aaa int,
    -> bbb int,
    -> primary key (task_id));
Query OK, 0 rows affected (0.28 sec)

23:13:58> insert into test values (1,1,1),(2,2,2),(3,3,3);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

23:14:36> select * from test;
+---------+------+------+
| task_id | aaa  | bbb  |
+---------+------+------+
|       1 |    1 |    1 |
|       2 |    2 |    2 |
|       3 |    3 |    3 |
+---------+------+------+
3 rows in set (0.00 sec)

新建一张表test，插入三条数据。

这里我们的主键是task_id字段，现在我们的需求是将主键修改成task_id和aaa的组合字段。

如果直接修改，会发生如下报错：

 23:14:40> alter table test drop primary key;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

看这个错误，很容易理解，在MYSQL中自增的列，必须被设置为索引，但不一定是主键。

那如何解决这个需求呢？

 23:19:12> alter table test drop primary key,add primary key (task_id,aaa);
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

在一个SQL里面，把主键drop掉，然后再重新添加一个主键。

上面这个SQL，因为要重新组织主键，所以它的代价比较高，在数据量小的表里面可以执行，如果你的表已经有好几百万行数据，为避免对表的正常访问产生影响，请使用pt-osc或者ght-ost工具来完成这个操作。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2021-11-02，如有侵权请联系 cloudcommunity@tencent.com 删除

sql