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

问题描述

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

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

这是我的 SQL 查询:

This is my SQL query :

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 可以让我形象化更改(在临时数据库中)，然后如果我满意，我会提交"查询.(我知道我需要 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');

和

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;

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

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屋！