与 InnoDB 表的 PDO 事务

发布于 2024-12-08 01:18:58 字数 471 浏览 1 评论 0原文

我有 InnoDB 表，我们可以通过 PHP 的 PDO API 访问它们。现在，我读到对于 INSERT 和 UPDATE 语句，使用 InnoDB 事务可能是一个好主意。由于自动提交设置为 1，因此它会在查询完成后立即提交查询。因此，如果我将一堆 INSERT 组合在一起并执行以下操作：

$GLOBALS['dbh']->query('BEGIN');
[multiple INSERT queries here]
$GLOBALS['dbh']->query('COMMIT');

它应该会更有效。

问题：

这是正确的吗？
我还了解到某些 API 使用它们自己的事务，并且想知道是否有人知道 PDO 是否这样做。换句话说，我应该担心这样做还是让 PDO 处理事务？
如果 PDO 确实处理事务，我是否会用上述查询搞砸一切？

谢谢。

原文

I have InnoDB tables that we access via a PDO API from PHP. Now, I've read that for INSERT and UPDATE statements, it would probably be a good idea to use InnoDB transactions. Since auto commit is set to 1, it would commit the query as soon as it is made. So if I group a bunch of INSERTs together and do:

$GLOBALS['dbh']->query('BEGIN');
[multiple INSERT queries here]
$GLOBALS['dbh']->query('COMMIT');

It's supposed to be more efficient.

Questions:

Is this correct?
I also read that certain APIs make use of their own transactions and was wondering if anyone knew if PDO does this. In other words, should I worry about doing this at all or let PDO handle transactions?
In the case that PDO does handle transactions, am I screwing everything up with the above queries?

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

迷鸟归林 2024-12-15 01:18:58

这是正确的吗？

是的。
小挑剔：我会使用 START TRANSACTION 而不是 begin，它是相同的，但更不言而喻。

我还了解到某些 API 使用自己的事务，并且想知道是否有人知道 PDO 是否这样做。换句话说，我应该担心这样做还是让 PDO 处理事务？

PDO 不会神奇地知道事务何时开始和结束，因此如果 auto-commit =1 并且您希望在事务中包含 1 条以上的语句，您仍然必须开始和结束事务。
你不用担心，你上面所做的一切都很好。

如果 PDO 确实处理事务，我是否会用上述查询搞砸一切？

不。

因此，如果我将一堆 INSERT 组合在一起并执行以下操作：{参见上面的代码}
应该会更有效率。

不是很多，如果您可以将所有插入压缩到一个语句中那会更有效。
如果您可以用 load data infile 替换插入，那么效率会更高。

示例：

INSERT INTO table1 (field1, field2) VALUES (1,1),(2,5),(5,6);
-- Much more efficient than 3 separate inserts 
-- (and you don't need to start and end the transaction :-)

Is this correct?

Yes.
Small nitpick: I would use START TRANSACTION instead of begin, it is the same, but more self-evident.

I also read that certain APIs make use of their own transactions and was wondering if anyone knew if PDO does this. In other words, should I worry about doing this at all or let PDO handle transactions?

PDO does not magically know when you transactions start and end, so you will still have to start and end your transactions if auto-commit =1 and you want to include more than 1 statement in a transaction.
You should not worry, what you are doing above is fine.

In the case that PDO does handle transactions, am I screwing everything up with the above queries?

No.

So if I group a bunch of INSERTs together and do: {see code above}
It's supposed to be more efficient.

Not very much, if you can cramp all your inserts into a single statement that would be more efficient.
And if you can replace the insert with a load data infile that would be more efficient still.

Example:

INSERT INTO table1 (field1, field2) VALUES (1,1),(2,5),(5,6);
-- Much more efficient than 3 separate inserts 
-- (and you don't need to start and end the transaction :-)

回复收藏 0 原文