SQL支持详细列表
由于SQL语法灵活复杂,分布式数据库和单机数据库的查询场景又不完全相同,难免有和单机数据库不兼容的SQL出现。
本文详细罗列出已明确可支持的SQL种类以及已明确不支持的SQL种类,尽量让使用者避免踩坑。
其中必然有未涉及到的SQL欢迎补充,未支持的SQL也尽量会在未来的版本中支持。
全局不支持项
有限支持子查询
子查询支持详情请参考分页及子查询。
不支持包含冗余括号的SQL
不支持OR
不支持CASE WHEN
支持的SQL
DQL
SELECT主语句
SELECT select_expr [, select_expr ...] FROM table_reference [, table_reference ...]
[WHERE where_condition]
[GROUP BY {col_name | position} [ASC | DESC]]
[ORDER BY {col_name | position} [ASC | DESC], ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
select_expr
* |
COLUMN_NAME [AS] [alias] |
(MAX | MIN | SUM | AVG)(COLUMN_NAME | alias) [AS] [alias] |
COUNT(* | COLUMN_NAME | alias) [AS] [alias]
table_reference
tbl_name [AS] alias] [index_hint_list] |
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 |
是 |
|
CREATE INDEX idx_name ON tbl_name |
是 |
|
DROP INDEX idx_name ON tbl_name |
是 |
|
DROP INDEX idx_name |
是 |
tableRule中配置logic-index |
不支持的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=?) |