MySQL事务:提交和回滚 [英] Mysql transaction : commit and rollback

问题描述

我将PhpMyAdmin数据库引擎从MyISAM更新为INNODB，以允许回滚.

I updated my PhpMyAdmin database engine from MyISAM to INNODB to allow rollback.

这是我的SQL查询:

START TRANSACTION;
UPDATE jkm_content SET state=0 WHERE title IN ('title-1','title2');

结果:

start transaction;# MySQL returned an empty result set (i.e. zero
rows).
UPDATE jkm_content SET state=1 WHERE title IN ('title-1','title2');# 2 rows affected.

1)因此，该语句告诉我有2行受到影响，但是更改没有出现在任何地方(无论是在数据库中还是在网站中).尽管start transaction可以让我直观地看到更改(临时DB)，然后如果我满意，我将提交"查询. (我知道我需要commit来更新数据库，但是如果我commit，更改将是永久的).

1) So the statement informs me that 2 rows are affected but the change doesn't appear anywhere (neither in my DB nor in the website).I though start transaction would allow me to visualize the changes (in a temporary DB) and then if I am satisfy I "commit" the query. (I understand I need to commit to update the DB, but if I commit the change will be permanent).

2)如果我在提交之前看不到效果，那么我不理解rollback的意思.这两个查询之间有什么区别:

2) Then I don't get the point of rollbackif I can't see the effect before committing it. What will be the difference between these two queries :

START TRANSACTION;
UPDATE jkm_content SET state=0 WHERE title IN ('title-1','title2');

AND

START TRANSACTION;
UPDATE jkm_content SET state=0 WHERE title IN ('title-1','title2');
ROLLBACK;

3)如果我做对了，这些功能都是一样的:

3) if I got it right, these functions are all the same :

START TRANSACTION
BEGIN
BEGIN WORK

推荐答案

1)您所做的所有更改在同一事务中都是可见的.如果你这样做

1) All changes you make are visible within the same transaction. If you do

START TRANSACTION;
INSERT INTO MyTable VALUES ('Hi there');
SELECT * FROM MyTable;

您的输出将包括"Hi there".但是，如果您启动第二个数据库连接，则在您从第一个连接中提交事务之前，不会显示新行.尝试通过命令行使用两个数据库连接来玩这个游戏.

your output will include the 'Hi there'. But if you start a second database-connection the new row won't be displayed until you commit your transaction from within the first connection. Try playing with this using two database-connections using the command-line.

您没有在网站上看到效果，因为在两个数据库连接中无法进行相同的事务(在请求开始时将建立新的数据库连接).

You're not seeing the effect in your website because you can't have the same transaction within two database-connection (a new db-connection will be made at the beginning of your request).

2)当与数据库的连接关闭时，所有未提交的事务将回滚.因此，如果这只是您的两个查询，则没有区别.但是，两者之间是有区别的

2) All transactions that aren't committed will be rolled back when the connection with the database is closed. So if these are your only two queries, there are no difference. However there is a difference between

START TRANSACTION;
INSERT INTO MyTable VALUES ('This one would be discarded on rollback');
ROLLBACK;
INSERT INTO MyTable VALUES ('This one will be permanent because not within transaction');  

3)是的，这些都一样.

3) Yes, these are all the same.

这篇关于MySQL事务:提交和回滚的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！