【MySQL】C语言连接MySQL数据库3——事务操作和错误处理API

时间:2024-10-25 07:10:10

目录

1.MySQL事务处理机制

1.1.autocommit

1.2.autocommit的设置与查看

1.3.使用示例

2.事务操作API

2.1.设置事务提交模式——mysql_autocommit()

 2.2.提交事务——mysql_commit()

2.3.事务回滚——mysql_rollback()

3.错误处理的API

3.1.返回错误的描述——mysql_error()

3.2.返回错误的编号——mysql_errno()


 

1.MySQL事务处理机制

1.1.autocommit

        默认情况下, MySQL启用自动提交模式(变量autocommit为ON)。这意味着,只要你执行DML操作的语句,MySQL会立即隐式提交事务(Implicit Commit)

        在 MySQL 命令行的默认设置下,事务都是自动提交的,即执行 SQL 语句后就会马上执行 COMMIT 操作。

在MySQL的InnoDB存储引擎中,事务的处理方式主要由autocommit参数来决定。该参数能够设定事务是自动提交还是手动提交。

1. 当autocommit=0时(手动提交模式)

  • 事务启动:一旦用户对数据进行操作(例如INSERT、UPDATE或DELETE等修改数据的SQL语句),事务将自动开始。
  • 事务提交:用户需要明确执行COMMIT命令来提交事务,从而确保所有更改都被永久保存到数据库中。若不执行commit命令,系统则默认事务回滚。
  • 事务周期:从事务开始到用户执行COMMIT命令之间的所有数据库操作,都被视为一个完整的事务周期。
  • 事务回滚:如果用户在事务期间未执行COMMIT命令,而是执行了ROLLBACK命令,或者由于某些错误导致事务中断,那么事务中的所有更改都将被撤销。

需要特别注意的是,在autocommit=0模式下,如果用户未执行COMMIT或ROLLBACK命令就关闭了数据库连接,那么MySQL会根据当前会话的autocommit设置和是否遇到了错误来决定是否自动回滚事务。然而,为了确保数据的一致性,通常建议用户明确执行COMMIT或ROLLBACK命令。

2. 当autocommit=1时(自动提交模式,系统默认值)

  • 自动提交:如果用户没有使用START TRANSACTION或BEGIN命令来显式地启动一个事务,那么MySQL会将每个独立的SQL语句视为一个单独的事务,并在该语句执行后立即提交。
  • 手动提交:尽管autocommit的默认值是1,但用户仍然可以通过执行START TRANSACTION或BEGIN命令来显式地启动一个事务,并在完成所有操作后执行COMMIT命令来提交事务。
  • 事务周期
    • 在没有显式启动事务的情况下,每个SQL语句都是一个独立的事务周期。
    • 在显式启动事务的情况下,从START TRANSACTION或BEGIN到COMMIT或ROLLBACK之间的所有操作为一个完整的事务周期。

注意:autocommit 设置只针对删除(DELETE)、插入(INSERT)、修改(UPDATE)这类会改变数据库中数据的操作,而不包括查询(SELECT)操作。

        在MySQL中,autocommit 是一个会话级别的变量,用于控制是否自动提交事务。当 autocommit 设置为 ON 时,每个独立的DML操作(如INSERT、UPDATE、DELETE)都会被当作一个事务并自动提交。这意味着,每执行一个这样的操作,MySQL都会立即将其更

改保存到数据库中,无需用户显式地执行COMMIT操作。

        然而,当 autocommit 设置为 OFF 时,用户需要手动控制事务的提交。在这种情况下,用户可以执行多个DML操作,并且这些更改在事务提交之前都不会被永久保存到数据库中。用户需要显式地执行COMMIT操作来提交事务,或者执行ROLLBACK操作来撤销事务中的更改。
        查询(SELECT)操作则不受 autocommit 设置的影响。无论 autocommit 是开启还是关闭,SELECT语句都会正常执行并返回结果,因为它不会改变数据库中的数据。

因此,autocommit 设置只影响那些会改变数据库中数据的DML操作,而不包括查询操作。 

1.2.autocommit的设置与查看

用户可以将自动提交功能强制置为OFF。这样用户执行SQL语句后将不会被提交了,而执行COMMIT命令才提交,执行ROLLBACK命令回滚。

  • 查看当前设置

