1.前言

mysql的表如果没有手动指定主键，后台会自动创建一个隐藏的字段row_id,这个row_id是存在数据库中是6字节。
在一个运行很久的数据库环境，并且使用频繁的话，是很可能超过数值281474976710656（6字节）的。代码片段如下：
static inline row_id_t dict_sys_get_new_row_id(void);
/** Reads a row id from a record or other 6-byte stored form.

dict_sys_get_new_row_id(void)
/*=========================*/
{
row_id_t id;

mutex_enter(&dict_sys->mutex);

id = dict_sys->row_id;

if (0 == (id % DICT_HDR_ROW_ID_WRITE_MARGIN)) {

	dict_hdr_flush_row_id();
}

dict_sys->row_id++;

mutex_exit(&dict_sys->mutex);

return(id);
复制

}

2.测试ID到最大值

mysql [localhost:5727] {root} (sbtest) > CREATE TABLE sbtest2 (
-> id bigint NOT NULL ,
-> k int NOT NULL DEFAULT ‘0’,
-> c char(120) NOT NULL DEFAULT ‘’
-> ) ENGINE=InnoDB ;
Query OK, 0 rows affected (0.00 sec)
首先改变量值
[root@testdb msb_5_7_26]# gdb -p pidof mysqld -ex ‘set dict_sys.row_id=1’ --batch
[New LWP 113679]
[New LWP 89696]
[New LWP 89695]
[New LWP 89694]
[New LWP 89693]
[New LWP 89692]
[New LWP 89691]
[New LWP 89690]
[New LWP 89689]
[New LWP 89688]
[New LWP 89687]
[New LWP 89686]
[New LWP 89685]
[New LWP 89684]
[New LWP 89683]
[New LWP 89678]
[New LWP 89677]
[New LWP 89676]
[New LWP 89675]
[New LWP 89674]
[New LWP 89673]
[New LWP 89672]
[New LWP 89671]
[New LWP 89670]
[New LWP 89669]
[New LWP 89668]
[New LWP 89667]
[Thread debugging using libthread_db enabled]
Using host libthread_db library “/lib64/libthread_db.so.1”.
0x00007ff1b288eccd in poll () from /lib64/libc.so.6
[Inferior 1 (process 89658) detached]

插入几笔数据
mysql [localhost:5727] {root} (sbtest) > insert into sbtest2(id,k,c) values(1,11,‘1’);
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5727] {root} (sbtest) > insert into sbtest2(id,k,c) values(2,22,‘12’);
Query OK, 1 row affected (0.01 sec)
mysql [localhost:5727] {root} (sbtest) > insert into sbtest2(id,k,c) values(3,33,‘123’);
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5727] {root} (sbtest) > select * from sbtest2;
±—±—±----+
| id | k | c |
±—±—±----+
| 1 | 11 | 1 |
| 2 | 22 | 12 |
| 3 | 33 | 123 |
±—±—±----+
3 rows in set (0.00 sec)

设置ID最大值
[root@testdb msb_5_7_26]# gdb -p pidof mysqld -ex ‘set dict_sys.row_id=281474976710656’ --batch
mysql [localhost:5727] {root} (sbtest) > insert into sbtest2(id,k,c) values(4,44,‘123’);
Query OK, 1 row affected (0.01 sec)

mysql [localhost:5727] {root} (sbtest) > select * from sbtest2;
±—±—±----+
| id | k | c |
±—±—±----+
| 4 | 44 | 123 |
| 1 | 11 | 1 |
| 2 | 22 | 12 |
| 3 | 33 | 123 |
±—±—±----+
4 rows in set (0.01 sec)

mysql [localhost:5727] {root} (sbtest) > insert into sbtest2(id,k,c) values(5,44,‘123’);
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5727] {root} (sbtest) > select * from sbtest2;
±—±—±----+
| id | k | c |
±—±—±----+
| 4 | 44 | 123 |
| 5 | 44 | 123 |
| 2 | 22 | 12 |
| 3 | 33 | 123 |
±—±—±----+
4 rows in set (0.00 sec)

从这里可以看到,ID=1的值被覆盖了。当ID达到最大值后，隐藏字段row_id会继续从0开始写数据。原存有的数据有覆盖风险。

表显示指定主键时，ID到最大值时。
mysql [localhost:5727] {root} (sbtest) > CREATE TABLE sbtest3 (
-> id int NOT NULL AUTO_INCREMENT,
-> k int NOT NULL DEFAULT ‘0’,
-> c char(120) NOT NULL DEFAULT ‘’,
-> PRIMARY KEY (id)
-> ) ENGINE=InnoDB AUTO_INCREMENT=1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:5727] {root} (sbtest) > insert into sbtest3(id,k,c) values(1,1,‘11’);
Query OK, 1 row affected (0.00 sec)

mysql [localhost:5727] {root} (sbtest) > insert into sbtest3(id,k,c) values(2,2,‘21’);
Query OK, 1 row affected (0.00 sec)

有符号整型最大值是2147483647，当把ID设为最大值时，
mysql [localhost:5727] {root} (sbtest) > insert into sbtest3(id,k,c) values(2147483647,2,‘21’);
Query OK, 1 row affected (0.00 sec)
mysql [localhost:5727] {root} (sbtest) > insert into sbtest3(id,k,c) values(2147483648,2,‘21’);
ERROR 1264 (22003): Out of range value for column ‘id’ at row 1
mysql [localhost:5727] {root} (sbtest) > insert into sbtest3(id,k,c) values(2147483647,2,‘21’);
ERROR 1062 (23000): Duplicate entry ‘2147483647’ for key ‘PRIMARY’

可以看到ID达到最大，写入数据会报超出范围或主键冲突。

3.小结

3.1建表没显示指定主键时，当ID达到最大值时，有数据丢失风险。
建表还是需要显示指定主键，毕竟数据库报错比丢失数据要好。