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

分手后的思念是犯贱 2022-08-31 00:51 188阅读 0赞

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

**1. Oracle的隐式转换**

隐式转换的历史文章，

《[如何找到隐式转换的SQL？][SQL]》

《[从隐式转换案例，来挖掘开发人员的技能提升][Link 1]》

《[浅谈显式转换和隐式转换][Link 2]》

《[隐式转换的案例场景][Link 3]》

《[一次有意思的错选执行计划问题定位][Link 4]》

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

![87ae1145ab89e4ff5850ee262eb86ad6.png][]

P.S.

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

这是显式转换的矩阵，

![be4f1cfa8bfe8779cbf73cafe63d3794.png][]

![bd8a8a8a66ff195b6d90503adcf28235.png][]

这是隐式转换的矩阵，

![5499e9c11e9af786a9e9d98b7b75d4e8.png][]

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

![86f9c3e5b46fab3cc3b3984475a09eeb.png][]

创建测试表，

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

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

![a4d68b0f0f3bb0e1c2e711d2b10e6fcd.png][]

原因就是谓词条件显示对左值用了TO\_NUMBER()函数，

![7e4838efabe0895263c277462fb0a2f3.png][]

**测试场景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

此时选择了索引，

![229d3d3cf4e234fbf3de61ea14a31220.png][]

![6d83b85bd8c0525c08800972af03eb42.png][]

这是一些在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的隐式转换**

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

![b58fcc1329fc49ab18395ff1eb941ed2.png][]

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]》中介绍了一种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，再回表的操作，

![95ccd48fa23cf9432137beda7bc1ddbd.png][]

