Oracle和MySQL竟然可以这么写这样的SQL?(r12笔记第99天)-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Oracle和MySQL竟然可以这么写这样的SQL?(r12笔记第99天)

简介: 今天看到Franck Pachot? 发了一个Twitter,意思是Oracle里的SQL还能这么写。猛一看确实让人有些意外。 禁不住诱惑,自己也尝试了一番。

今天看到发了一个Twitter,意思是Oracle里的SQL还能这么写。猛一看确实让人有些意外。

禁不住诱惑,自己也尝试了一番。我现在12cR2的环境中测试了一下。

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

尝试上面的步骤,先来看看dual表。

SQL> select count(*)from dual;
  COUNT(*)
----------
         1

开始测试,这样看起来不规范的SQL照样能够正常解析

SQL> select +count(*) from dual;
 +COUNT(*)
----------
         1

SQL> select -count(*)from dual;
 -COUNT(*)
----------
        -1SQL> select +dummy from dual;
DU
--
X
SQL> select -dummy from dual;
select -dummy from dual
        *
ERROR at line 1:
ORA-01722: invalid number如果对dual表抱有一丝幻想,那么我们换一个数据字典cat

SQL> select count(*)from cat;
  COUNT(*)
----------
      8113

SQL> select +count(*)from cat;
 +COUNT(*)
----------
      8113SQL> select -count(*)from cat;
 -COUNT(*)
----------
     -8113看起来依旧可以,我们换一个堆表。

SQL> create table test_data as select *from user_objects;
Table created.

SQL> select count(*)from test_data;
  COUNT(*)
----------
     51907SQL> select +count(*)from test_data;
 +COUNT(*)
----------
     51907SQL> select -count(*)from test_data;
 -COUNT(*)
----------
    -51907换成object_id字段 

SQL> select -object_id from test_data where rownum<3;
-OBJECT_ID
----------
       -16
       -20SQL> select +object_id from test_data where rownum<3;
 OBJECT_ID
----------
        16
        20  对此如果还有一些疑问,我们可以使用10053来解析一下,看看优化器是如何处理的。       
SQL> alter session set events '10053 trace name context forever,level 12';
SQL>  select -object_id from test_data where rownum<3;
-OBJECT_ID
----------
       -16
       -20
SQL> alter session set events '10053 trace name context off';    
查询转换后的SQL如下:

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT (-"TEST_DATA"."OBJECT_ID") "-OBJECT_ID" FROM "SYS"."TEST_DATA" "TEST_DATA" WHERE ROWNUM<3
*************************

而如果是字符型数据,则使用这种方式的减号就不可行了。

SQL> select  -object_name from test_data where rownum<2;
select  -object_name from test_data where rownum<2
         *
ERROR at line 1:
ORA-01722: invalid number如果你以为是12c里面的一些新特性这类的,其实在10g也是类似的结果。

MySQL篇

如果你认为这是Oracle优化器的强大,其实不然,我们看看MySQL里的表现,假设表为test_tab.

> select -count(*) from test_tab;
+-----------+
| -count(*) |
+-----------+
|   -548650 |
+-----------+
1 row in set (0.39 sec)

使用运算符,可以看到也是支持的。

> select +count(*)from test_tab;
+-----------+
| +count(*) |
+-----------+
|    548650 |
+-----------+
1 row in set (0.39 sec)

如果查询两条数据,进行比对测试。

> select login_account from test_tab limit 2;
+-------------------------------+
| login_account                 |
+-------------------------------+
| 0000000180000000@test.com |
| 000000111000@test.com     |
+-------------------------------+
2 rows in set (0.00 sec)

我们继续使用运算符来处理。

> select -login_account from test_tab limit 2;
+----------------+
| -login_account |
+----------------+
|     -180000000 |
|        -111000 |
+----------------+
2 rows in set, 2 warnings (0.00 sec)

注意这里有两个警告,我们看看警告内容,原来内容都被处理过了。

> show warnings;
+---------+------+-------------------------------------------------------------------+
| Level   | Code | Message                                                           |
+---------+------+-------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '0000000180000000@test.com' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '000000111000@test.com'     |
+---------+------+-------------------------------------------------------------------+
2 rows in set (0.00 sec)如果我们手动换一种形式,写为0-xxx的形式,结果是一样的。

> select 0-login_account from test_tab limit 2;
+-----------------+
| 0-login_account |
+-----------------+
|      -180000000 |
|         -111000 |
+-----------------+
2 rows in set, 2 warnings (0.01 sec)

这里的结果其实涉及到sql_mode的设置,在此就不再展开了。
回到这个问题,上面的语句竟然可以解析,在优化器中是什么样的呢?可以使用explain extended的方式来解析,结果如下:
> explain extended select -count(*) from test_tab;
+----+-------------+----------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
| id | select_type | table    | type  | possible_keys | key                   | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | test_tab | index | NULL          | ind_tmp_login_account | 303     | NULL | 548474 |   100.00 | Using index |
+----+-------------+----------+-------+---------------+-----------------------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)解析的结果如下,可以看到原本的count(*)自动转换为了count(0),然后做了运算处理。

> show warnings;
+-------+------+----------------------------------------------------------+
| Level | Code | Message                                                  |
+-------+------+----------------------------------------------------------+
| Note  | 1003 | select -(count(0)) AS `-count(*)` from `test`.`test_tab` |
+-------+------+----------------------------------------------------------+
1 row in set (0.00 sec)


6820c716-3835-433b-bba1-a8db512e323c.jpg

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章