用户可以使用以下SQL命令来查看autocommit的当前状况:

SHOW VARIABLES LIKE 'autocommit';

 

很明显,我们的自动提交已经开启了!

  • 永久设置

要永久更改autocommit的值,用户需要修改MySQL的配置文件(通常是my.cnf或my.ini)。

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf

 在配置文件的[mysqld]部分,用户可以添加或修改以下行来设置autocommit的值: 

autocommit=0
或者
autocommit=1

完成配置文件的修改后,用户需要重启MySQL服务来使更改生效。

sudo service mysql restart

这样子就算是配置好了!!! 

  • 临时设置

用户可以使用以下SQL命令来临时更改autocommit的值。这些更改仅在当前数据库会话中有效,当会话结束时,autocommit的值将恢复到默认值或配置文件中的设置值。

SET AUTOCOMMIT=1; -- 设置为自动提交模式  
SET AUTOCOMMIT=0; -- 设置为手动提交模式 

我们可以使用这个来展示一下MySQL的手动提交事务

1.3.使用示例

我们先创建一个表来

create table test(id int,name varchar(10));
insert into test values(1,'A'),(2,'B');
select * from test;

 

接下来我们先测试autocommit会不会影响我们的查询操作 

SET AUTOCOMMIT=1;
select * from test;
SET AUTOCOMMIT=0;
select * from test;

 

我们发现autocommit并不会影响到 这个查询操作,接下来我们来测试一下我们的插入操作。

​SET AUTOCOMMIT=1;
insert into test values(3,'C');
select * from test;

SET AUTOCOMMIT=0;
insert into stu test(4,'D');
select * from test;

嗯?为什么两个情况是一样的??都能查询的到?

但是,要是我们现在打开另外一个终端登陆我们的mysql数据库查询我们的test,就会发现下面这个情况

 

我们发现4,B不在里面!!!

这是什么情况!!

这里的关键在于理解MySQL中事务的提交和回滚机制,以及 AUTOCOMMIT 设置的实际影响。

AUTOCOMMIT=0 的影响:

  • 当您设置 AUTOCOMMIT=0 时,MySQL会开始一个新的事务,并且不会自动提交该事务中的任何更改。这意味着,从那时起,您执行的任何DML操作(如INSERT、UPDATE、DELETE)都会成为事务的一部分,直到您显式地执行 COMMIT 或 ROLLBACK。

事务的持久性:

  • 在MySQL中,当您执行 COMMIT 时,事务中的所有更改都会成为数据库中的永久更改,并且对其他会话可见。
  • 如果您执行 ROLLBACK,则事务中的所有更改都会被撤销,就像它们从未发生过一样。

未提交事务的可见性:

  • 通常,未提交的事务对其他会话是不可见的。但是,在同一个会话中,即使您没有提交事务,您仍然可以看到自己在这个事务中所做的更改。这是因为MySQL允许会话在事务期间查看自己的更改(这通常被称为“一致性读”或“非阻塞读”)。

缺少 COMMIT 或 ROLLBACK:

  • 在会话中,我们没有执行 COMMIT 或 ROLLBACK 来结束事务。但是,由于您是在同一个会话中查询数据,因此您仍然能够看到新插入的行。如果您在另一个会话中查询 test 表,并且那个会话在您的插入操作之前没有开始,那么在那个会话中您将看不到未提交的行。

会话结束时的自动回滚:

  • 重要的是要注意,如果您在禁用 AUTOCOMMIT 的情况下结束了一个MySQL会话(比如通过关闭客户端连接),那么MySQL会自动回滚该会话中所有未提交的事务。这意味着,如果您没有显式地提交事务,那么当会话结束时,您所做的更改将不会被保存到数据库中。

综上所述,尽管您在 AUTOCOMMIT=0 的情况下插入了数据,并且能够在同一个会话中看到这些数据,但这并不意味着这些数据已经被永久保存到数据库中。

  1. 如果您想要确保数据被永久保存,您需要在插入数据后执行 COMMIT。
  2. 如果您想要撤销更改,您可以执行 ROLLBACK。
  3. 如果您不执行任何操作并结束会话,那么更改将被自动回滚。

