We also discuss implicit conversion in MySQL _ MySQL

1. environment description blog address: http: blogcsdnnethw_liboarticledetails39252427RHEL64x86_6443; MySQL5619 Test table: mySQL [test] showcreatetableempG ************************** 1. environment description

Blog: http://blog.csdn.net/hw_libo/article/details/39252427

RHEL 6.4 x86_64 + MySQL 5.6.19

Test table:

MySQL [test]> show create table emp\G*************************** 1. row ***************************       Table: empCreate Table: CREATE TABLE `emp` (  `EMPNO` int(11) NOT NULL,  `ENAME` varchar(15) NOT NULL,  `JOB` varchar(15) NOT NULL,  `MGR` int(11) DEFAULT '0',  `HIREDATE` timestamp NULL DEFAULT NULL,  `SAL` int(20) DEFAULT '0',  `COMM` int(11) DEFAULT '0',  `DEPTNO` int(11) NOT NULL,  PRIMARY KEY (`EMPNO`),  KEY `idx_deptno` (`DEPTNO`),  KEY `idx_sal` (`SAL`),  KEY `idx_comm` (`COMM`),  KEY `idx_ename` (`ENAME`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)

MySQL [test]> select * from emp;+-------+--------+-----------+------+---------------------+------+------+--------+| EMPNO | ENAME  | JOB       | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |+-------+--------+-----------+------+---------------------+------+------+--------+|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800 | NULL |     20 ||  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600 |  300 |     30 ||  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250 |  500 |     30 ||  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975 |    0 |     20 ||  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250 | 1400 |     30 ||  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850 |    0 |     30 ||  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450 |    0 |     10 ||  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 ||  7839 | KING   | PRESIDENT |    0 | 1981-11-17 00:00:00 | 5000 |    0 |     10 ||  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500 |    0 |     30 ||  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 00:00:00 | 1100 |    0 |     20 ||  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950 |    0 |     30 ||  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000 |    0 |     20 ||  7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300 |    0 |     10 ||  7936 | 23456  | BOSCO-DBA | 7788 | 2014-09-13 16:13:56 | 2450 |  800 |     10 |+-------+--------+-----------+------+---------------------+------+------+--------+15 rows in set (0.00 sec)

2. value type (int)

First, I would like to raise a question. in the above test table, empno is the primary key and the type is int, so:

select * from emp where empno='7788';

Will implicit conversions be generated?

The following experiment proves:

MySQL [test]> select * from emp where empno=7788;+-------+-------+---------+------+---------------------+------+------+--------+| EMPNO | ENAME | JOB     | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |+-------+-------+---------+------+---------------------+------+------+--------+|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |+-------+-------+---------+------+---------------------+------+------+--------+1 row in set (0.00 sec)MySQL [test]> explain select * from emp where empno=7788;+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | emp   | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+1 row in set (0.00 sec)MySQL [test]> select * from emp where empno='7788';+-------+-------+---------+------+---------------------+------+------+--------+| EMPNO | ENAME | JOB     | MGR  | HIREDATE            | SAL  | COMM | DEPTNO |+-------+-------+---------+------+---------------------+------+------+--------+|  7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 | NULL |     20 |+-------+-------+---------+------+---------------------+------+------+--------+1 row in set (0.00 sec)MySQL [test]> explain select * from emp where empno='7788';+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+|  1 | SIMPLE      | emp   | const | PRIMARY       | PRIMARY | 4       | const |    1 | NULL  |+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+1 row in set (0.00 sec)

It can be seen that for data type fields, even if the types are inconsistent, it does not affect whether to use the index. the execution plan is the same and no implicit conversion is generated. However, we recommend that you avoid such SQL statements in the production database.

Note:

The value type has an implicit conversion. if it is switched to a number, the subsequent characters will be truncated and only the previous numeric values will be taken. if it is not switched to a number, it will be set to 0. As follows:

MySQL [test]> select * from emp where empno = '7788ab12'; # This is equivalent to empno = 7788, and the next ab12 will be truncated, and does not affect index usage + ------- + --------- + ------ + --------------------- + ------ + -------- + | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | + ------- + --------- + ------ + ------------------- + ------ + -------- + | 7788 | SCOTT | ANALYST | 7566 | 00:00:00 | 3000 | NULL | 20 | + ------- + --------- + ------ + --------------------- + ------ + -------- + 1 row in set, 1 warning (0.00 sec) MySQL [test]> show warnings; + --------- + ------ + -------------------------------------------- + | Level | Code | Message | + --------- + ------ + Warning + | Warning | 1292 | Truncated incorrect DOUBLE value: '7788ab12' | + --------- + ------ + -------------------------------------------- + 1 row in set (0.00 sec) MySQL [test]> select * from emp where empno = 'ab7788 '; # This is equivalent to empno = 0 Empty set (0.01 sec)

3. character type (varchar)

Similarly, for the ename field (varchar type) in the emp test table, there is an auxiliary index idx_ename, and one value in the ename is a full number. if there is such a query:

select * from emp where ename=23456;

Will the above SQL statements be implicitly converted?

The following experiment proves:

MySQL [test]> select * from emp where ename = '201312 '; + ------- + ----------- + ------ + ------------------- + ------ + -------- + | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | + ------- + ----------- + ------ + --------------------- + ------ + -------- + | 7936 | 23456 | BOSCO-DBA | 7788 | 16:13:56 | 2450 | 800 | 10 | + ------- + ----------- + ------ + ----------------- + ------ + -------- + 1 row in set (0.00 sec) mySQL [test]> explain select * from emp where ename = '000000'; # normally, you can use the index idx_ename + ---- + --------------- + ------- + ------ + ------------- + ----------- + --------- + ------- + ------ + keys + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------- + ------ + ------------- + ----------- + --------- + ------- + ------ + keys + | 1 | SIMPLE | emp | ref | idx_ename | 47 | const | 1 | Using index condition | + ---- + --------------- + ------- + ------ + --------------- + ----------- + --------- + ------- + ------ + ----------------------- + 1 row in set (0.00 sec)

MySQL [test]> select * from emp where ename = 23456; # When the varchar type is incorrect, still the result can be found + ------- + ----------- + ------ + ------------------- + ------ + -------- + | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | + ------- + ----------- + ------ + ------------------- + ------ + -------- + | 7936 | 23456 | BOSCO-DBA | 7788 | 16:13:56 | 2450 | 800 | 10 | + ------- + ----------- + ------ + --------------------- + ------ + -------- + 1 row in set, 14 warnings (0.00 sec) MySQL [test]> explain select * from emp where ename = 23456; # The index is invalid when the varchar type does not match, full table scan + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + --------------- + | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | + ---- + ------------- + ------- + ------ + ------------- + ------ + --------- + ------ + ------------- + | 1 | SIMPLE | emp | ALL | idx_ename | NULL | NULL | 15 | Using where | + ---- + ------------- + ------- + ------ + --------------- + ------ + --------- + ------ + ------------- + 1 row in set (0.00 sec)

It can be seen that if it is a character type, when the types are inconsistent, it will affect the use of the index and will generate implicit conversion.

Blog: http://blog.csdn.net/hw_libo/article/details/39252427

-- Bosco QQ: 375612082

---- END ----
Bytes -------------------------------------------------------------------------------------------------------
All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!