良好的MySQL表设计,需要有一个自增字段(一般命名为id),在表设计之初,添加上就好,但是对现存的不规范的表进行改造,该如何做呢?

  • 1、表不存在PRIMARY KEY字段,新增一个AUTO_INCREMENT字段

初始表结构如下:

CREATE TABLE `table_a` (
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商户后台管理员ID,对应sys_user表的user_id',
  `agent_name` varchar(100) NOT NULL DEFAULT '' COMMENT '代理商名称',
  `contact_mobile` varchar(15) NOT NULL DEFAULT '' COMMENT '联系人手机号',
  `contact_address` varchar(100) NOT NULL DEFAULT '' COMMENT '联系人地址'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='代理商信息';

这个表没有自增字段,需要这个这个表新增一个AUTO_INCREMENT字段:

alter table table_a add column `agent_id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '代理商id' first;
-- 新增AUTO_INCREMENT字段,字段名为agent_id(一般命名为id,这里只是演示一下别样的命名)
-- AUTO_INCREMENT必须是整数类型数据列
-- first表示新增的字段放在第一位
-- 自增字段必须是唯一的,所以设置为PRIMARY KEY

执行上面那条DDL语句之后:

CREATE TABLE `table_a` (
  `agent_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '代理商id',
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商户后台管理员ID,对应sys_user表的user_id',
  `agent_name` varchar(100) NOT NULL DEFAULT '' COMMENT '代理商名称',
  `contact_mobile` varchar(15) NOT NULL DEFAULT '' COMMENT '联系人手机号',
  `contact_address` varchar(100) NOT NULL DEFAULT '' COMMENT '联系人地址',
  PRIMARY KEY (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='代理商信息'

这个时候,table_a表就有自增字段了。


  • 2、表已经存在PRIMARY KEY字段,新增一个AUTO_INCREMENT字段

这种情况稍微复杂点,需要:

1.删除primary key字段

2.新增自增字段

初始表结构如下:

CREATE TABLE `table_a` (
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商户后台管理员ID,对应sys_user表的user_id',
  `agent_name` varchar(100) NOT NULL DEFAULT '' COMMENT '代理商名称',
  `contact_mobile` varchar(15) NOT NULL DEFAULT '' COMMENT '联系人手机号',
  `contact_address` varchar(100) NOT NULL DEFAULT '' COMMENT '联系人地址',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='代理商信息'

如果我们直接执行以下DDL脚本,给表添加自增字段:

alter table table_a add column `agent_id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '代理商id' first;

执行之后会报错:
SQL 错误 [1068] [42000]: Multiple primary key defined
  Multiple primary key defined
  Multiple primary key defined
失败原因:一个表最多只能有一个primary key。

正确的做法:

-- 先删除表中的primary key
alter table table_a drop primary key;

此时表的结构如下:

CREATE TABLE `table_a` (
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商户后台管理员ID,对应sys_user表的user_id',
  `agent_name` varchar(100) NOT NULL DEFAULT '' COMMENT '代理商名称',
  `contact_mobile` varchar(15) NOT NULL DEFAULT '' COMMENT '联系人手机号',
  `contact_address` varchar(100) NOT NULL DEFAULT '' COMMENT '联系人地址'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='代理商信息'

删除primary key之后,就可以执行添加自增字段脚本:

alter table table_a add column `agent_id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '代理商id' first;

此时表的结构如下:

CREATE TABLE `table_a` (
  `agent_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '代理商id',
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商户后台管理员ID,对应sys_user表的user_id',
  `agent_name` varchar(100) NOT NULL DEFAULT '' COMMENT '代理商名称',
  `contact_mobile` varchar(15) NOT NULL DEFAULT '' COMMENT '联系人手机号',
  `contact_address` varchar(100) NOT NULL DEFAULT '' COMMENT '联系人地址',
  PRIMARY KEY (`agent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='代理商信息'

  • 3、表已经存在PRIMARY KEY字段,PRIMARY KEY字段设置为AUTO_INCREMENT字段

前提:现有的primary key字段类型必须是整数类型

初始表结构如下:

CREATE TABLE `table_a` (
  `user_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '商户后台管理员ID,对应sys_user表的user_id',
  `agent_name` varchar(100) NOT NULL DEFAULT '' COMMENT '代理商名称',
  `contact_mobile` varchar(15) NOT NULL DEFAULT '' COMMENT '联系人手机号',
  `contact_address` varchar(100) NOT NULL DEFAULT '' COMMENT '联系人地址',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='代理商信息'

 

  • 3.1 表table_a数据为空

现在,把table_a的字段user_id设置成自增类型:

alter table table_a modify column `user_id` bigint(20) NOT NUll DEFAULT 0 AUTO_INCREMENT COMMENT '商户后台管理员ID,对应sys_user表的user_id';

执行之后报错:

SQL 错误 [1067] [42000]: Invalid default value for 'user_id'
  Invalid default value for 'user_id'
  Invalid default value for 'user_id'

失败原因:AUTO_INCREMENT字段不能有默认值。

取消默认值:

alter table table_a modify column `user_id` bigint(20) NOT NUll AUTO_INCREMENT COMMENT '商户后台管理员ID,对应sys_user表的user_id';

执行成功,此时表的结构如下:

CREATE TABLE `table_a` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商户后台管理员ID,对应sys_user表的user_id',
  `agent_name` varchar(100) NOT NULL DEFAULT '' COMMENT '代理商名称',
  `contact_mobile` varchar(15) NOT NULL DEFAULT '' COMMENT '联系人手机号',
  `contact_address` varchar(100) NOT NULL DEFAULT '' COMMENT '联系人地址',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='代理商信息'

  • 3.2 表table_a数据不为空,且user_id列有负数值

还是以table_a初始表结构做测试,table_a表初始数据为:

把table_a的字段user_id设置成自增类型:

alter table table_a modify column `user_id` bigint(20) NOT NUll AUTO_INCREMENT COMMENT '商户后台管理员ID,对应sys_user表的user_id';

执行成功,此时表的结构如下:

CREATE TABLE `table_a` (
  `user_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '商户后台管理员ID,对应sys_user表的user_id',
  `agent_name` varchar(100) NOT NULL DEFAULT '' COMMENT '代理商名称',
  `contact_mobile` varchar(15) NOT NULL DEFAULT '' COMMENT '联系人手机号',
  `contact_address` varchar(100) NOT NULL DEFAULT '' COMMENT '联系人地址',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COMMENT='代理商信息'

所以,网上有人说AUTO_INCREMENT字段需要设置为UNSIGNED无符号数字类型,这是不对的。

 

 

Logo

开放原子开发者工作坊旨在鼓励更多人参与开源活动,与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动,如meetup、训练营等,主打技术交流,干货满满,真诚地邀请各位开发者共同参与!

更多推荐