?2016-2026 Matools All rights reserved,
粵ICP備17059708號
簡單記錄棧幀以備后用
歡迎關(guān)注我的《深入理解MySQL主從原理 32講 》，如下：
一、轉(zhuǎn)換案例
最近有朋友討論字符串轉(zhuǎn)換double丟失精度的問題案例如下：
本案例來自朋友徐晨亮
root@mysqldb 22:12: [xucl]> show create table t1\G
*************************** 1. row *************************** Table: t1 Create
Table: CREATE TABLE `t1` ( `id` varchar(255) DEFAULT NULL ) ENGINE=InnoDB
DEFAULT CHARSET=utf8 1 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類型進行比較。
二、簡單測試
實際上我們可以用一個double類型的字段插入字符串就可以測試這種情況：
mysql> use testmts Reading table information for completion of table and
column names You can turn off this feature to get a quicker startup with -A
Database changed mysql> 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)
可以看到實際上精度已經(jīng)丟失了。大概16字符到17字符之間，多的可能被忽略掉，字符串丟失精度。
三、轉(zhuǎn)換函數(shù)
這個轉(zhuǎn)換函數(shù)幾百上千行，沒仔細看，太費勁。記錄一下棧幀：
#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ù)位才是精度的保證，它的精度實際上沒有l(wèi)ong int類型高。因此當查過保存的上限將會丟失精度。
作者微信：gp_22389860
熱門工具 換一換