好,看到这里我们算是明白了!接下来我们执行ROLLBACK;

        在MySQL中,ROLLBACK; 命令用于撤销(或回滚)当前事务中所做的所有更改。当您在一个事务中执行了多个DML操作(如INSERT、UPDATE、DELETE)后,如果您决定不想保存这些更改,您可以使用ROLLBACK;命令来撤销它们。

这里有一些关键点需要了解:

事务的开始

  1. 当您执行一个DML操作时并且AUTOCOMMIT设置为0(或您已经通过START TRANSACTION;显式地开始了一个事务),MySQL会开始一个新的事务。

事务的提交

  1. 如果您想要保存事务中的更改,您需要使用COMMIT;命令。

事务的回滚:

  1. 如果您决定不保存事务中的更改,您可以使用ROLLBACK;命令来撤销它们。回滚后,事务中的所有更改都将被撤销,就像它们从未发生过一样。

AUTOCOMMIT的影响:

  1. 当AUTOCOMMIT设置为1时,MySQL会在每个DML操作后自动提交事务。这意味着,每个DML操作都会立即成为数据库中的永久更改,并且不能被回滚。
  2. 当AUTOCOMMIT设置为0时,您需要显式地使用COMMIT;或ROLLBACK;来结束事务。

使用ROLLBACK;的时机:

  1. 您可以在事务中的任何时间点使用ROLLBACK;来撤销从上一个COMMIT;(或事务开始)以来的所有更改。
  2. 如果您在一个事务中执行了多个操作,并且决定不想保存任何更改,那么您应该在执行任何其他操作之前立即使用ROLLBACK;。

注意事项:

  1. 一旦您执行了COMMIT;,事务中的所有更改都将被永久保存到数据库中,并且不能再使用ROLLBACK;来撤销它们。
  2. 如果您结束了MySQL会话(例如,通过关闭客户端连接),并且在该会话中有未提交的事务,那么MySQL会自动回滚这些事务中的更改。

  • 未使用commit时使用rollback 
ROLLBACK;

 

执行我们发现我们插入的就不见了!!!

另外一台机器也没有看到我们的数据(4,D)

  • 未使用rollback时使用commit

我们现在换一台机器看

很显然,我们插入的数据还没有保存!!!

我们回到开始那台机器执行commit

接着,我们换一台机器看

很好,我们的数据保存了!!!这个时候我们再按rollback也无力回天了!!

其他机器也是如此


到现在我们就很清楚了我们的autocommit的工作场景

  1. autocommit=0
  2. 事务开始:执行了删除,修改,增加操作后
  3. 事务结束:执行了commit,rollback两个的任意一条

这3者缺一不可!!!!!

2.事务操作API

对于事务的操作我们也可以使用mysql_query()函数进行开启一个事务,然后进行处理事务,下面我们介绍一些关于控制事务操作的一些API。

2.1.设置事务提交模式——mysql_autocommit()

mysql_autocommit() 是 MySQL C API 中用于设置或检查当前会话的自动提交模式的函数。

  1. 在自动提交模式下,每个独立的语句(如 INSERT、UPDATE 或 DELETE)在被执行后都会自动提交到数据库中,这意味着每个语句都会立即生效,并且不能被回滚。
  2. 相反,在非自动提交模式下,你需要显式地使用 COMMIT 语句来提交事务,或者使用 ROLLBACK 语句来撤销自上次提交以来所做的所有更改。

函数原型

my_bool mysql_autocommit(MYSQL *mysql, my_bool mode); 
  1. mysql:一个指向已连接的 MYSQL 对象的指针。
  2. mode:一个 my_bool 值,用于指定要设置的自动提交模式。1(或非零值)表示启用自动提交,0 表示禁用自动提交。

返回值

  • 如果函数成功,则返回之前的自动提交模式(1 表示启用,0 表示禁用)。
  • 如果函数失败,则返回 -1。

使用示例

MYSQL *conn;  
// 假设 conn 已经通过 mysql_real_connect() 成功连接到数据库  
  
// 检查当前的自动提交模式  
my_bool current_mode = mysql_autocommit(conn, 0);  
if (current_mode == -1) {  
    // 处理错误  
} else {  
    printf("Current autocommit mode: %s\n", current_mode ? "enabled" : "disabled");  
}  
  
