MySQL (select_paren) union_order_or_limit 行为

MySQL源码版本5.6.2

MySQL Syntax(sql_yacc.yy)

select:      select_initselect_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_clauselimit_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];