MySQL의 놀라운 암시적 변환을 살펴보세요

mysql 튜토리얼 칼럼에서는 관련 암시적 변환을 소개합니다

더 많은 관련 무료 학습 권장사항: mysql tutorial(동영상)

一, 문제 설명

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)

이상한 현상:

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

뭐야 당연히 204027026112927603인데 왜 204027026112927605도 나왔는지

두번째, 소스코드 설명

스택콜 관계는 다음과 같습니다:

어디 < code>JOIN::exec()는 실행 진입점, Arg_comparator::compare_real()은 동등성 판단을 위한 함수로 정의는 다음과 같습니다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。

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

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

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

/*
  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).
*/

비교 단계는 다음과 같습니다. 그림과 같이 t1 테이블의 id 열을 한 행씩 읽어서 val1에 배치하고 캐시에 상수 204027026112927603이 존재하고 유형이 double(2.0402702611292762E+17)이므로 통과한 후 여기서 val2 값은 val2=2.0402702611292762E+17 입니다.

첫 번째 행을 스캔하면 204027026112927605를 double로 변환한 값이 2.0402702611292762e17이 됩니다. 방정식이 성립되고 정규 행으로 판단됩니다. 마찬가지로 계속해서 스캔하면 204027026112927603도 일치합니다. ="rich_pages" src="https://img.php.cn/upload/article/000/000/052/7c4b6d4d87941f0fc37f0c115a3cf15e-3.jpg" alt="MySQL의 놀라운 암시적 변환을 살펴보세요"/>

감지하는 방법 string형 숫자로 변환 double형이 오버플로 되나요? 여기서 테스트해본 결과 숫자가 16자리를 초과하면 double형으로의 변환이 더 이상 정확하지 않습니다. 예를 들어 20402702611292712(그림에서는 val1)로 표시됩니다. )

1. 

   MySQL 문자열을 double로 변환하는 정의 함수는 다음과 같습니다:

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

   실제 변환 함수 my_strtod_int는 dtoa.c에 있습니다(너무 복잡합니다. 댓글)

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

2. 이 경우에는 우리 테스트에서 Overflow가 없었습니다.

   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.

   의 결과는 예상과 일치하며, 이 경우 올바른 쓰기는 rrreee이어야 합니다. 3. 결론
암시적 유형 변환을 피하세요. 암시적 변환 유형에는 주로 일관성 없는 필드 유형이 포함됩니다. in 매개변수에는 여러 유형, 문자 세트 유형 또는 일관성 없는 데이터 정렬 규칙이 포함됩니다.
암시적 유형 변환으로 인해 사용이 불가능해질 수 있습니다. 인덱스, 부정확한 쿼리 결과 등이 있으므로 🎜🎜🎜🎜🎜 사용 시 주의 깊게 선별해야 합니다. 필드 정의 시 숫자 유형은 int 또는 bigint로 정의하는 것이 좋습니다. 유형, 문자 집합 및 조합 규칙을 일관되게 유지해야 합니다🎜🎜🎜🎜🎜🎜마지막으로 공식 웹사이트에서 암시적 유형 변환에 대한 지침을 게시하세요🎜 🎜🎜🎜rrreee

위 내용은 MySQL의 놀라운 암시적 변환을 살펴보세요의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!