很无聊的问题,但我找不到规律... 请帮忙看看,谢谢:) set auto_increment_offset=7;
set auto_increment_increment=3;
drop table if exists xxx;
create table xxx (i int auto_increment primary key);
insert into xxx values (null), (null);
select last_insert_id(), i from xxx;
show variables like 'auto_increment%';
+------------------+---+
| last_insert_id() | i |
+------------------+---+
| 3 | 3 |
| 3 | 6 |
+------------------+---+
set auto_increment_offset=11;
set auto_increment_increment=7;
drop table if exists xxx;
create table xxx (i int auto_increment primary key);
insert into xxx values (null), (null);
select last_insert_id(), i from xxx;
show variables like 'auto_increment%';
+------------------+---+
| last_insert_id() | i |
+------------------+---+
| 2 | 2 |
| 2 | 9 |
+------------------+---+ set auto_increment_offset=17;
set auto_increment_increment=11;
drop table if exists xxx;
create table xxx (i int auto_increment primary key);
insert into xxx values (null), (null);
select last_insert_id(), i from xxx;
show variables like 'auto_increment%';
+------------------+----+
| last_insert_id() | i |
+------------------+----+
| 1 | 1 |
| 1 | 12 |
+------------------+----+ 到底第一个id 的计算规律是什么呢? 文档上说offset 被忽略了,怎么忽略的呢? 谢谢&
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| auto_increment_increment | 3 |
| auto_increment_offset | 7 |
+--------------------------+-------+
2 rows in set (0.00 sec)mysql> drop table if exists xxx;
Query OK, 0 rows affected (0.06 sec)mysql> create table xxx (i int auto_increment primary ke
Query OK, 0 rows affected (0.09 sec)mysql> insert into xxx values (null);
Query OK, 1 row affected (0.06 sec)mysql> insert into xxx values (null);
Query OK, 1 row affected (0.06 sec)mysql> insert into xxx values (null);
Query OK, 1 row affected (0.06 sec)mysql> insert into xxx values (null);
Query OK, 1 row affected (0.06 sec)mysql> select * from xxx;
+----+
| i |
+----+
| 3 |
| 6 |
| 7 |
| 10 |
+----+
4 rows in set (0.00 sec)mysql> insert into xxx values (null);
Query OK, 1 row affected (0.08 sec)mysql> insert into xxx values (null);
Query OK, 1 row affected (0.06 sec)mysql> select * from xxx;
+----+
| i |
+----+
| 3 |
| 6 |
| 7 |
| 10 |
| 13 |
| 16 |
+----+
6 rows in set (0.00 sec)
maxid = 现有最大值,没数据的时候当然是0
insertid_ = maxid + auto_increment_increment
但如果 maxid < auto_increment_offset < insertid_ 那么 insertid_ = auto_increment_offset
$auto_increment_offset = 19;
$auto_increment_increment = 12;
$arr = array(
2 => 0,
3 => 2,
4 => 0,
5 => 4,
6 => 2,
7 => 5,
8 => 0,
9 => 2,
10 => 4,
11 => 6,
12 => 8,
13 => 10,
14 => 12,
15 => 14,
16 => 0,
17 => 16,
18 => 2,
19 => 2,
20 => 4,
21 => 5,
22 => 6,
23 => 17,
24 => 8,
25 => 9,
26 => 10,
27 => 2,
28 => 12,
29 => 5,
30 => 14,
31 => 15,
32 => 0,
);
$insertid = $auto_increment_offset;
if ($auto_increment_offset > $auto_increment_increment) {
$insertid = ($arr[$auto_increment_increment]+$auto_increment_offset)%$auto_increment_increment;
}
echo "第一次插入编号为:".$insertid;
?>
$arr[$auto_increment_increment]的值测出来了,但是还没找出根据$auto_increment_increment算出其对应值的算法。
/**
Generate the next auto-increment number based on increment and offset.
computes the lowest number
- strictly greater than "nr"
- of the form: auto_increment_offset + N * auto_increment_increment In most cases increment= offset= 1, in which case we get:
@verbatim 1,2,3,4,5,... @endverbatim
If increment=10 and offset=5 and previous number is 1, we get:
@verbatim 1,5,15,25,35,... @endverbatim
*/
inline ulonglong
compute_next_insert_id(ulonglong nr,struct system_variables *variables)
{
if (variables->auto_increment_increment == 1)
return (nr+1); // optimization of the formula below
nr= (((nr+ variables->auto_increment_increment -
variables->auto_increment_offset)) /
(ulonglong) variables->auto_increment_increment);
return (nr* (ulonglong) variables->auto_increment_increment +
variables->auto_increment_offset);
}
第一次被调用的时候,入口参数 nr 的值为 0。所以,offset 不是被忽略了,而只是计算过程中溢出了(出现了负值)。
Generate the next auto-increment number based on increment and offset.
computes the lowest number
- strictly greater than "nr"
- of the form: auto_increment_offset + N * auto_increment_increment In most cases increment= offset= 1, in which case we get:
@verbatim 1,2,3,4,5,... @endverbatim
If increment=10 and offset=5 and previous number is 1, we get:
@verbatim 1,5,15,25,35,... @endverbatim
*/
If increment=10 and offset=5 and previous number is 1, we get:
@verbatim 1,5,15,25,35,... @endverbatim <--- 这里明显就是一个错误应该是 5, 15, 25 ....
看来解决这个问题的关键就是给 ulonglong 这个 64位无符号整数设置一个负数会怎么样,
一会去试试看
我那段php是用来模拟算出第一次插入编号的。
在php和mysql中设定相同的两个参数得到的第一次插入ID是一样的。
$auto_increment_offset
$auto_increment_increment