SQL支持详细列表

由于SQL语法灵活复杂，分布式数据库和单机数据库的查询场景又不完全相同，难免有和单机数据库不兼容的SQL出现。本文详细罗列出已明确可支持的SQL种类以及已明确不支持的SQL种类，尽量让使用者避免踩坑。其中必然有未涉及到的SQL欢迎补充，未支持的SQL也尽量会在未来的版本中支持。

全局不支持项

动态表

未配置逻辑表和真实表对应关系的真实表，称为动态表。凡是动态表且未在SQL或Hint中包含分片键的SQL均不支持。原因是未找到分片键则需全路由，但由于未配置逻辑表和真实表的对应关系，无法全路由。

有限支持子查询

子查询支持详情请参考分页及子查询。

不支持包含冗余括号的SQL

不支持OR

不支持CASE WHEN

支持的SQL

DQL

SELECT主语句

  1. SELECT select_expr [, select_expr ...] FROM table_reference [, table_reference ...]
  2. [WHERE where_condition] 
  3. [GROUP BY {col_name | position} [ASC | DESC]] 
  4. [ORDER BY {col_name | position} [ASC | DESC], ...] 
  5. [LIMIT {[offset,] row_count | row_count OFFSET offset}]

select_expr

  1. * | 
  2. COLUMN_NAME [AS] [alias] | 
  3. (MAX | MIN | SUM | AVG)(COLUMN_NAME | alias) [AS] [alias] | 
  4. COUNT(* | COLUMN_NAME | alias) [AS] [alias]

table_reference

  1. tbl_name [AS] alias] [index_hint_list] | 
  2. table_reference ([INNER] | {LEFT|RIGHT} [OUTER]) JOIN table_factor [JOIN ON conditional_expr | USING (column_list)] |

示例

DQL

SQL无条件支持必要条件
SELECT FROM tbl_name是
SELECT FROM tbl_name WHERE col1 = val1 ORDER BY col2 DESC LIMIT limit是
SELECT COUNT(*), SUM(col1), MIN(col1), MAX(col1), AVG(col1) FROM tbl_name WHERE col1 = val1是
SELECT COUNT(col1) FROM tbl_name WHERE col2 = val2 GROUP BY col1 ORDER BY col3 DESC LIMIT offset, limit是

DML

SQL无条件支持必要条件
INSERT INTO tbl_name (col1, col2,…) VALUES (val1, val2,….)否插入列需要包含分片键
INSERT INTO tbl_name VALUES (val1, val2,….)否通过Hint注入分片键
UPDATE tbl_name SET col1 = val1 WHERE col2 = val2是
DELETE FROM tbl_name WHERE col1 = val1是

DDL

SQL无条件支持必要条件
CREATE TABLE tbl_name (col1 int,…)是
ALTER TABLE tbl_name ADD col1 varchar(10)是
DROP TABLE tbl_name是
TRUNCATE TABLE tbl_name是

不支持的SQL

SQL
INSERT INTO tbl_name (col1, col2, …) VALUES (val1, val2,….), (val3, val4,….)
INSERT INTO tbl_name (col1, col2, …) SELECT col1, col2, … FROM tbl_name WHERE col3 = val3
INSERT INTO tbl_name SET col1 = val1
SELECT DISTINCT FROM tbl_name WHERE column1 = value1
SELECT FROM tbl_name WHERE column1 = value1 OR column1 = value2
SELECT COUNT(col1) as count_alias FROM tbl_name GROUP BY col1 HAVING count_alias > val1
SELECT FROM tbl_name1 UNION SELECT FROM tbl_name2
SELECT FROM tbl_name1 UNION ALL SELECT FROM tbl_name2
SELECT * FROM tbl_name1 WHERE (val1=?) AND (val1=?)