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

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

成都创新互联公司专注为客户提供全方位的互联网综合服务，包含不限于网站制作、网站建设、达州网络推广、微信小程序开发、达州网络营销、达州企业策划、达州品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等，从售前售中售后，我们都将竭诚为您服务，您的肯定，是我们最大的嘉奖；成都创新互联公司为所有大学生创业者提供达州建站搭建服务，24小时服务热线：028-86922220，官方网址：www.cdcxhl.com

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>