// 设置自动提交模式为禁用  
if (mysql_autocommit(conn, 0) == -1) {  
    // 处理错误  
} else {  
    printf("Autocommit mode disabled.\n");  
}  
  
// 执行一些事务性操作...  
  
// 提交事务(仅在自动提交被禁用时需要)  
if (mysql_commit(conn) == -1) {  
    // 处理错误  
} else {  
    printf("Transaction committed.\n");  
}  
  
// 恢复自动提交模式为启用  
if (mysql_autocommit(conn, 1) == -1) {  
    // 处理错误  
} else {  
    printf("Autocommit mode enabled.\n");  
}  
  
// 关闭连接(不要忘记这一步)  
mysql_close(conn);

注意事项

  1. 在禁用自动提交模式后,务必确保在适当的时候使用 COMMIT 提交事务,或者使用 ROLLBACK 撤销事务。否则,你的更改将不会被保存到数据库中,并且在会话结束时可能会被自动回滚。
  2. 调用 mysql_autocommit() 时,如果当前有一个正在执行的事务(即已经执行了一些更改但尚未提交或回滚),则这些更改的状态将取决于你之后是调用 COMMIT 还是 ROLLBACK。在禁用自动提交模式后,直到你提交或回滚事务之前,其他会话将无法看到这些更改。

 2.2.提交事务——mysql_commit()

mysql_commit() 是 MySQL C API 中的一个函数,用于提交当前事务。

当在 MySQL 会话中禁用自动提交模式后(通过调用 mysql_autocommit(mysql, 0)),你可以执行多个 SQL 语句作为单个事务的一部分。这些语句要么全部成功并提交,要么在遇到错误时全部回滚。要提交这些语句作为事务的一部分,你需要调用 mysql_commit()。

函数原型

int mysql_commit(MYSQL *mysql); 
  • mysql:一个指向已连接的 MYSQL 对象的指针,该对象代表与 MySQL 服务器的连接。

返回值

  1. 如果函数成功,则返回 0。
  2. 如果函数失败,则返回非零值。通常,你可以通过调用 mysql_error(mysql) 来获取更详细的错误信息。

使用示例

MYSQL *conn;  
// 假设 conn 已经通过 mysql_real_connect() 成功连接到数据库,并且已经禁用了自动提交模式  
  
// 执行一些事务性操作,例如 INSERT、UPDATE 或 DELETE 语句  
if (mysql_query(conn, "INSERT INTO my_table (column1) VALUES ('value1')") != 0) {  
    // 处理错误  
    fprintf(stderr, "INSERT failed: %s\n", mysql_error(conn));  
    // 可能需要回滚事务  
    mysql_rollback(conn);  
    // 然后关闭连接等清理操作  
}  
// ... 可能还有其他事务性操作 ...  
  
// 提交事务  
if (mysql_commit(conn) != 0) {  
    // 处理错误  
    fprintf(stderr, "Commit failed: %s\n", mysql_error(conn));  
    // 在某些情况下,你可能希望回滚事务(尽管在提交失败后回滚可能没有意义)  
    // 或者进行其他错误处理  
} else {  
    printf("Transaction committed successfully.\n");  
}  
  
// 启用自动提交模式(可选,取决于你的应用程序逻辑)  
mysql_autocommit(conn, 1);  
  
// 关闭连接  
mysql_close(conn);

注意事项

  1. 在调用 mysql_commit() 之前,确保你已经禁用了自动提交模式,并且已经执行了作为事务一部分的所有 SQL 语句。
  2. 如果 mysql_commit() 失败,并且你希望保留部分或全部更改,则可能需要手动处理错误,并决定是回滚事务还是采取其他措施。然而,请注意,在 mysql_commit() 调用失败后回滚事务可能不是有效的操作,因为提交操作本身可能已经部分完成或失败,导致事务状态不确定。
  3. 在事务提交后,所有在事务中执行的更改都将对数据库中的其他会话可见。

2.3.事务回滚——mysql_rollback()

mysql_rollback() 是 MySQL C API 中的一个函数,用于回滚当前事务。

当在 MySQL 会话中禁用自动提交模式后,你可以执行一系列 SQL 语句作为单个事务的一部分。如果在这些语句执行过程中发生错误,或者出于某种原因你决定不保留这些更改,你可以调用 mysql_rollback() 来撤销自上次提交以来所做的所有更改。

