Mycat2 Sql兼容性 select

select兼容性

query:
select:
      SELECT [ ALL | DISTINCT ]
          { * | projectItem [, projectItem ]* }
      FROM tableExpression
      [ WHERE booleanExpression ]
      [ GROUP BY { groupItem [, groupItem ]* } ]
      [ HAVING booleanExpression ]
selectWithoutFrom:
      SELECT [ ALL | DISTINCT ]
          { * | projectItem [, projectItem ]* }
projectItem:
      expression [ [ AS ] columnAlias ]
  |   tableAlias . *
tableExpression:
      tableReference [, tableReference ]*
  |   tableExpression [ NATURAL ] [ ( LEFT | RIGHT | FULL ) [ OUTER ] ] JOIN tableExpression [ joinCondition ]
  |   tableExpression CROSS JOIN tableExpression
  |   tableExpression [ CROSS | OUTER ] APPLY tableExpression
joinCondition:
      ON booleanExpression
  |   USING '(' column [, column ]* ')'
tableReference:
      tablePrimary
      [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]
|tablePrimary:
      [ [ catalogName . ] schemaName . ] tableName
      '(' TABLE [ [ catalogName . ] schemaName . ] tableName ')'
|values:
      VALUES expression [, expression ]*
groupItem:
      expression
  |   '(' ')'
  |   '(' expression [, expression ]* ')'

  
SELECT UNION [ALL | DISTINCT] SELECT ...

when case语法

CASE case_value
    WHEN booleanExpression THEN expression
    [WHEN booleanExpression THEN expression] ...
    [ELSE expression]
END CASE

单表,全局表

会转发sql

对于分片表

生成的sql模板会带有for update语句,在涉及多个存储节点的时候,sql执行的锁的范围比所需单节点sql的大,所以尽量编写查询单节点的sql,一般就是select ...from where ......for update

1. 不支持select into outfile
2. 不支持select use/ignore index
3. 不支持STRAIGHT_JOIN和 NATURAL JOIN
4. 不支持有歧义的别名

错误

SELECT t.user_id FROM db1.travelrecord t GROUP BY id;
Expression 't.user_id' is not being grouped

修改成

SELECT any_value(t.user_id) FROM db1.travelrecord t GROUP BY id;

projectItem被group by的引用的无聚合函数的字段需要使用any_value
order by必须引用select item中存在的字段
子查询需要带有别名
project Item不支持相同的字段名
limit,offset不能超过2147483647且大于等于0