Self increase of primary key （auto_increment）

When the primary key is defined as self growing , The primary key will be defined by the database system according to the data type , Automatic assignment .

• By default auto_increment The initial value of 1
• Only one field can be used in a table auto_increment constraint , And the field must have a unique index , In order to avoid duplication of serial numbers （ It is the primary key or part of the primary key ）.
• auto_increment The constrained field must have not null attribute
• auto_increment The field of constraint can only be integer type (tinyint、smallint、int、bigint etc. )
• auto_increment The maximum value of the constraint is constrained by the data type of the field , If the upper limit is reached, it will fail .
  Cancel the self growth of the field
  alter table < The name of the data table > modify column < Field name > < New type ( New length )>

1. Use the default value

alter table personal modify < Field name > < Field data type > primary key auto_increment;

mysql> alter table personal modify _id int(10) primary key auto_increment;
Query OK, 0 rows affected (0.38 sec)
Records: 0  Duplicates: 0  Warnings: 0

2. Specify the initial value of the auto increment field
   * When creating tables , Specify primary key from 10 Start growing from

mysql> create table peronsal(
    -> _id int not null auto_increment,
    -> name varchar(10),
    -> primary key(_id))
    -> auto_increment=10;
Query OK, 0 rows affected (0.04 sec)

3. Modify the initial value of the auto increment field （ Only valid for data added later ）

alter table < Data table name > auto_increment= value 

mysql> alter table teacher auto_increment=10;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into teacher (name,phone) values(' Zhang San ','12345678911');
Query OK, 1 row affected (0.00 sec)

mysql> insert into teacher (name,phone) values(' Zhang San ','12345678911');
Query OK, 1 row affected (0.00 sec)

mysql> select * from teacher;
+-----+------+-------------+
| _id | name | phone       |
+-----+------+-------------+
|   1 |  Zhang San  | 12345678911 |
|   2 |  Zhang San  | 12345678911 |
|  10 |  Zhang San  | 12345678911 |
|  11 |  Zhang San  | 12345678911 |
+-----+------+-------------+
4 rows in set (0.00 sec)

5. Self increasing field values are discontinuous

This is not very good for the time being , It's a direct copy of
Create table tb_student3, among id Is a self incrementing primary key field ,name It's the only index

mysql> CREATE TABLE tb_student3(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20) UNIQUE KEY,
    -> age INT DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.04 sec)

6. Delete the primary key constraint with self growth

1、alter table < The name of the data table > modify column < Field name > < New type ( New length )>
2、alter table < The name of the data table > drop primary key;

1. Manually insert a piece of data into the field set as self growth value

1、 Initial table 
mysql> select * from student;
+-----+------+-----------+
| _id | name | phone     |
+-----+------+-----------+
|   1 |  Zhang San  | 111111111 |
|   2 |  Zhang San  | 111111111 |
|   3 |  Zhang San  | 111111111 |
|   4 |  Zhang San  | 111111111 |
|   5 |  Li Si  | 22222222  |
+-----+------+-----------+
5 rows in set (0.00 sec)

2、 Add data 
mysql> insert into student values(10,"tom","22222222");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student(name,phone) values("jerry","22222222");
Query OK, 1 row affected (0.00 sec)

3、 At this time, the self increasing value will self increase according to the last data 
mysql> select * from student;
+-----+-------+-----------+
| _id | name  | phone     |
+-----+-------+-----------+
|   1 |  Zhang San   | 111111111 |
|   2 |  Zhang San   | 111111111 |
|   3 |  Zhang San   | 111111111 |
|   4 |  Zhang San   | 111111111 |
|   5 |  Li Si   | 22222222  |
|  10 | tom   | 22222222  |
|  11 | jerry | 22222222  |
+-----+-------+-----------+
7 rows in set (0.00 sec)