Shulou(Shulou.com)05/31 Report--

This article mainly shows you the "MySQL implicit conversion example analysis", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and study the "MySQL implicit conversion example analysis" this article.

I. description of the problem

Root@mysqldb 22:12: [xucl] > show create table T1\ gateway * 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)

Strange phenomenon:

Root@mysqldb 22:19: [xucl] > select * from T1 where id=204027026112927603;+-+ | id | +-+ | 204027026112927605 | | 204027026112927603 | +-+ 2 rows in set (sec) 640?wx_fmt=jpeg

What the hell? it's 204027026112927603. Why did 204027026112927605 come out?

II. Source code interpretation

The stack call relationship is as follows:

Where JOIN::exec () is the entry of execution, and Arg_comparator::compare_real () is the function for equivalence judgment, which is defined as follows

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;}

The comparison step is shown in the following figure. The id column of the T1 table is read row by row and put into val1, while the constant 204027026112927603 exists in cache with a type of double (2.0402702611292762E+17), so the value is passed to val2 here and then val2=2.0402702611292762E+17.

When the first line is scanned, the value of 204027026112927605 converted to doule is 2.0402702611292762e17, and the equation holds. If the line is judged to be qualified, continue to scan, and the same is true for 204027026112927603.

How to detect whether the conversion of numbers of type string to type doule is overflowed? It has been tested here that when the number exceeds 16 digits, the conversion to double type is no longer accurate. For example, 20402702611292711 will be represented as 20402702611292712 (see val1 in the figure).

The definition function for converting MySQL string to double is as follows:

{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 select * from t1 where id=101;+------+| id |+------+| 101 |+------+1 row in set (0.00 sec) 结果符合预期，而在本例中，正确的写法应当是 root@mysqldb 22:19: [xucl]>

Select * from T1 where id='204027026112927603';+-+ | id | +-+ | 204027026112927603 | +-+ 1 row in set (0.01sec)

III. Conclusion

To avoid implicit type conversion, the main types of implicit conversion are inconsistent field types, multiple types of in parameters, character set types or inconsistent proofreading rules, etc.

Implicit type conversion may result in unavailability of indexes, inaccurate query results, etc., so it must be carefully screened when using it.

It is recommended that the numeric type be defined as int or bigint when the field is defined, and the associated field must be consistent with the type, character set and proofreading rules when the table is associated.

Finally, post a description of implicit type conversion on the official website.

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.

The above is all the content of the article "sample Analysis of MySQL implicit conversion". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.