Talk about implicit conversion in MySQL

In the course of work, you will encounter many cases of implicit conversion. In addition to causing slow queries, implicit conversion can also cause inaccurate data. This article uses several cases encountered in production to illustrate this.

Basics

Regarding the principles of comparison operations, the MySQL official document describes: https://dev.mysql.com/doc/refman/5.6/en/type-conversion.html

If one of the two sides of the judgment symbol is NULL, the result is null, unless a safe equivalence judgment <=> is used

(none) 05:17:16 >select null = null;
+-------------+
| null = null |
+-------------+
| NULL |
+-------------+
1 row in set (0.00 sec)

(none) 05:34:59 >select null <=> null;
+---------------+
| null <=> null |
+---------------+
| 1 |
+---------------+
1 row in set (0.00 sec)

(none) 05:35:51 >select null != 1;
+-----------+
| null != 1 |
+-----------+
| NULL |
+-----------+
1 row in set (0.00 sec)

How to determine whether the left and right sides are of the same type, for example, if they are both strings, compare them as strings. If it is a number, the comparison is performed as a number.

Note: For the more common comparison of strings and numbers, if the string field is an index field, MySQL cannot find data through the index, such as the following example:

(none) 05:39:42 >select 1='1';
+-------+
| 1='1' |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)

(none) 05:39:44 >select 1='1A';
+--------+
| 1='1A' |
+--------+
| 1 |
+--------+
1 row in set, 1 warning (0.00 sec)

(none) 05:39:47 >select 1='1 '; ##There is a space after 1+--------+
| 1='1 ' |
+--------+
| 1 |
+--------+
1 row in set (0.00 sec)

MySQL considers the number 1 to be equal to '1', '1_', and '1A', so it is not possible to accurately locate the specific value through index binary search.

Hexadecimal compares strings as binary strings.

How to determine if the left side of the symbol is of timestamp or datetime type and the right side is a constant. Before comparison, the constant will be converted to a time type.

Implicit Conversion

The field types are different

In all other cases, the arguments are compared as floating-point (real) numbers.

In addition to the other types of comparisons listed above, the system converts fields and parameters to floating-point types for comparison. Comparisons using floating point numbers (or values ​​converted to floating point) are approximate, because such numbers are not exact. Look at the following two examples

>select '190325171202362933' = 190325171202362931;
+-------------------------------------------+
| '190325171202362933' = 190325171202362931 |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.00 sec)

>select '190325171202362936' = 190325171202362931;
+-------------------------------------------+
| '190325171202362936' = 190325171202362931 |
+-------------------------------------------+
| 1 |
+-------------------------------------------+
1 row in set (0.00 sec)

The values ​​that are intuitively unequal are actually returned as 1 after being judged as equal. This brings two problems: the index cannot be used and the result data is inaccurate

>select '190325171202362931'+0.0;
+--------------------------+
| '190325171202362931'+0.0 |
+--------------------------+
| 1.9032517120236294e17 |
+--------------------------+
1 row in set (0.00 sec)

>select '190325171202362936'+0.0;
+--------------------------+
| '190325171202362936'+0.0 |
+--------------------------+
| 1.9032517120236294e17 |
+--------------------------+
1 row in set (0.00 sec)

Convert the above values ​​to floating point numbers, both are 1.9032517120236294e17, so when they are equal, they are true and True is returned.

in parameters contain multiple types

For a specific case, please refer to the previous article MySQL optimization case 1, where the data types in the in set are different, and the execution plan does not use the index

There is a case exactly like this one in Taobao MySQL Monthly Report (http://mysql.taobao.org/monthly/2017/12/06/), which I recommend to everyone. In short, there is a judgment at the entrance of IN. If the field types in IN are incompatible, it is considered that the index cannot be used.

The assignment logic of arg_types_compatible is:

if (type_cnt == 1) 
    arg_types_compatible = TRUE;

That is, when more than one field type appears in the IN list, the types are considered incompatible and the index cannot be used.

Inconsistent character set types

Environment Preparation:

CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` varchar(20) DEFAULT NULL,
`c2` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`),
KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;


CREATE TABLE `t2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`c1` varchar(20) DEFAULT NULL,
`c2` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_c1` (`c1`),
KEY `idx_c2` (`c2`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;


insert into t1(c1,c2) ​​values('a','a'),('b','b'),('c','c'),
('d','d'),('e','e');
insert into t2(c1,c2) ​​values('a','a'),('b','b'),('c','c'),
('d','d'),('e','e');

Test Results

summary

I hope that through the above cases and the introduction of basic knowledge, developers can avoid detours. When developing and writing SQL, they must clearly define the type of fields, especially fields such as id, xxxid, xxxno that look like numeric types, but may actually be character types.

The above is the details of implicit conversion in MySQL. For more information about MySQL implicit conversion, please pay attention to other related articles on 123WORDPRESS.COM!