MySQL表添加AUTO_INCREMENT列或者把现有的表字段设置为AUTO_INCREMENT

       良好的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无符号数字类型，这是不对的。