开发者社区> 勉仁> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MySQL (select_paren) union_order_or_limit 行为

简介: MySQL语法 (select_paren) union_order_or_limt行为
+关注继续查看

MySQL (select_paren) union_order_or_limit 行为

MySQL源码版本5.6.2

MySQL Syntax(sql_yacc.yy)

select:
      select_init

select_init:
    SELECT_SYM select_init2
    | '(' select_paren ')' union_opt
 ;
union_opt:
    /* Empty */ { $$= 0; }
      | union_list { $$= 1; }
      | union_order_or_limit { $$= 1; }
  ;
union_order_or_limit:
    order_or_limit:

order_or_limit:
    order_clause opt_limit_clause_init
    | limit_clause

limit_clause:
    LIMIT limit_options

处理逻辑

LIMIT

limit_options以limit_option ',' limit_option为例

      | limit_option ',' limit_option
      {
        SELECT_LEX *sel= Select;//Select表示current_select
        sel->select_limit= $3;
        sel->offset_limit= $1;
        sel->explicit_limit= 1;
      }

由该逻辑可以看出:

(select * from t1 [order by x | limit num]) limit off_x, count_y;//current_select不变

相当于

select * from t1 [order by x] limit off_x, count_y;

Order by

order_clause:
      ORDER_SYM BY
      {
        LEX *lex=Lex;
        SELECT_LEX *sel= lex->current_select;
        SELECT_LEX_UNIT *unit= sel-> master_unit();
        if (sel->linkage != GLOBAL_OPTIONS_TYPE &&
            sel->olap != UNSPECIFIED_OLAP_TYPE &&
            (sel->linkage != UNION_TYPE || sel->braces))
        {
          my_error(ER_WRONG_USAGE, MYF(0),
                   "CUBE/ROLLUP", "ORDER BY");
          MYSQL_YYABORT;
        }
        if (lex->sql_command != SQLCOM_ALTER_TABLE && !unit->fake_select_lex)
        {
          /*
            A query of the of the form (SELECT ...) ORDER BY order_list is
            executed in the same way as the query
            SELECT ... ORDER BY order_list
            unless the SELECT construct contains ORDER BY or LIMIT clauses.
            Otherwise we create a fake SELECT_LEX if it has not been created
            yet.
          */
          SELECT_LEX *first_sl= unit->first_select();
          if (!unit->is_union() &&
              (first_sl->order_list.elements ||
               first_sl->select_limit) &&
              unit->add_fake_select_lex(lex->thd))
            MYSQL_YYABORT;
        }
      }
      order_list
    ;

从上面可以看出当select_paren中没有order和limit的时候,current_select不会改变,有以下等价方式:

(select no_order_or_limit) order by xx [limit x];

等价于

select no_order_or_limit order by xx limit x;

当存在order或者limit的时候,MySQL会创建GLOBAL_OPTIONS_TYPE的fake_select_lex,这个fake_select_lex作为OPTIONS存在。

同时将current_select指向fake_select_lex。

  fake_select_lex->linkage= GLOBAL_OPTIONS_TYPE;
  if (!is_union())
  {
    /* 
      This works only for 
      (SELECT ... ORDER BY list [LIMIT n]) ORDER BY order_list [LIMIT m],
      (SELECT ... LIMIT n) ORDER BY order_list [LIMIT m]
      just before the parser starts processing order_list
    */ 
    global_parameters= fake_select_lex;
    fake_select_lex->no_table_names_allowed= 1;
    thd_arg->lex->current_select= fake_select_lex;
  }

因此当select_paren中存在order或者limit的时候,curren_select会指向fake_select,添加order by[limit]。我们可以认为其等价方式是:

(select xxx  order by xx limit x) order by yy[limit y];

等价于

select * from (select xxx order by xx limit x) ta order by yy limit y;

实验结果


mysql> create table t1(c1 int primary key);

mysql> insert into t1 values(1), (2),(3), (4),(5);

//limit option测试   
mysql> (select * from t1 limit 1) limit 5;
+----+
| c1 |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
 //等价:select * from t1 limit 5;

mysql> (select * from t1 order by c1 desc limit 1) limit 4;
+----+
| c1 |
+----+
|  5 |
|  4 |
|  3 |
|  2 |
+----+
//等价:select * from t1 order by c1 desc limit 4;

//order by[limit] option测试
mysql> (select * from t1) order by c1 desc limit 2;
+----+
| c1 |
+----+
|  5 |
|  4 |
+----+
//等价:select * from t1 order by c1 desc limit 2;

mysql> create table t(c1 int);

mysql> insert into t values(1), (1), (2), (2);

mysql> (select * from t group by(c1)) order by c1 limit 3;
+------+
| c1   |
+------+
|1 |
|2 |
+------+
//等价于select * from t group by (c1) order by c1 limit 3;

mysql> (select * from t1 limit 3) order by c1 desc limit 1;
+----+
| c1 |
+----+
|  3 |
+----+

//等价于:select * from (select * from t1 limit 3) ta order by c1 desc limit 1;

mysql> (select * from t1 order by c1 desc limit 2) order by c1;
+----+
| c1 |
+----+
|  4 |
|  5 |
+----+
//等价于:select * from (select * from t1 order by c1 desc limit 2) ta order by c1;

mysql> (select * from t1 order by c1 desc limit 2) order by c1 limit 1;
+----+
| c1 |
+----+
|  4 |
+----+   
//等价于:select * from (select * from t1 order by c1 desc limit 2) ta order by c1 limit 1;

mysql> insert into t values(3, 3);
mysql> (select * from t group by (c1) order by c1 desc limit 2) order by c1 limit 1;
+------+
| c1   |
+------+
|2 |
+------+
//等价于: select * from (select * from t group by (c1) order by c1 desc limit 2) ta order by c1 limit 1;

总结

MySQL语法 (select_paren) union_order_or_limt等价方式如下:

1、limit as option

(select xxx) limit yy;

等价于:

select xxx_no_limit limit yy;

2、order by [limit] as option

2.1、select_paren without order or limit

(select no_order_or_limit) order by yyy [limit y];

等价于:

select no_order_or_limit order by yyy [limit y];

2.2、select_paren with order or limit

(select xxx [order by xx | limit x]) order by yyy [limit y];

等价于:

select * from (select xxx [order by xx | limit x]) ta order by yyy [limit y];

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

相关文章
MySQL之select、distinct、limit使用
MySQL之select、distinct、limit使用
0 0
MySQL 里面的Where 和Having和Count 和distinct和Group By对比
mysql> select accid as uid,date(datetime) AS datetime from game.logLogin GROUP BY accid HAVING datetime='2013-8-20'; +---------+------------+ | ui...
431 0
mysql 5.7 中order by 和GROUP BY 一起使用 order by 不生效
在mysql5.7中,如果不加limit,系统会把order by优化掉。https://dev.mysql.com/doc/refman/5.7/en/semi-joins.html select from (select from zz.
2435 0
MySQL:The used SELECT statements have a different number of columns
执行SQL报错:The used SELECT statements have a different number of columns
0 0
explain 之 select-type 和 table 介绍|学习笔记
快速学习 explain 之 select-type 和 table 介绍
0 0
mysql的FIND_IN_SET group_concat 函数
mysql的FIND_IN_SET group_concat 函数
0 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
DRDS到ADS...[饮冰].1505740086.pdf
立即下载
对 2000 多亿条数据做一次 group by 需要多久?
立即下载
对2000多亿条数据做一次Group By 需要多久
立即下载