最新公告
  • 欢迎您光临吆喝资源网,本站提供站长亲测的网站源码下载,打造专业的源码网!立即加入我们
  • 看看 MySQL令人咋舌的隐式转换

    mysql教程栏目介绍相关的隐式转换

    更多相关免费学习推荐:mysql教程(视频)

    一、问题描述

    [email protected] 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)
    
    [email protected] 22:19:  [xucl]> select * from t1;
    +--------------------+
    | id                 |
    +--------------------+
    | 204027026112927605 |
    | 204027026112927603 |
    | 2040270261129276   |
    | 2040270261129275   |
    | 100                |
    | 101                |
    +--------------------+
    6 rows in set (0.00 sec)

    奇怪的现象:

    [email protected] 22:19:  [xucl]> select * from t1 where id=204027026112927603;
    +--------------------+
    | id                 |
    +--------------------+
    | 204027026112927605 |
    | 204027026112927603 |
    +--------------------+
    2 rows in set (0.00 sec)

    640?wx_fmt=jpeg

    什么鬼,明明查的是204027026112927603,为什么204027026112927605也出来了

    二、源码解释

    堆栈调用关系如下所示:

    640?wx_fmt=jpeg

    其中JOIN::exec()是执行的入口,Arg_comparator::compare_real()是进行等值判断的函数,其定义如下

    int Arg_comparator::compare_real()
    {
      /*
        Fix yet another manifestation of Bug#2338. 'Volatile' will instruct
        gcc to flush double values out of 80-bit Intel FPU registers before
        performing the comparison.
      */
      volatile double val1, val2;
      val1= (*a)->val_real();
      if (!(*a)->null_value)
      {
        val2= (*b)->val_real();
        if (!(*b)->null_value)
        {
          if (set_null)
            owner->null_value= 0;
          if (val1 < val2)  return -1;
          if (val1 == val2) return 0;
          return 1;
        }
      }
      if (set_null)
        owner->null_value= 1;
      return -1;
    }

    比较步骤如下图所示,逐行读取t1表的id列放入val1,而常量204027026112927603存在于cache中,类型为double类型(2.0402702611292762E+17),所以到这里传值给val2后val2=2.0402702611292762E+17。

    640?wx_fmt=jpeg

    当扫描到第一行时,204027026112927605转成doule的值为2.0402702611292762e17,等式成立,判定为符合条件的行,继续往下扫描,同理204027026112927603也同样符合

    640?wx_fmt=jpeg

    如何检测string类型的数字转成doule类型是否溢出呢?这里经过测试,当数字超过16位以后,转成double类型就已经不准确了,例如20402702611292711会表示成20402702611292712(如图中val1)

    640?wx_fmt=jpeg

    640?wx_fmt=jpeg

    MySQL string转成double的定义函数如下:

    {
      char buf[DTOA_BUFF_SIZE];
      double res;
      DBUG_ASSERT(end != NULL && ((str != NULL && *end != NULL) ||
                                  (str == NULL && *end == NULL)) &&
                  error != NULL);
    
      res= my_strtod_int(str, end, error, buf, sizeof(buf));
      return (*error == 0) ? res : (res < 0 ? -DBL_MAX : DBL_MAX);
    }

    真正转换函数my_strtod_int位置在dtoa.c(太复杂了,简单贴个注释吧)

    /*
      strtod for IEEE--arithmetic machines.
     
      This strtod returns a nearest machine number to the input decimal
      string (or sets errno to EOVERFLOW). Ties are broken by the IEEE round-even
      rule.
     
      Inspired loosely by William D. Clinger's paper "How to Read Floating
      Point Numbers Accurately" [Proc. ACM SIGPLAN '90, pp. 92-101].
     
      Modifications:
     
       1. We only require IEEE (not IEEE double-extended).
       2. We get by with floating-point arithmetic in a case that
         Clinger missed -- when we're computing d * 10^n
         for a small integer d and the integer n is not too
         much larger than 22 (the maximum integer k for which
         we can represent 10^k exactly), we may be able to
         compute (d*10^k) * 10^(e-k) with just one roundoff.
       3. Rather than a bit-at-a-time adjustment of the binary
         result in the hard case, we use floating-point
         arithmetic to determine the adjustment to within
         one bit; only in really hard cases do we need to
         compute a second residual.
       4. Because of 3., we don't need a large table of powers of 10
         for ten-to-e (just some small tables, e.g. of 10^k
         for 0 <= k <= 22).
    */

    既然是这样,我们测试下没有溢出的案例

    [email protected] 23:30:  [xucl]> select * from t1 where id=2040270261129276;
    +------------------+
    | id               |
    +------------------+
    | 2040270261129276 |
    +------------------+
    1 row in set (0.00 sec)
    
    [email protected] 23:30:  [xucl]> select * from t1 where id=101;
    +------+
    | id   |
    +------+
    | 101  |
    +------+
    1 row in set (0.00 sec)

    结果符合预期,而在本例中,正确的写法应当是

    [email protected] 22:19:  [xucl]> select * from t1 where id='204027026112927603';
    +--------------------+
    | id                 |
    +--------------------+
    | 204027026112927603 |
    +--------------------+
    1 row in set (0.01 sec)

    三、结论

    1. 避免发生隐式类型转换,隐式转换的类型主要有字段类型不一致、in参数包含多个类型、字符集类型或校对规则不一致等

    2. 隐式类型转换可能导致无法使用索引、查询结果不准确等,因此在使用时必须仔细甄别

    3. 数字类型的建议在字段定义时就定义为int或者bigint,表关联时关联字段必须保持类型、字符集、校对规则都一致

    4. 最后贴一下官网对于隐式类型转换的说明吧

    1、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.
    2、If both arguments in a comparison operation are strings, they are compared as strings.
    3、If both arguments are integers, they are compared as integers.
    4、Hexadecimal values are treated as binary strings if not compared to a number.
    5、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.
    6、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.
    7、In all other cases, the arguments are compared as floating-point (real) numbers.

    以上就是看看 MySQL令人咋舌的隐式转换的详细内容,更多请关注吆喝资源网其它相关文章!

    本文转自PHP中文网,吆喝网(http://yaoohe.com)搜集整理,如有疑问请联系本站客服!

    1. 本站所有资源来源于用户上传和网络,如有侵权请邮件联系站长!
    2. 分享目的仅供大家学习和交流,您必须在下载后24小时内删除!
    3. 不得使用于非法商业用途,不得违反国家法律。否则后果自负!
    4. 本站提供的源码、模板、插件等等其他资源,都不包含技术服务请大家谅解!
    5. 如有链接无法下载、失效或广告,请联系管理员处理!
    6. 本站资源售价只是赞助,收取费用仅维持本站的日常运营所需!
    7. 如遇到加密压缩包,默认解压密码为"yaoohe.com",如遇到无法解压的请联系管理员!
    8. 特别声明:本站源码除标明“已测试”外,其它都未测试,有BUG概本站概不负责。
    吆喝资源网 » 看看 MySQL令人咋舌的隐式转换

    常见问题FAQ

    免费下载或者VIP会员专享资源能否直接商用?
    本站所有资源版权均属于原作者所有,这里所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。更多说明请参考 VIP介绍。
    提示下载完但解压或打开不了?
    最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用百度网盘软件或迅雷下载。若排除这种情况,可在对应资源底部留言,或 联络我们.。
    找不到素材资源介绍文章里的示例图片?
    对于PPT,KEY,Mockups,APP,网页模版等类型的素材,文章内用于介绍的图片通常并不包含在对应可供下载素材包内。这些相关商业图片需另外购买,且本站不负责(也没有办法)找到出处。 同样地一些字体文件也是这种情况,但部分素材会在素材包内有一份字体下载链接清单。
    吆喝资源网
    一个高级程序员模板开发平台

    发表评论

    • 6783会员总数(位)
    • 19340资源总数(个)
    • 108本周发布(个)
    • 4 今日发布(个)
    • 1826稳定运行(天)

    专业的网站源码网,提供精品php网站源码下载,小程序源码下载等!!

    小程序源码 了解详情
    升级SVIP尊享更多特权立即升级