douban5644
2011-02-10 01:59
浏览 8
已采纳

原子地多个命令

I'm using PHP and MySQL to call a couple of commands. There is a group-employee cross-reference table, and employees may be added or removed from the group. I'm writing this feature by first removing all the employees and then adding back in each employee that is in the table if the group is modified. But I want to make sure that all the commands go through or else have the table roll back. How can I make sure the commands happen atomically?

图片转代码服务由CSDN问答提供 功能建议

我正在使用PHP和MySQL来调用几个命令。 有一个组 - 员工交叉引用表,可以在组中添加或删除员工。 我正在编写此功能,首先删除所有员工,然后在组中的每个员工中添加回来(如果组已修改)。 但我想确保所有命令都通过或者让表回滚。 如何确保命令以原子方式发生?

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dozoqn3347 2011-02-10 02:02
    已采纳

    You're looking for Transactions unless I'm mistaken.

    Here's a great place to start: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-transactions.html

    打赏 评论
  • dongwei1895 2011-02-10 02:10

    Assuming your target tables support transactions, then you want the mysqli transaction family of methods.

    See:

    If your tables do not support transactions, you could likely change your table types to 'InnoDB` without a problem, and thus support transactions. Check with your DB admin before doing so first.

    打赏 评论
  • doudi2431 2011-02-10 02:10

    If you're using the MyIsam engine instead of InnoDB, then you can't take advantage of transactions. In that case, your best bet is to perform all your INSERTS firsts (your cross-ref table should have a two-field unique constraint so dups will silently fail). Then perform a single DELETE:

    DELETE FROM employees_groups 
    WHERE group_id = <groupid> 
    AND employee_id NOT IN (list of employee ids you just inserted)
    
    打赏 评论

相关推荐 更多相似问题