一、轉(zhuǎn)換案例

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

站在用戶的角度思考問題，與客戶深入溝通，找到漢源網(wǎng)站設(shè)計(jì)與漢源網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗(yàn)好的作品，建站類型包括：成都網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、國際域名空間、虛擬主機(jī)、企業(yè)郵箱。業(yè)務(wù)覆蓋漢源地區(qū)。

本案例來自朋友徐晨亮

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)奇怪的現(xiàn)象：root@mysqldb?22:19:?[xucl]>?select?*?from?t1?where?id=204027026112927603;+--------------------+|?id?|+--------------------+|?204027026112927605?||?204027026112927603?|+--------------------+2?rows?in?set?(0.00?sec)什么鬼，明明查的是204027026112927603，為什么204027026112927605也出來了

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

二、簡單測試

實(shí)際上我們可以用一個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)

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

三、轉(zhuǎn)換函數(shù)

這個轉(zhuǎn)換函數(shù)幾百上千行，沒仔細(xì)看，太費(fèi)勁。記錄一下棧幀：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

開頭案例轉(zhuǎn)換棧幀

#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

四、官方文檔關(guān)于轉(zhuǎn)換的說明

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位 指數(shù)位

• 52位 尾數(shù)位

我們可以看到尾數(shù)位才是精度的保證，它的精度實(shí)際上沒有l(wèi)ong int類型高。因此當(dāng)查過保存的上限將會丟失精度。

分享名稱：MySQL字符串轉(zhuǎn)double轉(zhuǎn)換棧幀（可能丟失精度）
分享URL：http://www.rwnh.cn/article4/jgpcie.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供品牌網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)、電子商務(wù)、云服務(wù)器、移動網(wǎng)站建設(shè)、搜索引擎優(yōu)化

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)