MySQL和Oracle中的隐式转换

今天在处理一个问题的时候，需要根据其他部门提供的sql语句对一个表中的数据进行了筛查。
语句类似下面的形式
> SELECT MAX_LEVEL,LOGOUT_TIME,CURRENT_DATE AS NOWTIME,cn_master FROM t_test_october_back_a WHERE ID in ( 100, 200, 300, 400, 500) ;
+-----------+---------------+------------+-----------+
| MAX_LEVEL | LOGOUT_TIME   | NOWTIME    | ID|
+-----------+---------------+------------+-----------+
|         1 | 1440407918000 | 2015-08-31 | 100|
|        100| 1441009281000 | 2015-08-31 | 200|
|         1 | 1440408002000 | 2015-08-31 | 300|
+-----------+---------------+------------+-----------+
x rows in set, 65535 warnings (10.98 sec)
本来这一个简单查询就完成了，也得到了业务部门需要的数据情况，但是查看最后一行的内容，还是有些蹊跷。如果观察仔细，对于这种id的数据查询，走索引的话，绝对不会再10秒左右，这是第一个奇怪的地方，第二个奇怪的地方就是65535 warnings，一个简单查询怎么会有这么多的warnings
最开始怕show warnings的时候会一下子显示出来6万多行数据，还小小担心了一下，结果输出的结果只有64行。
>show warnings;
+---------+------+----------------------------------------------------------------+
| Level   | Code | Message                                                        |
+---------+------+----------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '3506996@abc.com'       |
| Warning | 1292 | Truncated incorrect DOUBLE value: '28366@abc.com'    |
| Warning | 1292 | Truncated incorrect DOUBLE value: '81700700@abc.com'     |
| Warning | 1292 | Truncated incorrect DOUBLE value: '391112900@abc.com'     |
| Warning | 1292 | Truncated incorrect DOUBLE value: '867964771@abc.com'    |
...
64 rows in set (0.00 sec)

