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

This article focuses on "how to understand the auto_increment_offset and auto_increment_ increment values of MYSQL". Interested friends might as well take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to understand the auto_increment_offset and auto_increment_ increment values of MYSQL.

Actually, the two values are as follows:

We understand that auto_increment_offset starts with an offset of 0.

Auto_increment_increment is a step size

Auto_increment_offset+ (NMur1) * auto_increment_increment

N represents the number of inserts. This is actually a value that can be set between 0mi + ∽.

Take an analogy

Mysql > set auto_increment_offset=2

Query OK, 0 rows affected (0.00 sec)

Mysql > set auto_increment_increment=5

Query OK, 0 rows affected (0.00 sec)

So the value we allow is 2 7 12 17.

We set up a table.

Mysql > create table testcr11 (id int primary key auto_increment) AUTO_INCREMENT=1

Query OK, 0 rows affected (0.22 sec)

Mysql > insert into testcr11 values (NULL)

Query OK, 1 row affected (0.01sec)

Mysql > select * from testcr11

+-- +

| | id |

+-- +

| | 2 |

+-- +

1 row in set (0.00 sec)

You can see that the value does not start with 1 but 2, inserting a row

Mysql > insert into testcr11 values (NULL)

Query OK, 1 row affected (0.20 sec)

Mysql > select * from testcr11

+-- +

| | id |

+-- +

| | 2 |

| | 7 |

+-- +

2 rows in set (0.00 sec)

As you can see, there is no problem.

But the problem is that we encounter the following hint:

When the value of auto_increment_offset is greater than that of

Auto_increment_increment, the value of auto_increment_offset is ignored

That is, if auto_increment_offset > auto_increment_increment, auto_increment_offset will be ignored.

This is also understandable, such as

Auto_increment_offset = 10

Auto_increment_increment = 5

According to the formula, the value we inserted for the first time was 10 15 20, but we found that in the linear range of 0muri + ∽, we lost a

This value is 10-5 = 5, which makes sense if we understand it this way, but is this really the case?

I open the source code:

You can see the following calculation

Inline ulonglong

Compute_next_insert_id (ulonglong nr,struct system_variables * variables)

{

Const ulonglong save_nr= nr

If (variables- > auto_increment_increment = = 1)

Nr= nr + 1; / / optimization of the formula below

Else

{

Nr= ((nr+ variables- > auto_increment_increment-

Variables- > auto_increment_offset)) /

(ulonglong) variables- > auto_increment_increment)

Nr= (nr* (ulonglong) variables- > auto_increment_increment +

Variables- > auto_increment_offset)

}

If (unlikely (nr auto_increment_increment = = 1)

(gdb) p save_nr

$2 = 0

(gdb) p variables- > auto_increment_increment

$3 = 5

(gdb) p variables- > auto_increment_offset

$4 = 10

(gdb) n

3485 (ulonglong) variables- > auto_increment_increment)

(gdb) p nr

$5 = 0

(gdb) n

3487 variables- > auto_increment_offset)

(gdb) p nr

$6 = 3689348814741910322

(gdb) n

3490 if (unlikely (nr auto_increment_increment-

Variables- > auto_increment_offset)) /

(ulonglong) variables- > auto_increment_increment)

Variables- > auto_increment_increment-

Variables- > auto_increment_offset

Negative numbers appear here, but the operation is of unsigned longlong type, which is obtained after automatic type conversion.

A very big one.

$6 = 3689348814741910322

There's an exception here, and I end up with the number 4.

And then what we insert is 4.

Mysql > select * from testcr5

+-- +

| | id |

+-- +

| | 4 |

+-- +

1 row in set (0.00 sec)

Maybe if auto_increment_offset > auto_increment_increment will get one because of the conversion problem

The uncertain result is simply called

When the value of auto_increment_offset is greater than that of

Auto_increment_increment, the value of auto_increment_offset is ignored

-

The following is the specific calculation process:

If we're going to get to the root of the question, why is it 4? this question involves a lot of things, let's first look at the types of variables.

First give the calculation source code

Typedef unsigned longlong ulonglong

Typedef unsigned long ulong

Nr= ((nr+ variables- > auto_increment_increment-

Variables- > auto_increment_offset)) /

(ulonglong) variables- > auto_increment_increment)

Nr= (nr* (ulonglong) variables- > auto_increment_increment +

Variables- > auto_increment_offset)

Give the type

Nr (ulonglong *) = 0 (initial)

Variables- > auto_increment_increment (ulong *) = 5

Variables- > auto_increment_offset (ulong *) = 10

On 64-bit LINUX, ULONG and ULONGLONG are both 8 bytes, so we think they represent the same range, but they are the same

At the same time, we need to know that ulonglong cannot store negative numbers.

While variables- > auto_increment_increment-variables- > auto_increment_offset =-5 he converted to

The positive number of ulong is 18446744073709551611. Why are there so many?

First of all, we need to look at the representation of the ulong of 5 as follows:

00000000 00000000 00000000 00000101 starts with symbolic bits

Inverse code

