专栏首页bisal的个人杂货铺Oracle、SQL Server和MySQL的隐式转换异同

Oracle、SQL Server和MySQL的隐式转换异同

这次的组内分享,选择了在不同数据库中的隐式转换这个话题。隐式转换是个老生常谈的问题了,不同的数据库,隐式转换的影响因素有所不同,我们通过一些例子来看一下。但是问题来了,如何避免隐式转换带来的负面影响?一方面是编程习惯的问题,另一方面就需要一些人肉/自动化的手段主动发现问题,如果两者都没有,就只能被动等着出问题,再找解决方案了。

1. Oracle的隐式转换

隐式转换的历史文章,

如何找到隐式转换的SQL?

从隐式转换案例,来挖掘开发人员的技能提升

浅谈显式转换和隐式转换

隐式转换的案例场景

一次有意思的错选执行计划问题定位

如下是官方文档,对显式转换和隐式转换的介绍,顾名思义,显式转换就是人为指定数据类型的转换关系,隐式转换则是数据库自动进行的类型转换,推荐前者,因为隐式转换,可能带来一些风险和隐患,例如隐式转换列不能用索引、隐式转换还受到系统参数、不同数据库版本实现功能差异的影响,

P.S. 

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/Data-Type-Comparison-Rules.html#GUID-98BE3A78-6E33-4181-B5CB-D96FD9DC1694

这是显式转换的矩阵,

这是隐式转换的矩阵,

这是常用到的字符串类型之间的转换矩阵,

创建测试表,

SQL> desc t0
Name          Null?    Type
------------- -------- -----------------
ID                     VARCHAR2(1000)


SQL> select count(*) from t0;
  COUNT(*)
----------
     97112


SQL> create index idx_t0_01 on t0(id);
Index created.

测试场景1

构造where varchar2=number,

SQL> var x number;
SQL> exec :x := 1;
PL/SQL procedure successfully completed.


SQL> select * from t0 where id = :x
no rows selected

未使用索引,而是用了全表扫,

原因就是谓词条件显示对左值用了TO_NUMBER()函数,

测试场景2

构造where varchar2=varchar2,

SQL> var z varchar2(1000);
SQL> exec :z := 'a';
PL/SQL procedure successfully completed.


SQL> select * from t0 where id = :z;
no rows selected

此时选择了索引,

这是一些在Oracle中,常见的隐式转换,各位在开发过程中务必注意,

where varchar2=number      ->  where to_number(varchar2)=number where varchar2=nvarchar2  ->  where sys_op_c2c(varchar2)=nvarchar2 where date=timestamp         ->  where to_timestamp(date)=timestamp

2. SQL Server的隐式转换

这是官网给出的数据类型转换矩阵,

P.S.

https://docs.microsoft.com/zh-cn/sql/t-sql/data-types/data-type-conversion-database-engine?view=sql-server-ver15

见识一下SQL Server隐式转换处理的不同》中介绍了一种SQL Server隐式转换的案例,和Oracle不同的是,SQL Server的隐式转换,还可能和排序规则相关。

测试场景1

创建一个SQL_Latin1_General_CP1_CI_AS排序规则的数据库,测试表如下,一个字段是varchar,一个字段是nvarchar,都创建了索引,

create table test(c1 nvarchar(200), c2 varchar(200));
insert into test(c1,c2) select cast(a.name as nvarchar(200)), a.name from master.dbo.spt_values a where a.number<10000;
create nonclustered index idx_test_01 on test(c1);
create nonclustered index idx_test_02 on test(c2);

(1) 构造where nvarchar=varchar,

select * from test where c1='a';

此时选择了Index Seek,再回表的操作,

(2) 构造where varchar=nvarchar,

select * from test where c2=N'a';

我们看到执行计划中提醒表达式列出现了类型转换,这会影响执行计划选择“SeekPlan”,执行了CONVERT_IMPLICIT函数的列就是左值c2,强制转换为nvarchar,“SeekPlan”的执行计划,我理解就是Oracle中的Index Unique Scan或Index Range Scan,而且当前确实选择了全表扫描,Table Scan,这就是隐式转换,导致不能使用索引的场景,

测试场景2

创建一个Latin1_General_CP1_CI_AS排序规则的数据库,和场景1相同,测试表如下,一个字段是varchar,一个字段是nvarchar,都创建了索引,

create table test(c1 nvarchar(200), c2 varchar(200));
insert into test(c1,c2) select cast(a.name as nvarchar(200)), a.name from master.dbo.spt_values a where a.number<10000;
create nonclustered index idx_test_01 on test(c1);
create nonclustered index idx_test_02 on test(c2);

(1) 构造where nvarchar=varchar,

select * from test where c1='a';

效果和场景1是相同的,此时选择了Index Seek,再回表的操作,