函数原型

int mysql_rollback(MYSQL *mysql); 
  • mysql:一个指向已连接的 MYSQL 对象的指针,该对象代表与 MySQL 服务器的连接。

返回值

  1. 如果函数成功,则返回 0。
  2. 如果函数失败,则返回非零值。通常,你可以通过调用 mysql_error(mysql) 来获取更详细的错误信息。

使用示例

MYSQL *conn;  
// 假设 conn 已经通过 mysql_real_connect() 成功连接到数据库,并且已经禁用了自动提交模式  
  
// 执行一些事务性操作,例如 INSERT、UPDATE 或 DELETE 语句  
if (mysql_query(conn, "INSERT INTO my_table (column1) VALUES ('value1')") != 0) {  
    // 处理错误  
    fprintf(stderr, "INSERT failed: %s\n", mysql_error(conn));  
      
    // 回滚事务  
    if (mysql_rollback(conn) != 0) {  
        // 处理回滚错误  
        fprintf(stderr, "Rollback failed: %s\n", mysql_error(conn));  
        // 进行其他错误处理或清理操作  
    } else {  
        printf("Transaction rolled back successfully.\n");  
    }  
      
    // 可能需要重新尝试事务,或者进行其他逻辑处理  
    // ...  
      
    // 完成后关闭连接  
    mysql_close(conn);  
    return -1; // 或其他适当的错误代码  
}  
// ... 可能还有其他事务性操作 ...  
  
// 如果所有操作都成功,则提交事务  
if (mysql_commit(conn) != 0) {  
    // 处理提交错误  
    fprintf(stderr, "Commit failed: %s\n", mysql_error(conn));  
      
    // 尝试回滚(尽管在提交失败后回滚可能没有意义,因为提交可能已经部分完成)  
    // 但出于完整性考虑,这里还是包含回滚调用  
    mysql_rollback(conn);  
      
    // 进行其他错误处理或清理操作  
    // ...  
      
    // 完成后关闭连接  
    mysql_close(conn);  
    return -1; // 或其他适当的错误代码  
} else {  
    printf("Transaction committed successfully.\n");  
}  
  
// 启用自动提交模式(可选,取决于你的应用程序逻辑)  
mysql_autocommit(conn, 1);  
  
// 关闭连接  
mysql_close(conn);

注意事项

  1. 在调用 mysql_rollback() 之前,确保你已经禁用了自动提交模式,并且已经执行了作为事务一部分的 SQL 语句。
  2. 如果在事务执行过程中发生错误,并且你决定不保留更改,应立即调用 mysql_rollback() 来撤销这些更改。
  3. 请注意,在调用 mysql_commit() 之后调用 mysql_rollback() 通常是无效的,因为提交操作已经将事务的更改永久保存到数据库中。然而,出于完整性考虑,你可以在提交失败后尝试回滚(尽管这通常没有意义),但应该意识到这样做可能不会撤销任何更改。
  4. 在回滚事务后,所有在事务中执行的更改都将被撤销,并且这些更改将不会对数据库中的其他会话可见。
  5. 确保在不再需要数据库连接时调用 mysql_close() 来释放资源。如果事务在回滚或提交之前由于某种原因被中断(例如,由于网络问题或服务器崩溃),则连接可能会处于不确定状态。在这种情况下,最好关闭连接并重新建立新的连接。

3.错误处理的API

3.1.返回错误的描述——mysql_error()

mysql_error() 是 MySQL C API 中的一个函数,用于获取最近一次 MySQL 函数调用失败的错误描述。

当你调用 MySQL C API 中的函数(如 mysql_query(), mysql_commit(), mysql_rollback() 等)时,如果函数返回非零值(通常表示失败),你可以使用 mysql_error() 函数来获取更详细的错误信息。

函数原型

const char *mysql_error(MYSQL *mysql); 
  • mysql:一个指向已连接的 MYSQL 对象的指针,该对象代表与 MySQL 服务器的连接。

返回值

  • 返回一个指向描述最近一次错误的字符串的指针。如果最近一次 MySQL 函数调用成功,则返回的字符串可能是一个空字符串或表示没有错误的消息(这取决于 MySQL 的版本和配置)。

使用示例

MYSQL *conn;  
// 假设 conn 已经通过 mysql_real_connect() 连接到数据库  
  
