MySQL 字符串轉double轉換棧幀（可能丟失精度）

一、轉換案例

最近有朋友討論字符串轉換double丟失精度的問題案例如下：

本案例來自朋友徐晨亮

root@mysqldb?22:12:?[xucl]>?show?create?table?t1\G***************************?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?||?204027026112927603?||?2040270261129276?||?2040270261129275?||?100?||?101?|+--------------------+6?rows?in?set?(0.00?sec)奇怪的現象：root@mysqldb?22:19:?[xucl]>?select?*?from?t1?where?id=204027026112927603;+--------------------+|?id?|+--------------------+|?204027026112927605?||?204027026112927603?|+--------------------+2?rows?in?set?(0.00?sec)什么鬼，明明查的是204027026112927603，為什么204027026112927605也出來了

轉換在官方文檔寫的很清楚，這里兩邊都轉換為了double類型進行比較。

二、簡單測試

實際上我們可以用一個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('1111111111111111111111111111111111');Query?OK,?1?row?affected?(37?min?53.07?sec)mysql>?show?create?table?test11;+--------+----------------------------------------------------------------------------------------+|?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)

可以看到實際上精度已經丟失了。大概16字符到17字符之間，多的可能被忽略掉，字符串丟失精度。鄭州不孕不育醫院：http://jbk.39.net/yiyuanzaixian/zztjyy/

三、轉換函數

這個轉換函數幾百上千行，沒仔細看，太費勁。記錄一下棧幀：https://www.jianshu.com/p/fc56f6221728

#0??my_strtod_int?(s00=0x7ffedc0065f0?'1'?<repeats?34?times>,?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'?<repeats?34?times>,?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'?<repeats?34?times>,?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'?<repeats?34?times>,?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

開頭案例轉換棧幀

#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

四、官方文檔關于轉換的說明

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.

五、為什么會丟失精度

一般來講double占用8直接也就是64位其中：

• 1位 符號位

• 11位 指數位

• 52位 尾數位

我們可以看到尾數位才是精度的保證，它的精度實際上沒有long int類型高。因此當查過保存的上限將會丟失精度。