Also discuss the implicit conversion in MySQL

1. Environmental statement

Blog Address: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 (one) not NULL, ' ename ' var char (+) is not null, ' JOB ' varchar (0) is not null, ' MGR ' int (one) ' Default ', ' hiredate ' timestamp null default NULL, ' SAL ' Int (default ' 0 '), ' COMM ' int (one) ' default ' 0 ', ' DEPTNO ' int (one) 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=ut F81 row in Set (0.00 sec) 

</pre><pre name= "code" class= "SQL" >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 |   &NBSP; 10 | |  7936 | 23456  | BOSCO-DBA | 7788 | 2014-09-13 16:13:56 | 2450 |  800 |     |+-------+--------+-----------+------+---------------------+------+------+--------+15 rows in Set ( 0.00 sec)

2. Numeric type (int)

First of all, as in the test table EMP Empno is the primary key, type int, then:

SELECT * from emp where empno= ' 7788 ';

Will an implicit conversion be generated?

The following experiments show that:

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 | |+-------+-------+---------+------+---------------------+------+------+--------+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 | |+-------+-------+---------+------+---------------------+------+------+--------+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) 

Visible, for data type fields, even if the type is inconsistent, it does not affect whether the index is used, the execution plan is the same, and no implicit conversions occur. However, it is still recommended to avoid such SQL in the production library as much as possible.

Attention:

Numeric types have an implicit conversion, and if a number is switched on, subsequent characters are truncated, and only the preceding numeric values are taken, if not with the digital switch will be set to 0. As follows:

MySQL [test]> SELECT * from emp where empno= ' 7788ab12 '; # # This is equivalent to empno=7788, the latter ab12 will be truncated and does not affect the use of the index +-------+-------+---------+------+---------------------+------+------ +--------+| EMPNO | ename | JOB | MGR | HireDate | SAL | COMM |  DEPTNO |+-------+-------+---------+------+---------------------+------+------+--------+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 |     NULL | |+-------+-------+---------+------+---------------------+------+------+--------+1 row in set, 1 Warning (0.00 sec) MySQL [test]> Show warnings;+---------+------+----------------------------------------------+| Level | Code | Message |+---------+------+----------------------------------------------+| 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=0empty set (0.01 sec) 

3. Character type (varchar)

Similarly, for the ename field (varchar type) in the test table EMP, there is a secondary index of idx_ename, and a value of ename is full-numeric, if any such query:

SELECT * from EMP where ename=23456;

Will the above SQL appear implicitly converted?

The following experiments show that:

MySQL [test]> SELECT * from emp where ename= ' 23456 '; +-------+-------+-----------+------+---------------------+---- --+------+--------+| EMPNO | ename | JOB | MGR | HireDate | SAL | COMM |  DEPTNO |+-------+-------+-----------+------+---------------------+------+------+--------+| 7936 | 23456 | BOSCO-DBA | 7788 | 2014-09-13 16:13:56 |  2450 |     800 | |+-------+-------+-----------+------+---------------------+------+------+--------+1 row in Set (0.00 sec) MySQL [   Test]> explain select * from emp where ename= ' 23456 '; # # Normal, you can use the index idx_ename+----+-------------+-------+------+---------------+-----------+---------+-------+------ +-----------------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows | Extra |+----+-------------+-------+------+---------------+-----------+---------+-------+------+-----------  ------------+| 1 | Simple | EMP | Ref | Idx_ename | 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, you can still find the result +-------+-------+-----------+------+---------------------+------+------+--------+| EMPNO | ename | JOB | MGR | HireDate | SAL | COMM |  DEPTNO |+-------+-------+-----------+------+---------------------+------+------+--------+| 7936 | 23456 | BOSCO-DBA | 7788 | 2014-09-13 16:13:56 |  2450 |     800 | |+-------+-------+-----------+------+---------------------+------+------+--------+1 row in Set, Warnings (0.00   SEC) MySQL [test]> explain select * from EMP where ename=23456; # # When the varchar type does not match, the index is invalid, select the full table Scan +----+-------------+-------+------+---------------+------+---------+------+----- -+-------------+| ID | Select_type | Table | Type | Possible_keys | Key | Key_len | Ref | Rows |  Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | Simple | EMP | All | Idx_ename | NULL | NULL |   NULL | 15 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in Set ( 0.00 sec)

It can be seen that, in the case of a character type, the use of the index is affected when the type inconsistency occurs, and an implicit conversion is generated.

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

--Bosco qq:375612082

----END----
-------------------------------------------------------------------------------------------------------
Copyright, the article allows reprint, but must be linked to the source address, otherwise investigate legal responsibility!

Also discuss the implicit conversion in MySQL