人大金仓 金仓数据库KingbaseES 新兼容—— Limit/Order By For Update/Delete

金仓数据库KingbaseES 新兼容—— Limit/Order By For Update/Delete

关键字：

KingbaseES、DML、limit、order by

1.什么是limit/order by？

在关系型数据库系统中，我们经常遇到如下场景：通过select语句，在一张数据表中，查询到了满足条件的数据，此时我们希望按照一定的顺序重新组织这些数据，同时又希望展示这些数据的部分条目，这就需要在select语句中用到order by和limit两个子句，分别实现排序和限制数据条目的作用。那么，在update/delete操作中呢？我们希望达到更加灵活地完成批量数据的更新或者删除操作，为此我们实现了update/delete语句的order by和limit两个子句，完美兼容了MySQL的相关语法。

2.MySQL中update/delete的limit/order by

MySQL数据库文档中update语法说明：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

MySQL数据库文档中delete语法说明：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[PARTITION (partition_name [, partition_name] ...)]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

MySQL数据库文档中关于limit/order by的使用说明：

If the ORDER BY clause is specified, the rows are updated in the order that is specified. The LIMIT clause places a limit on the number of rows that can be updated. For multiple-table syntax, ORDER BY and LIMIT cannot be used. You can use LIMIT row_count to restrict the scope of the UPDATE. A LIMIT clause is a rows-matched restriction. The statement stops as soon as it has found row_count rows that satisfy the WHERE clause,whether or not they actually were changed.

简单概括来讲，MySQL的update语句中的limit/order by受如下规则约束：

1、如果指定了order by，表中行会按照指定顺序更新，limit语句限制更新的行数；

2、多表更新不支持limit/order by；

3、不管有没有更新，带有limit的update语句，会在找到符合条件的行数后停止。

此外，根据进一步调研结果，我们发现limit与order by子句还有如下应当注意的地方：

1、不支持limit m,n形式；

2、不支持limit…offset形式；

3、不支持limit后跟表达式的形式；

4、limit 子句要在order by后，否则会报语法错误；

5、order by后可接表达式或者函数。

在我们金仓数据库KingbaseES 产品中，对其进行功能性兼容设计，目前已经实现MySQL的这一功能特性。

3.金仓数据库KingbaseES的实现方式

update语法格式：

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
SET {[{ column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]] | [ ROW = record]}
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ LIMIT { count | ALL } ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
update语法示例：
update table set name = ‘KingbaseES’ where score = 100 order by score;
update table set name = ‘KingbaseES’ where score = 100 order by score desc;
update table set name = ‘KingbaseES’ where score = 100 order by score using >;
update table set name = ‘KingbaseES’ where score = 100 order by score limit 1;
delete语法格式：
[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]
[ USING using_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ] 
[ LIMIT { count | ALL } ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
delete语法示例：
delete from t1 where a = 1 order by a; 
delete from t1 where a = 1 order by a desc; 
delete from t1 where a = 1 order by a using < ; 
delete from t1 where a = 1 order by a nulls first limit 1;

通过调整update/delete的执行计划，将sort和limit算子下推到modify table算子 之下先完成排序和选择限制的操作，最终对筛选出来的目标结果集进行更新。极大丰富了KingbaseES 的语法，让客户更加便捷的设计个性化命令，实现其想要的功能，数据更新操作更加完善。金仓数据库始终践行成就客户的核心价值观，坚持以奋斗者为本，为创业者搭台的价值导向，努力完善数据库产品，引领国产数据库越好又快向前发展，努力成为世界卓越的数据库产品与服务提供商。 更多信息，参见https://help.kingbase.com.cn/v8/index.html