一、转换案例
最近有朋友讨论字符串转换双丢失精度的问题案例如下:
本案例来自朋友徐晨亮
root@mysqldb二二12:[xucl]比;显示创建表t1 \ G * * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * *表:t1Create表:创建表t1的(“id”varchar(255)默认为空)引擎=InnoDB默认字符集=utf81行组(0.00秒)root@mysqldb 22:19: [xucl]比;从t1 select *, +, - - - - - - - - - - - - - - - - - - + | | id + - - - - - - - - - - - - - - - - - - - - - - + | 204027026112927605 | | 204027026112927603 | | 2040270261129276 | | 2040270261129275 | | 100 | | 101 | + - - - - - - - - - - - - - - - - - - - - - - + 6行组(0.00秒)奇怪的现象:root@mysqldb 22:19: [xucl]比;select * from t1 id=204027026112927603; + - - - - - - - - - - - - - - - - - - - - - - + | | id + - - - - - - - - - - - - - - - - - - - - - - + | 204027026112927605 | 204027026112927603 | | + - - - - - - - - - - - - - - - - - - - - - -在集合(0.00秒)+ 2行什么鬼,明明查的是204027026112927603,为什么204027026112927605也出来了 转换在官方文档写的很清楚,这里两边都转换为了两类型进行比较,
二简单测试
实际上我们可以用一个双类型的字段插入字符串就可以测试这种情况:
mysql>使用testmtsReading表完成信息表和列namesYou可以关闭此功能得到更快的启动与-ADatabase changedmysql>插入test11值(“1111111111111111111111111111111111”),查询好了,1行影响mysql>(37分钟53.07秒);显示创建表test11; + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | + | |表创建表+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + | test11 |创建表“test11”(“n”双重默认空)引擎=InnoDB默认字符集=utf8 | + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + 1行组(0.00秒)mysql>从test11 select *, + - - - - - - - - - - - - - - - - - - - - - - - - n + | | + - - - - - - - - - - - - - - - - - - - - - - - - + | 9.007199254740991 e15 | | 9.007199254740992 e15 | | 9.007199254740991 e17 | | 1.1111111111111112 e31 | | 1.1111111111111112 e31 | | 1.1111111111111111 e33 | | 1.1111111111111111 e33 | | 1.1111111111111111 e33 | | 1.1111111111111111 e33 | | 1.1111111111111111 e33 | | 1.1111111111111111 e33 | + - - - - - - - - - - - - - - - - - - - - - - - - + 11行组(0.00秒) 可以看到实际上精度已经丢失了。大概16字符到17字符之间,多的可能被忽略掉,字符串丢失精度。
三、转换函数
这个转换函数几百上千行,没仔细看,太费劲。记录一下栈帧:
# 0 my_strtod_int (s00=0 x7ffedc0065f0 ' 1 ' & lt;重复34 times>, se=0 x7fffec5eb678,错误=0 x7fffec5eb680 buf=0 x7fffec5ea770“\ 320 \ 001”, buf_size=3680)在/mysqldata/percona-server-locks-detail-5.7.22/字符串/时差。c: 1356 # 1 0 x0000000001ee07c1 my_strtod (str=0 x7ffedc0065f0 ' 1 ' & lt;重复34 times>,=0 x7fffec5eb678结束,错误=0 x7fffec5eb680)在/mysqldata/percona-server-locks-detail-5.7.22/字符串/时差。c: 472 # 2 0 x0000000001ec4eda my_strntod_8bit (c=0 x2e379e0, str=0 x7ffedc0065f0 ' 1 ' & lt;重复34 times>,长度=34,=0 x7fffec5eb678结束,呃=0 x7fffec5eb680)在//ctype-simple mysqldata/percona-server-locks-detail-5.7.22/字符串。c: 741 # 3 0 x0000000000f4dbae Field_double::存储(从=0=0 x7ffedc9a1390, x7ffedc0065f0 ' 1 ' & lt;重复34 times>, len=34, c=0 x2e379e0)/mysqldata/percona-server-locks-detail-5.7.22/sql/字段。答:4825 # 4 0项x0000000000f9bbc7: save_str_value_in_field (=0 x7ffedc006618、字段=0 x7ffedc9a1390结果=0 x7ffedc006628)/mysqldata/percona-server-locks-detail-5.7.22/sql/项。答:561 # 5 0 x0000000000fae204 Item_string:: save_in_field_inner (=0 x7ffedc006618、字段=0 x7ffedc9a1390 no_conversions=false)/mysqldata/percona-server-locks-detail-5.7.22/sql/项。答:6895 # 6 0项x0000000000fad7de: save_in_field (=0 x7ffedc006618、字段=0 x7ffedc9a1390 no_conversions=false)/mysqldata/percona-server-locks-detail-5.7.22/sql/项。答:6752 # 7 0 x0000000001549101 fill_record (thd)=0 x7ffedc000b90表=0 x7ffedc9a8650 ptr=0 x7ffedc9a1388,值=煌?0 x0, insert_into_fields_bitmap=0 x0)/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_base。答:9702 # 8 0 x00000000015493e9 fill_record_n_invoke_before_triggers (thd)=0 x7ffedc000b90 ptr=0 x7ffedc9a1380值=⒈?0 x7ffedc9a8650事件=TRG_EVENT_INSERT num_fields=1)/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_base。答:9784 # 9 0 x00000000017fd477 Sql_cmd_insert:: mysql_insert (thd)=0 x7ffedc000b90这=0 x7ffedc006d80, table_list=0 x7ffedc0067c8)/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_insert。答:751 # 10 0 x00000000018040ed Sql_cmd_insert::执行(这=0 x7ffedc006d80,=0 x7ffedc000b90)/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_insert。答:3121 # 11 0 x00000000015cc753 mysql_execute_command (thd)=0 x7ffedc000b90 first_level=true)/mysqldata/percona-server-locks-detail-5.7.22/sql/sql_parse。null
