Shulou(Shulou.com)05/31 Report--

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!

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.