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).