// 执行一个查询  
if (mysql_query(conn, "SOME INVALID SQL STATEMENT") != 0) {  
    // 获取并打印错误信息  
    fprintf(stderr, "Query failed: %s\n", mysql_error(conn));  
      
    // 进行其他错误处理,如回滚事务或关闭连接  
    // ...  
} else {  
    // 处理查询结果  
    // ...  
}  
  
// 关闭连接  
mysql_close(conn);

注意事项

  1. 在调用 mysql_error() 之前,确保你有一个有效的 MYSQL 连接对象,并且该对象与最近一次失败的 MySQL 函数调用相关联。
  2. mysql_error() 返回的字符串是静态分配的,并且在下次调用任何 MySQL 函数时可能会被覆盖。因此,如果你需要保留错误信息,请将其复制到自己的缓冲区中。
  3. 当你完成数据库操作并关闭连接后,再调用 mysql_error() 可能不会返回有用的信息,因为连接已经被销毁。
  4. 在多线程环境中,每个线程应该有自己的 MYSQL 连接对象,并且应该确保在调用 mysql_error() 时使用的是与失败操作相关联的正确连接对象。
  5. 记住,mysql_error() 只提供最近一次 MySQL 函数调用的错误信息。如果你执行了多个操作并且它们中的任何一个失败了,你应该在每次失败后立即检查错误,因为后续的操作可能会覆盖之前的错误信息。

3.2.返回错误的编号——mysql_errno()

mysql_errno() 是 MySQL C API 中的一个函数,它用于获取与最近一次 MySQL 函数调用失败相关联的错误编号。

与 mysql_error() 函数不同,mysql_errno() 返回的是一个整数错误代码,而不是描述错误的字符串。这个错误编号可以用于错误处理逻辑中,以便根据特定的错误类型执行不同的操作。

函数原型

unsigned int mysql_errno(MYSQL *mysql); 
  1. mysql:一个指向已连接的 MYSQL 对象的指针,该对象代表与 MySQL 服务器的连接。
  • 返回值
  1. 返回一个无符号整数,表示最近一次 MySQL 函数调用失败的错误编号。如果最近一次调用成功,则返回的值可能依赖于 MySQL 的实现,但通常可以认为是 0(表示没有错误)。

使用示例

MYSQL *conn;  
// 假设 conn 已经通过 mysql_real_connect() 连接到数据库  
  
// 执行一个查询  
if (mysql_query(conn, "SOME INVALID SQL STATEMENT") != 0) {  
    // 获取错误编号  
    unsigned int err_no = mysql_errno(conn);  
      
    // 获取并打印错误信息  
    fprintf(stderr, "Query failed with error number: %u, message: %s\n", err_no, mysql_error(conn));  
      
    // 根据错误编号执行特定的错误处理逻辑  
    if (err_no == ER_BAD_TABLE_ERROR) {  
        // 处理表不存在的错误  
        // ...  
    } else if (err_no == ER_ACCESS_DENIED_ERROR) {  
        // 处理访问被拒绝的错误  
        // ...  
    } else {  
        // 处理其他类型的错误  
        // ...  
    }  
      
    // 进行其他错误处理,如回滚事务或关闭连接  
    // ...  
} else {  
    // 处理查询结果  
    // ...  
}  
  
// 关闭连接  
mysql_close(conn);

注意事项

  1. 与 mysql_error() 一样,mysql_errno() 也需要在调用之前确保你有一个有效的 MYSQL 连接对象,并且该对象与最近一次失败的 MySQL 函数调用相关联。
  2. 错误编号是 MySQL 服务器定义的,并且可以在 MySQL 的文档或错误日志中找到它们的含义。
  3. mysql_errno() 返回的错误编号是静态的,直到你执行另一个 MySQL 函数调用为止。因此,你应该在每次失败调用后立即检查错误编号。
  4. 在多线程环境中,每个线程应该有自己的 MYSQL 连接对象,并且应该确保在调用 mysql_errno() 时使用的是与失败操作相关联的正确连接对象。
  5. 记住,mysql_errno() 和 mysql_error() 提供了关于最近一次 MySQL 函数调用失败的不同类型的信息,通常你会同时使用这两个函数来获取完整的错误上下文。