(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，这就是隐式转换，导致不能使用索引的场景，

![7f1cdaaacb1694a91ed8fa66be73f3b6.png][]

**测试场景2**

创建一个Latin1\_General\_CP1\_CI\_AS排序规则的数据库，和场景1相同，测试表如下，一个字段是varchar，一个字段是nvarchar，都创建了索引，

(1) 构造where nvarchar=varchar，

select * from test where c1='a';

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

![10541621dbaa44783080f3a47853cafd.png][]

(2) 构造where varchar=nvarchar，

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

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

![01a54795a624dd162b2c95cc9eb35c85.png][]

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，即忽略索引，

![82b297c2bcf63bbb54c7ebab23375361.png][]

(2) Latin\_General\_CI\_AS排序规则

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

![17a386c4bb970180a14f51468c6009d7.png][]

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

![b60939e505b2679da8fef563c0ccef0a.png][]

**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.”，

![7087b6f1ff624c517bd73b27481309e2.png][]

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. 所有其他情况下，两个参数都会被转换为浮点数再进行比较。

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

![6631d8bf651405d992a602a065964002.png][]

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;

没使用上索引，

![c012b9c24202d65f645cc819a2a0630a.png][]

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

![4e895b14264ab9b9ffcb3d333da9b7c9.png][]

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

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

此时就可以用到索引了，

![23bc089e6349409a1fe06a9dd80c6f89.png][]

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层面指定字符集解决这个问题？

![d6bb9e693020a83e42f59f973f0246fd.png][]

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

**近期更新的文章：**

《[JDK的版本号解惑][JDK]》

《[理财投资和个人投资的闲聊][Link 5]》

《[最近碰到的几个问题][Link 6]》

《[新增字段在数据块中的体现][Link 7]》

《[SQL Server的索引碎片][SQL Server 1]》

**文章分类和索引：**

《[公众号700篇文章分类和索引][700]》

[SQL]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a414ddf52d02b74d3e476d6c4daf1aa9ba6a5be958a4f19d9b880a0029b43895eebceb2f&idx=1&mid=2247492071&scene=21&sn=05655f3f6ec05fd54b94097552708259#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea815f9dddf6d68bc7e8f65937962917b00a5fbc0153890cf1070c022fa12c0c83c85eef25a5&idx=1&mid=2247490158&scene=21&sn=a71aa178975a38ecc1e6110f0d0dc274#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814a02ddf6c31494565b7add85e0ab7cd6497c06920c7f13b338363660a74ca7f2218c34cc&idx=1&mid=2247487473&scene=21&sn=3a1c9fee7d73e5fda1612d65166b7511#wechat_redirect
[Link 3]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea81485dddf6c14b7af06e84eb4115815828aa958a7b3c42283a1b8feb1cff3e25d7ea5837f1&idx=1&mid=2247486894&scene=21&sn=96754f2cdbd73086dc7a3126ec46fb69#wechat_redirect
[Link 4]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea814467ddf6cd711d512911eae87931a33577d9113fcb1be8433e9912ad7a53010c322c9914&idx=1&mid=2247483796&scene=21&sn=3bb50929387d42d1f3c34e6859ce5b8b#wechat_redirect
[87ae1145ab89e4ff5850ee262eb86ad6.png]: /images/20220829/a44ce35aa02b468396956c6e29f0fadb.png
[be4f1cfa8bfe8779cbf73cafe63d3794.png]: /images/20220829/1880fd45032444f3bc6d3e2d96039930.png
[bd8a8a8a66ff195b6d90503adcf28235.png]: /images/20220829/fac736ba05fb42d69f4c6351e829fb4b.png
[5499e9c11e9af786a9e9d98b7b75d4e8.png]: /images/20220829/7e138400cb824915ad84fdec0e3a1eb7.png
[86f9c3e5b46fab3cc3b3984475a09eeb.png]: /images/20220829/49cd5112e70843ed85892f4411c142c3.png
[a4d68b0f0f3bb0e1c2e711d2b10e6fcd.png]: /images/20220829/d6dfa9ed489f4a2895a9596957d97230.png
[7e4838efabe0895263c277462fb0a2f3.png]: /images/20220829/eef20677b7044130a7344679e6242db9.png
[229d3d3cf4e234fbf3de61ea14a31220.png]: /images/20220829/4bcb50c0ebf54902971fd1f681de3320.png
[6d83b85bd8c0525c08800972af03eb42.png]: /images/20220829/bdd8c266af894982bb1b9efc37078ea3.png
[b58fcc1329fc49ab18395ff1eb941ed2.png]: /images/20220829/4e24b4d61a8a473295f2ec6eea650107.png
[SQL Server]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a72cddf52e3ae3cc45ba92dd5083dd963358b1f07b7dc98348683252d4f4bc66c397ed1a&idx=1&mid=2247492319&scene=21&sn=0bf77c5d4db98818038f906fbe11a809#wechat_redirect
[95ccd48fa23cf9432137beda7bc1ddbd.png]: /images/20220829/20bf4fa8c78a4209942c69820221bdc3.png
[7f1cdaaacb1694a91ed8fa66be73f3b6.png]: /images/20220829/63fd60405219401a9d0ac7c2fe081c86.png
[10541621dbaa44783080f3a47853cafd.png]: /images/20220829/2f50b36ada1e4af9ad3c0d39153618e7.png
[01a54795a624dd162b2c95cc9eb35c85.png]: /images/20220829/00cdf26366a04938a4bdb559071de9ec.png
[82b297c2bcf63bbb54c7ebab23375361.png]: /images/20220829/475a6a14d8d34b1f8f5eef6b41fddd05.png
[17a386c4bb970180a14f51468c6009d7.png]: /images/20220829/4522f239f8f0439297c0b49a6b1169ee.png
[b60939e505b2679da8fef563c0ccef0a.png]: /images/20220829/b57203d7ea59408c8862abe749cc65b5.png
[7087b6f1ff624c517bd73b27481309e2.png]: /images/20220829/d26f4cebc1af4c9088927ef013cdbc10.png
[6631d8bf651405d992a602a065964002.png]: /images/20220829/84e66a406a334b50a377cc817fc5e6d3.png
[c012b9c24202d65f645cc819a2a0630a.png]: /images/20220829/a35b6308af724d98aa739083d5c2592d.png
[4e895b14264ab9b9ffcb3d333da9b7c9.png]: /images/20220829/afc1c961c7414e6bbc17fc8fa87de966.png
[23bc089e6349409a1fe06a9dd80c6f89.png]: /images/20220829/abdfd465ad07468b9e246aae42f342b0.png
[d6bb9e693020a83e42f59f973f0246fd.png]: /images/20220829/e36fd4d47d4b41c0957df64ba329e185.png
[JDK]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a159ddf5284f2f7b440676c128aa9d342e3331ecaf5bd3eb4f700e80080b0798c3ac4009&idx=1&mid=2247492778&scene=21&sn=211b4ccd0d6b56da233114235b787b73#wechat_redirect
[Link 5]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a167ddf52871caccacb62f36dededb9b3c0b4798abd6549f0a12a8542b407450803d464c&idx=1&mid=2247492756&scene=21&sn=7190358a7b14dbb8d78438a44031266a#wechat_redirect
[Link 6]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a172ddf52864b3e68f23f395a9e80624902ffcb4f14f577bb53acc558d4812c00cead5ee&idx=1&mid=2247492737&scene=21&sn=c2ffecf7b90708977fb04d5793da8a93#wechat_redirect
[Link 7]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a190ddf52886b9393435abb5f4941b2e7b4ab072d4bd93f690cedcac6b814c61a5a728e5&idx=1&mid=2247492707&scene=21&sn=59107188dea8535a6f7d131bfffe3787#wechat_redirect
[SQL Server 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a1bcddf528aa6ad491b5baa8de865f6c9673365d6460ef3e6be490fda8e7b8c1c1eda468&idx=1&mid=2247492687&scene=21&sn=e7068f39a0cfd1425104882684f70273#wechat_redirect
[700]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a5dfddf52cc949a5b8b316ee905927ff399713b34aec7d806c656e8ada09bfb562c039ca&idx=1&mid=2247491628&scene=21&sn=83bd320bd28da84501fada2e8d515e77#wechat_redirect