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

目录

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 函数调用失败的不同类型的信息，通常你会同时使用这两个函数来获取完整的错误上下文。