(2) 构造where varchar=nvarchar,

select * from test where c2=N'a';

这时就可以看出一些不同了,场景1中相同语句,因为隐式转换,导致用了Table Scan,而此处,虽然谓词提示CONVERT_IMPLICIT(c2),但未作为Warning,而且执行计划还是使用的Index Seek,路径上和场景1稍有不同,

Jonathan Kehayias在这篇文章中,提到了SQL_Latin1_General_CP1_CI_AS和Latin1_General_CP1_CI_AS这两种排序规则不同数据类型的转换关系。

P.S.

https://www.sqlskills.com/blogs/jonathan/implicit-conversions-that-cause-index-scans/

(1) SQL_Latin_General_CP1_CI_AS排序规则

varchar到nvarchar的隐式转换,是黄色的,意思是Causes Scan,即忽略索引,

(2) Latin_General_CI_AS排序规则

varchar到nvarchar的隐式转换,是绿色的,允许用Seek,

由此看出,SQL Server中不同的排序规则对隐式转换的影响可能是不同的,但是SQL Server中有非常多的排序规则,这就比较尴尬了,难道你能列举出所有排序规则对应的隐式转换的影响?

3. MySQL的隐式转换

MySQL的官方文档,同样强调了“For comparisons of a string column with a number, MySQL cannot use an index on the column to look up the value quickly.”,

P.S.

https://dev.mysql.com/doc/refman/5.7/en/type-conversion.html?spm=5176.100239.blogcont47339.5.1FTben

译文如下,

1. 两个参数至少有一个是 NULL 时,比较的结果也是 NULL,例外是使用 <=> 对两个 NULL 做比较时会返回 1,这两种情况都不需要做类型转换。

2. 两个参数都是字符串,会按照字符串来比较,不做类型转换。

3. 两个参数都是整数,按照整数来比较,不做类型转换。

4. 十六进制的值和非数字做比较时,会被当做二进制串。

5. 有一个参数是 TIMESTAMP 或 DATETIME,并且另外一个参数是常量,常量会被转换为 timestamp。

6. 有一个参数是 decimal 类型,如果另外一个参数是 decimal 或者整数,会将整数转换为 decimal 后进行比较,如果另外一个参数是浮点数,则会把 decimal 转换为浮点数进行比较。

7. 所有其他情况下,两个参数都会被转换为浮点数再进行比较。

数值型和字符串型之间的隐式转换,可以参考如下,

MySQL中隐式转换,还可能和字符集校对规则相关,

