php mysql同时写入数据库表,PHP MySQL将数据插入多个表

I'm trying to make an experimental web application which minimises redundant data.

I have three example tables set up like so:

Table one

ID | created_at (unix timestamp) | updated_at (unix timestamp)

Table two

ID | Foreign Key to table one | Title

Table three (pages)

ID | Foreign Keys to both table one and two | Content | Metadata

The idea being that everything created in the application will have a creation/edit time.

Many (but not all) things will have a title (For example a page or a section for a page to go into).

Finally, some things will have attributes specific to themselves, eg content and metadata for a page.

I'm trying to work out the best way to enter data into multiple tables. I know I could do multiple insert queries from PHP, keep track of rows created in the current transaction and delete those rows should a later part of the transaction fail. However, if the PHP script dies completely, it may stop before all of the deletions can be completed.

Does MySQL have any inbuilt logic which would allow the insert query to be split up? Would a trigger be able to handle this type of transaction or is it beyond its capabilities?

Any advice, thoughts or ideas would be greatly appreciated.

Thanks!

解决方案

A solution would be to use Transactions, which allow to get "all or nothing" behaviour.

The idea is the following :

you start a transaction

you do your inserts/updates

if everything is OK, you commit the transaction ; which will save everything you did during this transaction

if not, you rollback the transaction ; and everything you did in it will be cancelled.

if you don't commit and disconnected (if your PHP script dies, for instance), nothing will be commited, and what you did during the un-commited transaction will automatically be rolled-back.

For more informations, you can take a look at 12.4.1. START TRANSACTION, COMMIT, and ROLLBACK Syntax, for MySQL.

Note that transactions are only available for some DB engines :

MyISAM doesn't support transactions

InnoDB does (it also supports foreign keys, for instance -- it's far more advanced that MyISAM).