Updating multiple rows dating spear and jackson saws

That is to use a variable, put the update result in the variable, and then read out the variable in the same session. update likes set gmt_modified = now(), count = @cnt := count 1 where object_id = ?

; select @cnt as count; Because the Web application will involve the use of the connection pool when interacting with the database to get a connection before executing SQL statements, execute a great number of statements in the connection, and then return the connection to the connection pool, we basically do not have to worry about the case where the update and select statements are not in the same session as long as the code guarantees that the update and select statements are executed on the same connection.

Also, consider such a scenario: we have to update multiple fields of one or multiple row(s) of data in the relational database, and need to get the primary key of this batch of records updated in order to manage other related tables.

updating multiple rows-65

Of course, the back-end engineer is willing to implement the former. It is just to update gmt_modified and count using an update statement, and then return OK to have it done; otherwise, more work is required. How nice it will be if the back-end interface can return more data to the front-end server.

Why is business logic involved so gratuitously by increasing the number by one?

The object_id is the primary key of the object that can be liked, which is equivalent to an external key, but the data consistency of association tables will be guaranteed by the application logic, and it will not be aware of the database; the count field records the number of likes of this object.

-------------- ------------ ------ ----- --------- ---------------- | Field | Type | Null | Key | Default | Extra | -------------- ------------ ------ ----- --------- ---------------- | id | bigint(20) | NO | PRI | NULL | auto_increment | | gmt_created | datetime | NO | | NULL | | | gmt_modified | datetime | NO | | NULL | | | object_id | bigint(20) | NO | | NULL | | | count | bigint(20) | NO | | 0 | | -------------- ------------ ------ ----- --------- ---------------- Then we click Like on the page.

If the time consumption in the transaction is reduced, the connection time will be reduced correspondingly, and the system will be able to bear more concurrent requests! If the four statements: begin, update, select and commit are written in the same stored procedure, the network communication times will be reduced from four to one, and the performance will be improved by 75%. The architect often tells us that our system is internet architecture, and if there is no particular reason, the business logic should be put in the application server, and the database is only used for storage but not business.

There should be no particular reason to use stored procedures for such a small requirement.

Even until now, there is still no way to have it done via a single query in the Oracle-maintained official version of My SQL. Besides the Maria DB which has been thoroughly split out, there is also Percona, the so-called enhanced version which is fully compatible with My SQL.

Besides Percona which provides source code, there is also Ali SQL maintained by Alibaba, and the database-as-a-service Alibaba Cloud RDS.

If you are using Alibaba Cloud RDS, you may try the writing to combine the update and select statements into an SQL statement, and further reduce the network overhead and database overhead to improve the performance.

select count from update likes set gmt_modified = now(), count = count 1 where object_id = ?; It is important to note that the enhanced syntax is not expressly stated in the cloud database document.

Well, the user experience is really a useful imperial sword.

Tags: , ,