查看建表语句,发现这个id列是varchar类型的。
>show create table  t_test_october_back_a;
| t_tl_october_back_a | CREATE TABLE `t_tl_october_back_a` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `id` varchar(60) NOT NULL,
  `area_server` varchar(80) NOT NULL,
  `max_level` tinyint(4) NOT NULL,
  `logout_time` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_test_october_back_cn_master` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2042252493 DEFAULT CHARSET=gbk |

我们就顺着错误信息来看看，把警告中的部分直接作为参数,发现查询的时间极快。
>select *from t_tl_october_back_a where cn_master ='867964771@abc.com';
+-------+-------------------------+---------------------------+-----------+---------------+
| id    | cn_master               | area_server               | max_level | logout_time   |
+-------+-------------------------+---------------------------+-----------+---------------+
| 18723 | 867964771@abc.com | abcd-abcd            |       104 | 1434446979000 |
+-------+-------------------------+---------------------------+-----------+---------------+
1 row in set (0.00 sec)
对此我们来通过执行计划来简单对比一下。
如果使用数字的方式，会走全表扫描
> explain SELECT MAX_LEVEL,LOGOUT_TIME FROM t_tl_october_back_a WHERE CN_MASTER in ( 100, 200);
+----+-------------+---------------------+------+-------------------------------+------+---------+------+----------+-------------+
| id | select_type | table               | type | possible_keys                 | key  | key_len | ref  | rows     | Extra       |
+----+-------------+---------------------+------+-------------------------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | t_test_october_back_a | ALL  | idx_test_october_back_cn_master | NULL | NULL    | NULL | 28597841 | Using where |
+----+-------------+---------------------+------+-------------------------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)
而如果使用字符的形式，执行计划就会走索引，执行效果就是预期的样子。
>explain SELECT MAX_LEVEL,LOGOUT_TIME FROM t_tl_october_back_a WHERE CN_MASTER = '100' ;
+----+-------------+---------------------+-------+-------------------------------+-------------------------------+---------+-------+------+-------+
| id | select_type | table               | type  | possible_keys                 | key                           | key_len | ref   | rows | Extra |
+----+-------------+---------------------+-------+-------------------------------+-------------------------------+---------+-------+------+-------+
|  1 | SIMPLE      | t_test_october_back_a | const | idx_test_october_back_cn_master | idx_test_october_back_cn_master | 122     | const |    1 |       |
+----+-------------+---------------------+-------+-------------------------------+-------------------------------+---------+-------+------+-------+
1 row in set (0.00 sec)
通过上面的例子可以看到，在查询的时候抛出的警告，其实就是在做类型转换的时候本来输出的是数字类型，就会尝试做隐式转换，而那个65535只是一个最大限制而已，表中的数据其实已经远远超过千万。
这个时候我们大体感受到了隐式转换在MySQL中的一些影响，我们来看看在MySQL 5.6和Oracle中的表现如何。
###############
MYSQL 5.6
mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.6.23-enterprise-commercial-advanced-log |
+-------------------------------------------+
mysql> create table test (id1 int,id2 varchar(10));
mysql> insert into test values(1,'1');
mysql> insert into test values(2,'2');
mysql> insert into test values(3,'3');
mysql> commit;
mysql> create index idx_id1 on test(id1);
mysql> create index idx_id2 on test(id2);
隐式转换，有数字转换为字符的时候，直接走了索引扫描
mysql> explain select * from test where id1='1';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | ref  | idx_id1       | idx_id1 | 5       | const |    1 | NULL  |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.01 sec)

隐式转换，由字符转换为数字的时候，直接走了全表扫描
mysql> explain select *from test where id2=2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | idx_id2       | NULL | NULL    | NULL |    3 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

###############
Oracle 11gR2

sqlplus -v
SQL*Plus: Release 10.2.0.3.0 - Production

SQL> create table test (id1 number,id2 varchar2(10));
SQL> insert into test values(1,'1');
SQL> begin                   
  2  for i in 1..100 loop
  3  insert into test values(i,chr(39)||i||chr(39));
  4  end loop;
  5  commit;
  6  end;
  7  /
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('TEST','TEST',CASCADE=>TRUE);
隐式转换，由数字转换为字符的时候，直接走了索引扫描
SQL> explain plan for select *from test where id1='2';
SQL> select *from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3847161316
---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |     7 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST    |     1 |     7 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_ID1 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
   2 - access("ID1"=2)

隐式转换，由字符转换为数字的时候，直接走了全表扫描
SQL> explain plan for select *from test where id2=3;
SQL> select *from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     7 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 - filter(TO_NUMBER("ID2")=3)

可见在这个方面MySQL和Oracle中的表现是一致的，对于这种隐式转换还是要多加注意。

时间： 2024-09-21 17:15:33

MySQL和Oracle中的隐式转换的相关文章

从Java的类型转换看MySQL和Oracle中的隐式转换(二)

说起数据类型转换,在开发中如此,在数据库中也是如此,之前简单对比过MySQL和Oracle的数据类型转换情况,可以参见MySQL和Oracle中的隐式转换 http://blog.itpub.net/23718752/viewspace-1787973/ 不过当时写完之后,有个读者随口问了一句为什么,为什么呢?似乎自己还是一知半解,说是规则,无规矩不成方圆,倒也无可非议,不过我觉得还是要再看看,看看还能有哪些收获,接下来的内容我就不能保证正确性了,希望大家明辨,也希望提出意见,毕竟就是希望把问题

C语言中自动隐式转换与类型强制转换实例分析_C 语言

本文通过一个C程序实例对C语言中自动隐式转换与类型强制转换的注意点进行深入分析,详情如下: 先看一个C程序: #include<stdlib.h> #include<stdio.h> #include<conio.h> double proc(int q){ int n; double sum,t;//本例的关键就在这几个变量的类型上 sum = 2.0; while(sum<=q){ t=sum; //sum = sum+(n+1)/n;//自动隐式转换 sum

ORACLE绑定变量隐式转换导致性能问题

   年后一次系统升级后,监控数据库的工具DPA发现数据库的Total Wait时间突然飙增,如下截图所示,数据库的总体等待时间对比升级前飙增了非常多 另 外就是发现出现了较多的等待事件,主要有latch: cache buffers chains. latch: shared pool .db file scattered read.根据这边的监控发现TOP SQL里面从升级前的0次变为了一天的一万多次(有些甚至更多),分析过后我们就找开发人员了解一下系统升级变跟的内容和改动 开 发人员坚定的

Scalaz（1）－ 基础篇：隐式转换解析策略－Implicit resolution

  在正式进入scalaz讨论前我们需要理顺一些基础的scalaz结构组成概念和技巧.scalaz是由即兴多态(ad-hoc polymorphism)类型(typeclass)组成.scalaz typeclass在scala中的应用有赖于scala compiler的一项特别功能:隐式转换(implicit conversion),使程序表述更精简.由于隐式转换是一项compiler功能,在程序编译(compile)的时候是由compiler来进行类型转换代码的产生和替代的.   让我们先了

Scala入门到精通——第十九节 隐式转换与隐式参数（二）

作者:摇摆少年梦 配套视频地址:http://www.xuetuwuyou.com/course/12 本节主要内容 隐式参数中的隐式转换 函数中隐式参数使用概要 隐式转换问题梳理 1. 隐式参数中的隐式转换 前一讲中,我们提到函数中如果存在隐式参数,在使用该函数的时候如果不给定对应的参数,则编译器会自动帮我们搜索相应的隐式值,并将该隐式值作为函数的参数,这里面其实没有涉及到隐式转换,本节将演示如何利用隐式参数进行隐式转换,下面的代码给定的是一个普通的比较函数: object ImplicitP

Scala入门到精通——第十八节 隐式转换与隐式参数（一）

本节主要内容 隐式转换简介 隐式转换函数 隐式转换规则 隐式参数 1. 隐式转换简介 在scala语言当中,隐式转换是一项强大的程序语言功能,它不仅能够简化程序设计,也能够使程序具有很强的灵活性.要想更进一步地掌握scala语言,了解其隐式转换的作用与原理是很有必要的,否则很难得以应手地处理日常开发中的问题. 在scala语言中,隐式转换是无处不在的,只不过scala语言为我们隐藏了相应的细节,例如scala中的类继承层次结构中: 它们存在固有的隐式转换,不需要人工进行干预,例如Float在必要

Oracle中的数据类型隐式转换(implicit conversion)

和其他的关系型数据库一样,oracle中也能进行一些隐式的数据转换,这对我们写SQL语句有非常用,我们可以不必麻烦地手动转化很多类型的字符.虽然前面我们介绍了一些使用例如to_char,to_date的函数进行强制转换的方法,但是隐式转换也还是不错的. Oracle可以隐式地进行一些变量类别之间转化,例如从字符串转换到数值,看下面的例子. SQL> select ename,sal from emp where sal = '1100′;   ENAME SAL ------– ---- SMI

SQL Server中提前找到隐式转换提升性能的办法

原文:SQL Server中提前找到隐式转换提升性能的办法     http://www.cnblogs.com/shanksgao/p/4254942.html 高兄这篇文章很好的谈论了由于数据隐式转换造成执行计划不准确,从而造成了死锁.那如果在事情出现之前发现了这类潜在的风险岂不是更好?     那么我们来看一个简单的例子,如代码清单1所示.   1: SELECT * 2: FROM HumanResources.Employee 3: WHERE NationalIDNumber = 2

Oracle的隐式转换

都说Oracle存在NUMBER和VARCHAR2类型的隐式转换,严格意义上需要避免,但为何需要避免,从下面的实验进行验证. 1. 创建测试表和索引 create table tn (id number, name varchar2(1)); create index idx_tn on tn (id); create index idx_tn on tn (name); 分别对NUMBER类型的id字段,VARCHAR2类型的name字段创建索引. 2. 查看VARCHAR2->NUMBER的