PHP MySQL将数据插入多个表 [英] PHP MySQL inserting data to multiple tables

问题描述

我正在尝试制作一个实验性Web应用程序，以最大程度地减少冗余数据。
我设置了三个示例表，如下所示：

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.

我正在努力找出最好的方法将数据输入多个表的方法。我知道我可以从PHP进行多个插入查询，跟踪当前事务中创建的行，并在事务的稍后部分失败时删除这些行。但是，如果PHP脚本完全死掉，它可能会在所有删除操作完成之前停止。

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.

MySQL是否具有任何内置逻辑，这些逻辑将允许插入查询被删除。分开？触发器能够处理这种类型的交易还是超出其能力范围？

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.

谢谢！

推荐答案

一种解决方案是使用交易，该交易可以获取全有或全无的行为。

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

想法如下：

• 您开始交易


• 您执行插入/更新


• 如果一切正常，则提交事务；这样会节省您在此交易期间所做的一切


• 如果没有，则回滚该交易；


• 如果您没有提交并断开连接（例如，如果您的PHP脚本死了），则不会有任何事情发生

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

有关更多信息，您可以选择查看 12.4.1。对于MySQL，请执行START TRANSACTION，COMMIT和ROLLBACK语法。

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 不支持交易


• InnoDB 确实（例如，它还支持外键-比MyISAM更高级）。

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

这篇关于PHP MySQL将数据插入多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！