Update with result set implementation
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.1 |
Invalid
|
Undecided
|
Unassigned | |||
5.5 |
Triaged
|
Wishlist
|
Unassigned | |||
5.6 |
Triaged
|
Wishlist
|
Unassigned | |||
5.7 |
Triaged
|
Wishlist
|
Unassigned |
Bug Description
Oracle and PostgreSQL has the returning syntax for DML, which can return the result set immediately after the update, this feature can save network communications between client and mysql server, one SQL parse for the following SELECT statement.
MariaDB has now implementd the DELETE...RETURNING feature
http://
and other RETURNINGs are in their plan:
https:/
one year ago I have implemented this feature based on Percona server 5.5.18: combine UPDATE+SELECT into single statement to gain performance improvement.
~~~~~~~
mysql> show create table tt\G
*******
Table: tt
Create Table: CREATE TABLE `tt` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB
1 row in set (0.00 sec)
mysql> select * from tt;
+----+-
| id | a | b | c |
+----+-
| 1 | 1 | 2 | 3 |
| 2 | 2 | 3 | 4 |
| 3 | 3 | 4 | 5 |
| 4 | 4 | 5 | 6 |
| 5 | 5 | 6 | 7 |
| 6 | 6 | 7 | 8 |
| 7 | 6 | 1 | 9 |
+----+-
7 rows in set (0.00 sec)
mysql> select NOW() as time, a+5 as a5, b from update tt set a=7, b=b+7 where id=7;
+------
| time | a5 | b |
+------
| 2013-05-24 18:08:54 | 12 | 8 |
+------
1 row in set (0.01 sec)
mysql> select * from tt;
+----+-
| id | a | b | c |
+----+-
| 1 | 1 | 2 | 3 |
| 2 | 2 | 3 | 4 |
| 3 | 3 | 4 | 5 |
| 4 | 4 | 5 | 6 |
| 5 | 5 | 6 | 7 |
| 6 | 6 | 7 | 8 |
| 7 | 7 | 8 | 9 |
+----+-
7 rows in set (0.00 sec)
~~~~~~~
the syntax I added is not like Oracel/PostgreSQL's "UPDATE.
SELECT
select_expr [, select_expr ...]
FROM UPDATE
[LOW_PRIORITY] [IGNORE]
[TARGET_AFFECT_ROW num]
tbl_name
SET col_name1=
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
the reason I implemented "the "SELECT ... FROM UPDATE..." is that returning result set from an update statement is incompatible with the mysql connector, which think statements that begin with DELETE/
see the function checkForDml in StatementImpl:
I will attached the patch, hope someone can review it for me.
Thanks!
this patch is based on our MySQL branch(which is actually based on Percona Server 5.5.18)