问题描述：mysql在给表新增主键时报错ERROR 1063，如下所示：

数据库：mysql 5.7.21

1、模拟异常

1.1、建测试表

mysql> create table fruits

    -> (

    -> s_id int not null,

    -> f_name char(255) not null,

    -> f_price decimal(8,2) not null

    -> );

Query OK, 0 rows affected (0.00 sec)

 

mysql> desc fruits;

+---------+--------------+------+-----+---------+-------+

| Field   | Type         | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| s_id    | int(11)      | NO   |     | NULL    |       |

| f_name  | char(255)    | NO   |     | NULL    |       |

| f_price | decimal(8,2) | NO   |     | NULL    |       |

+---------+--------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

1.2、新增字段与主键

mysql> alter table fruits add f_id char(10) not null comment '主键ID';

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> alter table fruits change f_id f_id char(10) not null comment '主键ID' auto_increment primary key;

ERROR 1063 (42000): Incorrect column specifier for column 'f_id'

 

说明：如上所示，添加主键时报错ERROR 1063.

2、异常原因

auto_increment columns must be integer type (TINYINT, SMALLINT, INTEGER, or BIGINT)

3、解决方案

--调整f_id为bigint类型.

mysql> alter table fruits drop f_id;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> desc fruits;

+---------+--------------+------+-----+---------+-------+

| Field   | Type         | Null | Key | Default | Extra |

+---------+--------------+------+-----+---------+-------+

| s_id    | int(11)      | NO   |     | NULL    |       |

| f_name  | char(255)    | NO   |     | NULL    |       |

| f_price | decimal(8,2) | NO   |     | NULL    |       |

+---------+--------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

 

mysql> alter table fruits add f_id bigint(20) not null comment '主键ID';

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> alter table fruits change f_id f_id bigint(20) not null comment '主键ID' auto_increment primary key;

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

说明：如上所示，成功添加主键.