0 1111111 11111111 11111111 11111111 1111111111111010

Complement code

0 1111111 11111111 11111111 11111111 11111011

None of us have a moving symbol bit, in fact, the symbol bit of a negative number is 1, so it is

11111111 11111111 11111111 11111011

Okay, let's take a look at his hexadecimal representation.

FF FF FF FF FF FF FF FB, this is the representation of-5long, because ULONG does not have negative numbers, so it uses symbolic bits as numeric bits.

So converting to base 10 is actually

18446744073709551611

The following is my GDB, because the small end Little_endian is the low number of low-address stored values that are stored in memory, both in memory and on disk.

Actually, 0xfb 0xff 0xff 0xff 0xff 0xff 0xff 0xff

Fb is low.

(http://blog.itpub.net/7728585/viewspace-2124159/ about big end and small end)

(gdb) p test

$1 = 18446744073709551611

(gdb) p & test

$2 = (ulonglong *) 0x7fffffffea28

(gdb) x/8bx 0x7fffffffea28

0x7fffffffea28: 0xfb 0xff 0xff 0xff 0xff 0xff 0xff 0xff

Since

Nr+ variables- > auto_increment_increment = 18446744073709551611

Let's look at the next step.

/ (ulonglong) variables- > auto_increment_increment

Actually it is.

18446744073709551611 / 5 = 3689348814741910322

Why did 3689348814741910322 obviously lose a 1?

Actually

36893488147419103225th = 18446744073709551610

Because integers cannot represent floating-point numbers, the value after the discarded decimal point is used in C language. 1 is lost here, which is why it is 4 instead of 5.

So (initial nr=0)

Nr= ((nr+ variables- > auto_increment_increment-

Variables- > auto_increment_offset)) /

(ulonglong) variables- > auto_increment_increment)

Nr = 3689348814741910322

The next thing to do is

Nr= (nr* (ulonglong) variables- > auto_increment_increment + variables- > auto_increment_offset)

Nr* (ulonglong) variables- > auto_increment_increment We have said that his value is

36893488147419103225th = 18446744073709551610

And then

18446744073709551610 variables-> auto_increment_offset

It sure is

1844674407370955161010

Let me take a look at the 18446744073709551610 binary.

11111111 11111111 11111111 11111010

The binary of 10

1010 low bit addition

11111111 11111111 11111111 11111010

+ 1010

1 00000000 00000000 00000000 00000100

We obviously saw the overflow. The overflow is discarded and the rest is

00000000 00000000 00000000 00000100

It's decimal 4.

This is the reason calculated by 4.

So the MYSQL official document is represented by an omission, which is actually an uncertain value if

If

Auto_increment_offset is much larger than variables- > auto_increment_increment.

Like auto_increment_offset=1000.

Auto_increment_increment = 2

So as long as

Nr+ variables- > auto_increment_increment

< variables->

Auto_increment_offset

Then the values are all uncertain. The nr here is the self-increment of the last storage, with an initial value of 0.

Nr+ variables- > auto_increment_increment-variables- > auto_increment_offset

Therefore, for this reason, it is suggested that you should note that auto_increment_increment is greater than auto_increment_offset.

It's necessary.

Here is a simple program that demonstrates this process:

Click (here) to collapse or open

# include

Typedef unsigned longlong ulonglong

Typedef unsigned long ulong

Int main (void)

{

Ulonglong nr = 0

Ulonglong nr1

Ulong auto_increment_increment = 5

Ulong auto_increment_offset = 10

Ulonglong T1 music 5

Ulonglong test1

Printf ("ulonglong size is:%lu ulong size is:%lu\ n", sizeof (unsigned longlong), sizeof (unsigned long))

Printf ("nr init values is:%llu\ n", nr)

Printf ("auto_increment_increment is:%lu\ n", auto_increment_increment)

Printf ("auto_increment_offset is:% lu\ n", auto_increment_offset)

Nr= ((nr+ auto_increment_increment-auto_increment_offset)) / (ulonglong) auto_increment_increment)

Printf ("- 5 ulonglong is:% llu\ n", T1)

Printf ("nr+ auto_increment_increment-auto_increment_offset)) / (ulonglong) auto_increment_increment is:%llu\ n", nr)

Test1 = nr* (ulonglong) auto_increment_increment

Nr= (nr* (ulonglong) auto_increment_increment + auto_increment_offset)

Printf ("nr* (ulonglong) auto_increment_increment is:% llu\ n", test1)

Printf ("last nr is:% llu\ n", nr)

}

Run as follows:

Ulonglong size is:8 ulong size is:8

Nr init values is:0

Auto_increment_increment is:5

Auto_increment_offset is: 10

-5 ulonglong is: 18446744073709551611

Nr+ auto_increment_increment-auto_increment_offset)) / (ulonglong) auto_increment_increment is:3689348814741910322

Nr* (ulonglong) auto_increment_increment is: 18446744073709551610

Last nr is: 4

At this point, I believe you have a deeper understanding of "how to understand the auto_increment_offset and auto_increment_ increment values of MYSQL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.