A few months ago, I was asking myself how to insert in two tables in a single round-trip to the database. I wanted to do that to optimize a process. My optimization involved splitting a table in two, which would need inserting in two tables atomically. The downside was changing an auto-commit INSERT to a transaction with two inserts, which was changing the shape of the workload from a single round-trip to the database to four: BEGIN, INSERT1, INSERT2, and COMMIT. I had no satisfying solution to that, I now have one: JSON Duality Views in MySQL 9.7.
I found that solution in Marcelo Altmann post JSON Duality Views in MySQL 9.7 — What you need to know. I will let you read Marcelo's post for the details.
The unsatisfying solutions I had were Stored Procedures and Triggers. Both are deploying application logic in the database, and this is not a line I cross lightly. I was thinking of a new INSERT syntax for solving this, but it was non-trivial (UPDATE and DELETE are possible on two tables). Now, I know of a better way in MySQL 9.7, and it makes this new version more appealing to me (before knowing this, I found both MySQL 8.4 and 9.7 very ordinary and not worth the upgrade, this now changed).
No comments:
Post a Comment