Shulou(Shulou.com)06/01 Report--

I. conversion cases

Recently, a friend discussed the problem of losing precision in string conversion double as follows:

This case comes from my friend Xu Chenliang.

Root@mysqldb 22:12: [xucl] > show create table T1\ gateway * 1. Row * * Table: t1Create Table: CREATE TABLE `t1` (`id` varchar (255) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec) root@mysqldb 22:19: [xucl] > select * from T1 +-+ | id | +-+ | 204027026112927605 | | 2040270261129273 | | 2040270261129276 | | 2040270261129275 | | 100 | 101 | +-+ 6 rows in set (0.00 sec) Strange phenomenon: root@mysqldb 22:19: [xucl] > select * from T1 where id=204027026112927603 +-+ | id | +-+ | 204027026112927605 | | 204027026112927603 | +-+ 2 rows in set (0.00 sec). It is clear that 204027026112927603 is checked, why 204027026112927605 also came out.

The conversion is clearly written in the official documentation, where both sides are converted to the double type for comparison.

Second, simple testing

We can actually test this by inserting a string with a field of type double:

Mysql > use testmtsReading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with-ADatabase changedmysql > insert into test11 values ('1111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111111 +-+-+ | Table | Create Table | | +-+ | test11 | | | CREATE TABLE `test11` (`n` double DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 | +-+-+ 1 row in set (0.00 sec) mysql > select * from test11 | +-- + | n | +-- + | 9.007199254740991e15 | | 9.007199254740992e15 | | 9.007199254740991e17 | | 1.1111111111111112e31 | | 1.1111111111111112e31 | | 1.1111111111111111e33 | | 1.1111111111111111e33 | | 1.1111111111111111e33 | | 1.1111111111111111e33 | | 1.1111111111111111e33 | | 1.1111111111111111e33 | +- -+ 11 rows in set (0.00 sec)

You can see that the accuracy has actually been lost. Between 16 and 17 characters, much of it may be ignored and the string loses precision. Zhengzhou Infertility Hospital: http://jbk.39.net/yiyuanzaixian/zztjyy/

III. Conversion function

This conversion function is hundreds of thousands of lines, without looking at it carefully, it is too hard. Record the stack frame: https://www.jianshu.com/p/fc56f6221728

# 0 my_strtod_int (s00=0x7ffedc0065f0'1', se=0x7fffec5eb678, error=0x7fffec5eb680, buf=0x7fffec5ea770 "\ 320\ 001", buf_size=3680) at / mysqldata/percona-server-locks-detail-5.7.22/strings/dtoa.c:1356#1 0x0000000001ee07c1 in my_strtod (str=0x7ffedc0065f0'1', end=0x7fffec5eb678, error=0x7fffec5eb680) at / mysqldata/percona-server-locks-detail-5.7.22/strings/dtoa.c:472#2 0x0000000001ec4eda in my_strntod_8bit (cs=0x2e379e0, str=0x7ffedc0065f0'1', length=34, end=0x7fffec5eb678 Err=0x7fffec5eb680) at / mysqldata/percona-server-locks-detail-5.7.22/strings/ctype-simple.c:741#3 0x0000000000f4dbae in Field_double::store (this=0x7ffedc9a1390, from=0x7ffedc0065f0'1', len=34, cs=0x2e379e0) at / mysqldata/percona-server-locks-detail-5.7.22/sql/field.cc:4825#4 0x0000000000f9bbc7 in Item::save_str_value_in_field (this=0x7ffedc006618, field=0x7ffedc9a1390) Result=0x7ffedc006628) at / mysqldata/percona-server-locks-detail-5.7.22/sql/item.cc:561#5 0x0000000000fae204 in Item_string::save_in_field_inner (this=0x7ffedc006618, field=0x7ffedc9a1390, no_conversions=false) at / mysqldata/percona-server-locks-detail-5.7.22/sql/item.cc:6895#6 0x0000000000fad7de in Item::save_in_field (this=0x7ffedc006618, field=0x7ffedc9a1390) No_conversions=false) at / mysqldata/percona-server-locks-detail-5.7.22/sql/item.cc:6752#7 0x0000000001549101 in fill_record (thd=0x7ffedc000b90, table=0x7ffedc9a8650, ptr=0x7ffedc9a1388, values=..., bitmap=0x0, insert_into_fields_bitmap=0x0) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_base.cc:9702#8 0x00000000015493e9 in fill_record_n_invoke_before_triggers (thd=0x7ffedc000b90, ptr=0x7ffedc9a1380, values=..., table=0x7ffedc9a8650, event=TRG_EVENT_INSERT) Num_fields=1) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_base.cc:9784#9 0x00000000017fd477 in Sql_cmd_insert::mysql_insert (this=0x7ffedc006d80, thd=0x7ffedc000b90, table_list=0x7ffedc0067c8) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_insert.cc:751#10 0x00000000018040ed in Sql_cmd_insert::execute (this=0x7ffedc006d80 Thd=0x7ffedc000b90) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_insert.cc:3121#11 0x00000000015cc753 in mysql_execute_command (thd=0x7ffedc000b90, first_level=true) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:3746#12 0x00000000015d2fde in mysql_parse (thd=0x7ffedc000b90, parser_state=0x7fffec5ed600) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#13 0x00000000015c6b72 in dispatch_command (thd=0x7ffedc000b90, com_data=0x7fffec5edd70) Command=COM_QUERY) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#14 0x00000000015c58ff in do_command (thd=0x7ffedc000b90) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#15 0x000000000170e578 in handle_connection (arg=0x6ab5a00) at / mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#16 0x0000000001945538 in pfs _ spawn_thread (arg=0x697a460) at / mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#17 0x00007ffff7bcfaa1 in start_thread () from / lib64/libpthread.so.0#18 0x00007ffff6b37c4d in clone () from / lib64/libc.so.6

Start case conversion stack frame

# 0 my_strtod (str=0x7ffee097a803 "204027026112927605", end=0x7fff009ef778, error=0x7fff009ef784) at / mysqldata/percona-server-locks-detail-5.7.22/strings/dtoa.c:468#1 0x0000000001ec4eda in my_strntod_8bit (cs=0x2e377a0, str=0x7ffee097a803 "204027026112927605", length=18, end=0x7fff009ef778 Err=0x7fff009ef784) at / mysqldata/percona-server-locks-detail-5.7.22/strings/ctype-simple.c:741#2 0x0000000000f5639f in Field_varstring::val_real (this=0x7ffee097ae60) at / mysqldata/percona-server-locks-detail-5.7.22/sql/field.cc:7663#3 0x0000000000fa2b9d in Item_field::val_real (this=0x7ffee0007180) at / mysqldata/percona-server-locks-detail-5.7.22/sql/item.cc:2960#4 0x0000000000fc8d3d in Arg_comparator:: Compare_real (this=0x7ffee0007378) at / mysqldata/percona-server-locks-detail-5.7.22/sql/item_cmpfunc.cc:1745#5 0x0000000000fde0fc in Arg_comparator::compare (this=0x7ffee0007378) at / mysqldata/percona-server-locks-detail-5.7.22/sql/item_cmpfunc.h:92#6 0x0000000000fcafb9 in Item_func_eq::val_int (this=0x7ffee00072a0) at / mysqldata/percona-server-locks-detail-5.7.22/sql/item_cmpfunc.cc:2507#7 0x0000000001581a11 in evaluate_join_record (join=0x7ffee00077b8 Qep_tab=0x7ffee0a77520) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1492#8 0x0000000001581372 in sub_select (join=0x7ffee00077b8, qep_tab=0x7ffee0a77520 End_of_records=false) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:1297#9 0x0000000001580be6 in do_select (join=0x7ffee00077b8) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:950#10 0x000000000157eaa2 in JOIN::exec (this=0x7ffee00077b8) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_executor.cc:199#11 0x0000000001620327 in handle_query (thd=0x7ffee0000c00, lex=0x7ffee0003230, result=0x7ffee00074e8, added_options=0 Removed_options=0) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_select.cc:185#12 0x00000000015d1f77 in execute_sqlcom_select (thd=0x7ffee0000c00, all_tables=0x7ffee0006be0) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5445#13 0x00000000015ca380 in mysql_execute_command (thd=0x7ffee0000c00, first_level=true) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:2939#14 0x00000000015d2fde in mysql_parse (thd=0x7ffee0000c00 Parser_state=0x7fff009f1600) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:5901#15 0x00000000015c6b72 in dispatch_command (thd=0x7ffee0000c00, com_data=0x7fff009f1d70 Command=COM_QUERY) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1490#16 0x00000000015c58ff in do_command (thd=0x7ffee0000c00) at / mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse.cc:1021#17 0x000000000170e578 in handle_connection (arg=0x3605a30) at / mysqldata/percona-server-locks-detail-5.7.22/sql/conn_handler/connection_handler_per_thread.cc:312#18 0x0000000001945538 in pfs _ spawn_thread (arg=0x36e71c0) at / mysqldata/percona-server-locks-detail-5.7.22/storage/perfschema/pfs.cc:2190#19 0x00007ffff7bcfaa1 in start_thread () from / lib64/libpthread.so.0#20 0x00007ffff6b37c4d in clone () from / lib64/libc.so.6 IV. Official document description of the conversion

The following rules describe how conversion occurs for comparison operations:

If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe

Equality comparison operator. For NULL NULL, the result is true. No conversion is needed.

If both arguments in a comparison operation are strings, they are compared as strings.

If both arguments are integers, they are compared as integers.

Hexadecimal values are treated as binary strings if not compared to a number.

If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a

Constant, the constant is converted to a timestamp before the comparison is performed. This is

Done to be more ODBC-friendly. This is not done for the arguments to IN (). To be safe, always

Use complete datetime, date, or time strings when doing comparisons. For example, to achieve best

Results when using BETWEEN with date or time values, use CAST () to explicitly convert the values to

The desired data type.

A single-row subquery from a table or tables is not considered a constant. For example, if a subquery

Returns an integer to be compared to a DATETIME value, the comparison is done as two integers.

The integer is not converted to a temporal value. To compare the operands as DATETIME values

Use CAST () to explicitly convert the subquery value to DATETIME.

If one of the arguments is a decimal value, comparison depends on the other argument. The

Arguments are compared as decimal values if the other argument is a decimal or integer value, or as

Floating-point values if the other argument is a floating-point value.

In all other cases, the arguments are compared as floating-point (real) numbers.

5. Why is the precision lost?

Generally speaking, double occupies 8, that is, 64 bits:

1-bit symbol bit

11-bit index bit

52 Mantissa digit

We can see that the Mantissa digit is the guarantee of accuracy, which is actually not as high as the long int type. So when you check the upper limit of the save, you will lose precision.

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.