MySQL primary key use trap summary

1. The trap of auto-incrementing the primary key of the table

We know that MySQL tables can define a self-growth ID. But if the self-increasing ID of the table is used up, there is any problem in inserting the data.

In MySQL, the logic after the self-increment of the table definition reaches the upper limit is: when applying for the next ID, the value obtained remains unchanged, so after reaching the upper limit, inserting data will report a primary key conflict

The test is as follows:

Created a user table with id as the primary key for self-increment. Because it is of unsigned type, the maximum value is 2^32 -1 = 4294967295. When the insert statement is executed for the first time, the id is set to 4294967295, and for the second insert. , The id is still 4294967295, so the primary key conflicts.

CREATE TABLE user (  id int(20) unsigned NOT NULL AUTO_INCREMENT,  name varchar(10) DEFAULT NULL,  PRIMARY KEY (id)) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8; insert into user(name) values('张三'); insert into user(name) values('张三');

2. Table does not set the trap of primary key

If the created InnoDB table does not specify a primary key, then InnoDB will create an invisible row_id with a length of 6 Bytes

InnoDB maintains a global dict_sys.row_id value. For all InnoDB tables without a primary key, every time a row of data is inserted, the current dict_sys.row_id value is used as the row_id of the data to be inserted, and then the value of dict_sys.row_id is +1

In terms of code implementation, row_id is a 8 Bytes BIGINT UNSIGNED, but when InnoDB is designed, only 6 Bytes is reserved for row_id. When writing to the data table, only the last 6 Bytes will be stored. The value range of row_id: 0 ~ 2 ^48-1, after reaching the upper limit, the next value is 0

In InnoDB, after applying for row_id=N, write this row of data into the table. If there is already a row with row_id=N in the table, the newly written row will overwrite the original row [ Trap: row_id value Will be recycled ]

Therefore, it is recommended to create an auto-increment primary key or specify a primary key for all tables. After the auto-increment ID of the table reaches the upper limit, the primary key conflict error will be reported when the data is inserted again, which affects the availability.

If the primary key is not set, the data may be overwritten, which means that the data is lost, and the reliability is affected

2.1. Row_id boundary overflow verification

1. Create a table

CREATE TABLE person (  id int not null,  name varchar(10) not null) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. Get the corresponding process number through ps -ef|grep mysql

3. Use gdb to start debugging configuration, set row_id to 0, remember! This should be your own test environment. ( Gdb is a powerful program debugging tool under Linux released by the GNU open source organization. If it is not installed, please use yum install gdb to install first)

gdb -p 333855 -ex'p dict_sys.row_id=0' --batch

4. Insert data:   insert into person(id,name) values(1,'张三'),(2,'李四');

5. Set row_id to the maximum value via gdb, which is 2^48 -1.  gdb -p 333855 -ex'p dict_sys.row_id=281474976710656' --batch

6. Insert data   insert into person(id,name) values(3,'王五'),(4,'赵六');

7. Check the data and find that there are only 2 data in the table, and the records with row_id 0 and 1 are overwritten.

The operation screenshot of MySQL is as follows:

3. When is the hidden field (_rowid) visible

As long as the table is created, this _rowid must exist, the only difference is the difference between display and hiding, that is, whether it can be queried through select _rowid from table

The conditions displayed by _rowid are as follows:

If the primary key consists of a single integer column , _rowid represents the primary key column. If there is a primary key, but it does not contain a single integer column, _rowid cannot be used.

When there is no primary key in the table, _rowid points to the column in the first UNIQUE NOT NULL index, and the index consists of a single integer column . If the first UNIQUE NOT NULL index does not contain a single integer column, you cannot use _rowid.

The official MySQL description for _rowid is as follows:

The verification example is as follows:

reference

https://developpaper.com/what-should-i-do-when-the-self-incrementing-id-of-mysql-table-is-used-up/

https://dev.mysql.com/doc/refman/5.7/en/create-index.html