人大金仓 金仓数据库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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值