How to create self-increasing Fields in MySQL Table
This article introduces the knowledge of "how to create self-increasing fields in MySQL table". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!
Note: if you use the new self-incrementing mutex, you should avoid using INSERT for replication. ON DUPLICATE KEY UPDATE statement.
Set the new self-increment mutual exclusion method: through the configuration option: innodb_autoinc_lock_mode: adjust the lock policy:
Innodb_autoinc_lock_mode = 0 ("traditional" lock mode: all use table locks)
Innodb_autoinc_lock_mode = 1 (default) ("consecutive" lock mode: use a new method when the number of rows can be predicted, and table locks when not)
Innodb_autoinc_lock_mode = 2 ("interleaved" lock mode: all use the new method, unsafe, not suitable for replication)
# # creating self-increasing Fields
Method 1. Create:
Mysql > create table c (id int auto_increment,name varchar (20), primary key (id))
Query OK, 0 rows affected (0.52sec)
Mysql > desc c
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (11) | NO | PRI | NULL | auto_increment |
| | name | varchar (20) | YES | | NULL |
+-+ +
2 rows in set (0.03 sec)
Method 2. Modify:
Mysql > create table cc (id int,name varchar (20))
Query OK, 0 rows affected (0.42 sec)
Mysql > alter table cc change id id int primary key auto_increment
Query OK, 0 rows affected (1.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
Mysql > desc cc
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (11) | NO | PRI | NULL | auto_increment |
| | name | varchar (20) | YES | | NULL |
+-+ +
2 rows in set (0.11 sec)
Mysql > insert into cc (id,name) values (1 NULL,'b'), (NULL,'b'), (NULL,'c'), (5)
Mysql > select * from cc
+-+ +
| | id | name |
+-+ +
| | 1 | a |
| | 2 | b | |
| | 3 | c |
| | 5 | d | |
+-+ +
4 rows in set (0.00 sec)
Note: only int type and primary key can be used with auto_increment.
# # modify the columns of tables with records to self-incrementing columns
Mysql > create table ccc (id int,name varchar (20))
Query OK, 0 rows affected (0.27 sec)
Mysql > insert into ccc (id,name) values (1 NULL,'b'), (NULL,'b'), (NULL,'c'), (5)
Query OK, 4 rows affected (.53 sec)
Records: 4 Duplicates: 0 Warnings: 0
Mysql > select * from ccc
+-+ +
| | id | name |
+-+ +
| | 1 | a |
| | NULL | b | |
| | NULL | c | |
| | 5 | d | |
+-+ +
4 rows in set (0.00 sec)
Mysql > alter table ccc change id id int primary key auto_increment
Query OK, 4 rows affected (1.04 sec)
Records: 4 Duplicates: 0 Warnings: 0
Mysql > desc ccc
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (11) | NO | PRI | NULL | auto_increment |
| | name | varchar (20) | YES | | NULL |
+-+ +
2 rows in set (0.01sec)
Mysql > insert into ccc (id,name) values (1 NULL,'b'), (NULL,'b'), (NULL,'c'), (5)
ERROR 1062 (23000): Duplicate entry'1' for key 'PRIMARY'
Mysql > insert into ccc (id,name) values (6 Magna'), (NULL,'ab'), (NULL,'ac'), (10)
Query OK, 4 rows affected (0.07 sec)
Records: 4 Duplicates: 0 Warnings: 0
Mysql > select * from ccc
+-+ +
| | id | name |
+-+ +
| | 1 | a |
| | 2 | b | |
| | 3 | c |
| | 5 | d | |
| | 6 | aa |
| | 7 | ab |
| | 8 | ac |
| | 10 | ad |
+-+ +
8 rows in set (0.00 sec)
Mysql >
This is the end of the content of "how to create self-increasing fields in MySQL". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!
