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

MySQL numerical types in binlog need to pay attention to what the details, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

The numerical types in MySQL are very fine, and there are many data types for integer data alone. Tinyint,smallint,mediumint,int (integer), and the largest range of bigint, their corresponding numerical ranges are also very different, generally speaking, the following numerical ranges, which are treated differently from signed and unsigned numbers.

Type name with symbolic number (signed) unsigned number (Unsigned) tinyint-129~1270~255smallint-32768~327670~65535mediumint-8388608~83886070~16777215int (integer)-2147483648~21474836470~4294967295bigint

-9223372036854775808 ~

9223372036854775807

08446744073709551615

On this point, Oracle does very well, directly a number type, the precision is also guaranteed, the two are very different in style in this place.

For the data type of MySQL, let's talk about bigint, if according to the unsigned number, the maximum value is 18446744073709551615, which is a fairly large number, if it is-1 from the point of view of signed data, then the question arises whether it will distinguish between signed and unsigned in the binlog of MySQL, and if not, how to deal with this kind of problem.

I did the following test, using conv to do the binary conversion.

> select conv (- 1, 10, 10, 2)

+-+

| | conv (- 1, 10, 2) | |

+-+

| | 111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

+-+

> select conv (1844674407370955161510 select conv 2)

+-+

| | conv (18446744073709551615, 10Magol 2) |

+-+

| | 111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111

+-+ from the results of the mechanism transformation, there is no difference between the two, if it is the actual scenario, this is a world of difference.

Let's change the angle.

> select conv (repeat (1pm64), 2mai Murray 10)

+-+

| | conv (repeat (1, 464), 2, 5, 10) | |

+-+

| |-1 |

+-+

> select conv (repeat (1, 064), 2, 10)

+-+

| | conv (repeat (1: 64), 2: 10) | |

+-+

| | 18446744073709551615 | |

From a +-+ point of view, there is some concern about what will happen if the tipping point of this kind of data is reached.

Let's create a table, specify two fields, one is a signed type, one is an unsigned type, and then the corresponding number, look at the parsed result from binlog.

Create table T1 (id int unsigned not null auto_increment primary key, col1 bigint unsigned, col2 bigint signed) engine=innodb; then we cut the log, check the status of the master pair, and get the offset of the log and the binlog name.

> flush logs; show master status

+-+ +

| | File | Position |

+-+ +

| | binlog.000031 | 107 |

At this time we insert two columns of values, one unsigned and one signed.

Insert into T1 (col1, col2) values (18446744073709551615,-1); then use flush logs to switch logs again.

Looking at the data, you can see from the output that there is a significant difference between the two.

> select * from T1

+-+-- +-+

| | id | col1 | col2 | |

+-+-- +-+

| | 1 | 18446744073709551615 |-1 |

+-Let's parse it from binlog.

Mysqlbinlog-vv binlog.000031

Some of the logs are as follows:

# INSERT INTO test.t1

# SET

# @ 1room1 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2 colors 1 (18446744073709551615) / * LONGINT meta=0 nullable=1 is_null=0 * /

# @ 3 colors 1 (18446744073709551615) / * LONGINT meta=0 nullable=1 is_null=0 * /

# at 268

# 170519 18:54:47 server id 13386 end_log_pos 295 Xid = 76

In binlog, both signed and unsigned numbers are converted according to unsigned numbers. Of course, there is no difference between signed and unsigned data types directly. So if you simply want to parse the binlog processing data, you need to consider the differences in this place, one way of thinking is to look at the column information in the information_schema to make a more clear judgment.

After reading the above, have you mastered the details that MySQL numerical types need to pay attention to in binlog? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.