Shulou(Shulou.com)06/01 Report--

1. Create test table and view table structure

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. Set other fields as self-increasing primary keys (there are already self-increasing primary keys in the current table, and setting other fields as self-increasing primary keys results in an error)

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. Delete constraints on the table

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. Delete the primary key on the table

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>

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.