将其他字段设置为自增主键

1、创建测试表，查看表结构

mysql> desc test_autoinc;

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

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

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

| id | int(11) | NO | PRI | NULL | auto_increment |

| c1 | int(11) | YES | UNI | NULL | |

| c2 | varchar(100) | YES | | NULL | |

| id_no | int(20) | NO | | NULL | |

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

4 rows in set (0.00 sec)

2、将其他字段设置为自增主键（当前表上已经有自增主键，设置其他字段为自增主键报错）

mysql>

mysql> alter table test_autoinc modify id_no int(11) auto_increment,add primary key(id_no);

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql>

mysql> desc test_autoinc;

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

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

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

| id | int(11) | NO | PRI | NULL | auto_increment |

| c1 | int(11) | YES | UNI | NULL | |

| c2 | varchar(100) | YES | | NULL | |

| id_no | int(20) | NO | | NULL | |

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

4 rows in set (0.00 sec)

3、删除表上的约束

mysql>

mysql> alter table test_autoinc change id id int;

Query OK, 5 rows affected (0.07 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql>

mysql> desc test_autoinc;

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

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

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

| id | int(11) | NO | PRI | NULL | |

| c1 | int(11) | YES | UNI | NULL | |

| c2 | varchar(100) | YES | | NULL | |

| id_no | int(20) | NO | | NULL | |

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

4 rows in set (0.00 sec)

5、删除表上的主键

mysql> alter table test_autoinc drop primary key;

Query OK, 5 rows affected (0.07 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql>

mysql> desc test_autoinc;

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

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

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

| id | int(11) | NO | | NULL | |

| c1 | int(11) | YES | UNI | NULL | |

| c2 | varchar(100) | YES | | NULL | |

| id_no | int(20) | NO | | NULL | |

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

4 rows in set (0.00 sec)

mysql> show variables like 'sql_mode';

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

| Variable_name | Value |

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

| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |

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

1 row in set (0.00 sec)

mysql> select * from test_autoinc;

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

| id | c1 | c2 | id_no |

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

| 6 | 1 | abc | 0 |

| 8 | 3 | abcdd | 0 |

| 9 | 4 | abcdd | 0 |

| 10 | 5 | abcdd | 0 |

| 11 | 2 | eeee | 0 |

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

5 rows in set (0.00 sec)

mysql> update test_autoinc set id_no=1 ;

Query OK, 5 rows affected (0.14 sec)

Rows matched: 5 Changed: 5 Warnings: 0

mysql>

mysql> select * from test_autoinc;

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

| id | c1 | c2 | id_no |

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

| 6 | 1 | abc | 1 |

| 8 | 3 | abcdd | 1 |

| 9 | 4 | abcdd | 1 |

| 10 | 5 | abcdd | 1 |

| 11 | 2 | eeee | 1 |

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

5 rows in set (0.00 sec)

mysql> alter table test_autoinc add primary key(id_no) ,modify id_no int(11) auto_increment;

ERROR 1062 (23000): ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

mysql> alter table test_autoinc add primary key(id_no);

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

mysql> update test_autoinc set id_no=2 where id=7 ;

Query OK, 0 rows affected (0.00 sec)

Rows matched: 0 Changed: 0 Warnings: 0

mysql> update test_autoinc set id_no=3 where id=8 ;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update test_autoinc set id_no=4 where id=9 ;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update test_autoinc set id_no=5 where id=10 ;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> update test_autoinc set id_no=6 where id=11 ;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql>

mysql> select * from test_autoinc;

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

| id | c1 | c2 | id_no |

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

| 6 | 1 | abc | 1 |

| 8 | 3 | abcdd | 3 |

| 9 | 4 | abcdd | 4 |

| 10 | 5 | abcdd | 5 |

| 11 | 2 | eeee | 6 |

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

5 rows in set (0.00 sec)

mysql>

mysql> alter table test_autoinc add primary key(id_no) ,modify id_no int(11) auto_increment;

Query OK, 5 rows affected (0.27 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql>

mysql> show create table test_autoinc\G;

*************************** 1. row ***************************

Table: test_autoinc

Create Table: CREATE TABLE `test_autoinc` (

`id` int(11) NOT NULL,

`c1` int(11) DEFAULT NULL,

`c2` varchar(100) DEFAULT NULL,

`id_no` int(11) NOT NULL AUTO_INCREMENT,

PRIMARY KEY (`id_no`),

UNIQUE KEY `c1` (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

ERROR:

No query specified

mysql>