CREATE TABLE test1 (
  ID varchar(64) COLLATE utf8_bin NOT NULL,
  TASK_ID varchar(64) COLLATE utf8_bin DEFAULT NULL,
  TEXT varchar(4000) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


CREATE TABLE test2 (
  ID int not null auto_increment,
  TASK_ID varchar(64) DEFAULT NULL,
  TEXT varchar(4000) DEFAULT NULL,
  PRIMARY KEY (ID),
  unique key unique_test2 (task_id) using btree
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如下例子中,字符集校对规则有所区别,

explain select * from test1, test2 where test1.task_id=test2.task_id;

没使用上索引,

MySQL源代码中,能看到确实用到了字符集校对规则,

但是能在SQL层面指定字符集校对规则,

explain select * from test1, test2 where test1.task_id COLLATE utf8_general_ci=test2.task_id;

此时就可以用到索引了,

MySQL的隐式转换,还和字符集相关,

CREATE TABLE test3 (
  ID int not null auto_increment,
  TASK_ID varchar(64) DEFAULT NULL,
  TEXT varchar(4000) DEFAULT NULL,
  PRIMARY KEY (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


CREATE TABLE test4 (
  ID int not null auto_increment,
  TASK_ID varchar(64) DEFAULT NULL,
  TEXT varchar(4000) DEFAULT NULL,
  PRIMARY KEY (ID),
  unique key unique_test2 (task_id) using btree
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

确实索引不可用,

explain select * from test3, test4 where test3.task_id=test4.task_id;

但是不太清楚,能否在SQL层面指定字符集解决这个问题?

上面说了这么多,其实我们也都知道隐式转换的风险,而且不同数据库,隐式转换的影响因素还不多,更增加了难度,与其冒着风险,找各种的补救,不如从源头控制,避免隐式转换,对一些常规类型(字符串、数值、日期等),结合业务需求,定义合适的字段类型,程序中的变量、SQL写法等,与定义类型保持一致,就可以解决大部分隐式转换的问题。退而求其次,如果不能做到规范的设计和开发,至少在开发测试的阶段,通过工具或人肉,检索下当前系统中用了全表扫描的语句,再根据字段是否存在索引、where条件两侧的数据类型等,判断是否因为书写不当造成了隐式转换。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 见识一下SQL Server隐式转换处理的不同

    隐式转换(Implicit Conversion)就像他的名字一样,是个隐秘、不容易被发现的问题,但归根结底,还是设计开发中未遵守相关的规范,或者说是不良的设计...

    bisal
  • 迁移到MySQL的语法转换工具初步设计

    迁移到MySQL有很多原因,在基础准备工作中,源库的建表语句到MySQL建表语句的转换,不仅涉及到数据库间的语法,数据类型等差异,同时在SQL开发规范方向也有一...

    jeanron100
  • MySQL和Oracle中的隐式转换(r6笔记第45天)

    今天在处理一个问题的时候,需要根据其他部门提供的sql语句对一个表中的数据进行了筛查。 语句类似下面的形式 > SELECT MAX_LEVEL,LOGOUT_...

    jeanron100
  • SQL SERVER 隐士转换,你不仁,休怪他无义?

    SQL SERVER 在数据库的里面一直是以“绵羊”的身份出现,不如 ORACLE 的尊贵,也不如POSTGRESQL 的 犀利, 更没有MYSQL 的人气。但...

    AustinDatabases
  • Java List集合转数组的两种重载方法

    ArrayList提供了将List转为数组的简单方法toArray,他有两个重载的方法,

    bisal
  • 一些关系数据库的架构总结

    ORACLE数据库既能跑OLTP业务,也能跑OLAP业务,能力是商业数据库中数一数二的。支持IBM小机和x86 PC服务器,支持多种OS。同时有多种数据库架构方...

    用户1278550
  • vcenter内嵌和外链数据库的解析

    还有就是VCNEGER是部署在windows系统上的,后面一般是SQL SERVER 数据库。sql2008 R2可以用于小型部署(最多5个主机和 50 个虚拟...

    孙杰
  • 分享我常用的5个免费的在线 SQL 数据库环境,简直太方便了!

    blog.csdn.net/horses/article/details/108603935

    好好学java
  • 数据库中间件

    作者:[美]威廉·肯尼迪(William Kennedy)布赖恩·克特森(Brian

    李海彬
  • 数据库相关中间件介绍

    https://www.cnblogs.com/grefr/p/6087942.html#top

    二狗不要跑
  • 从Java的类型转换看MySQL和Oracle中的隐式转换(二)(r6笔记第68天)

    说起数据类型转换,在开发中如此,在数据库中也是如此,之前简单对比过MySQL和Oracle的数据类型转换情况,可以参见MySQL和Oracle中的隐式转换 ht...

    jeanron100
  • 「3306π」沪江:从 SQL Server 到 MySQL (一):异构数据库迁移

    本系列文章就是向大家介绍, 从 SQL Server 迁移到 MySQL 所面临的问题和我们的解决方案。

    田帅萌
  • MySQL数据库(良心资料)

    我们所说的数据库泛指“关系型数据库管理系统(RDBMS-Relational database management system)”,即“数据库服务器”。

    阮键
  • 「3306π」沪江从 SQL Server 到 MySQL(二):在线迁移,空中换发动机

    在上篇文章 从 SQL Server 到 MySQL (一):异构数据库迁移 中,我们给大家介绍了从 SQL Server 到 MySQL 异构数据库迁移的基本...

    田帅萌
  • Oracle 数据库、Microsoft SQL Server、MySQL 数据库三种常见数据库的区别深度剖析

    Oracle 数据库、Microsoft SQL Server、MySQL 数据库是我们在项目开发过程中最为常见的三种关系型数据库。下面我们分别从不同的角度对这...

    白鹿第一帅
  • 数据库技术发展与非关系型数据库NoSQL:

    近几年来,NoSQL运动如火如荼,不断有业内的巨头加入阵营,发布和推广NoSQL的相关产品,开拓应用场景。传统关系型数据库软件厂商也不甘寂寞,新产品、新版本、新...

    Enjoy233
  • 一文告诉你全世界最顶级的开发者都在使用什么数据库

    作为一名IT行业从业者,其实从去年已经隐隐约约感觉到数据库的有变化,只是没有想到变得这么快。今年的一些事情实实在在地给了某些数据库重击,如果以前去某数据库还是喊...

    数据和云
  • 一条垃圾SQL,把 64 核 CPU 快跑崩了!

    其实就是根据 XX_NO 查询一 条数据,然后查询条件和字段数据类型不一致,结果隐式转换导致索引失效而全表扫描……

    Java技术栈
  • Oracle和MySQL的高可用方案对比(一)

    关于Oracle和MySQL的高可用方案,其实一直想要总结了,就会分为几个系列来简单说说。通过这样的对比,会对两种数据库架构设计上的细节差异有一个基本的认识。O...

    jeanron100

扫码关注云+社区

领取腾讯云代金券