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

單表,全局表

會(huì)轉(zhuǎn)發(fā)sql

對(duì)于分片表

生成的sql模板會(huì)帶有for update語句,在涉及多個(gè)存儲(chǔ)節(jié)點(diǎn)的時(shí)候,sql執(zhí)行的鎖的范圍比所需單節(jié)點(diǎn)sql的大,所以盡量編寫查詢單節(jié)點(diǎn)的sql,一般就是select ...from where ......for update

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

錯(cuò)誤

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的引用的無聚合函數(shù)的字段需要使用any_value
order by必須引用select item中存在的字段
子查詢需要帶有別名
project Item不支持相同的字段名
limit,offset不能超過2147483647且大于等于0