【重学 MySQL】八十、掌握定义条件与处理程序，让数据库管理更高效！

【重学 MySQL】八十、掌握定义条件与处理程序，让数据库管理更高效！

在 MySQL 中，定义条件（Conditions）和处理程序（Handlers）通常用于存储过程（Stored Procedures）和存储函数（Stored Functions）中，以便在特定情况下执行特定的操作。这些功能在错误处理和流程控制方面非常有用。

定义条件

条件（Conditions）允许你定义一组命名条件，这些条件可以对应到特定的 SQLSTATE 或 MySQL 错误代码。定义条件的一般语法如下：

DECLARE condition_name CONDITION FOR [sqlstate_value | MYSQL_error_code] value;

• condition_name 是你定义的条件的名称。
• ``sqlstate_value` 是一个 5 字符的 SQLSTATE 值（例如 ‘45000’）。
• MYSQL_ERROR_CODE 是一个 MySQL 错误代码（例如 1062）。
• value 是具体的 SQLSTATE 或 MySQL 错误代码值。

MYSQL_error_code 和 sqlstate_value

在MySQL中，MYSQL_error_code 和 sqlstate_value 是两种用于标识数据库操作错误的代码，它们各自具有不同的特点和用途。

MYSQL_error_code

• 定义：MYSQL_error_code 是MySQL数据库特有的错误代码，每个代码都对应一个特定的错误类型或情况。
• 特点：这些代码是MySQL数据库系统内部定义的，用于标识和记录各种可能的错误。它们通常是数字形式的，例如 1062 表示重复键错误。
• 使用：当MySQL数据库操作失败时，系统通常会返回一个或多个 MYSQL_error_code，以帮助开发者或数据库管理员诊断问题。这些代码可以在MySQL的错误日志、返回的错误信息或异常处理程序中找到。

sqlstate_value

• 定义：sqlstate_value 是一个由五个字符组成的字符串，用于表示SQL标准的错误代码。这些代码是从ANSI SQL和ODBC等标准中引用过来的，因此具有更好的通用性和标准化。
• 特点：sqlstate_value 代码以 '00000' 开头表示成功执行，而以其他字符开头的代码则表示不同类型的错误。例如，以 '01' 开头的代码表示警告（SQLWARNING），以 '02' 开头的代码表示未找到（NOTFOUND），而以其他字符开头的代码则表示异常（SQLEXCEPTION）。
• 使用：在MySQL中，当发生错误时，系统也会返回一个 sqlstate_value 代码。这个代码可以在异常处理程序中用于捕获和处理特定类型的错误。此外，它还可以用于与其他遵循SQL标准的数据库系统进行交互和错误处理。

两者之间的关系

• 对应关系：虽然 MYSQL_error_code 和 sqlstate_value 是两种不同的错误表示方式，但它们在MySQL内部是有一一对应关系的。也就是说，每个 MYSQL_error_code 都有一个对应的 sqlstate_value。
• 使用场景：在实际开发中，开发者可以根据自己的需求选择使用哪种错误表示方式。如果需要更具体的MySQL错误信息，可以选择使用 MYSQL_error_code；如果需要更通用的SQL标准错误信息，可以选择使用 sqlstate_value。

示例

假设在MySQL中执行一个插入操作时发生了重复键错误，那么可能会返回以下错误信息：

• 使用 MYSQL_error_code 表示：ERROR 1062 (23000): Duplicate entry 'xxx' for key 'PRIMARY'。在这里，1062 是 MYSQL_error_code，表示重复键错误；23000 是对应的 sqlstate_value。
• 使用 sqlstate_value 表示：SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'xxx' for key 'PRIMARY'。在这里，23000 是 sqlstate_value，表示完整性约束错误（即重复键错误）；1062 是对应的MySQL错误代码。

综上所述，MYSQL_error_code 和 sqlstate_value 都是MySQL中用于表示错误的代码，它们各自具有不同的特点和使用场景。开发者可以根据自己的需求选择使用哪种方式来表示和处理错误。

常见的错误码

• 1005：创建表失败。
• 1006：创建数据库失败。
• 1007：数据库已存在，创建数据库失败。
• 1008：数据库不存在，删除数据库失败。
• 1009：不能删除数据库文件导致删除数据库失败。
• 1010：不能删除数据目录导致删除数据库失败。
• 1012：不能读取系统表中的记录。
• 1020：记录已被其他用户修改。
• 1021：硬盘剩余空间不足，请加大硬盘可用空间。
• 1022：关键字重复，更改记录失败。
• 1040：已到达数据库的最大连接数，请加大数据库可用连接数。
• 1044：当前用户没有访问数据库的权限。
• 1045：不能连接数据库，用户名或密码错误。
• 1048：字段不能为空。
• 1049：数据库不存在。
• 1050：数据表已存在。
• 1051：数据表不存在。
• 1054：字段不存在。
• 1062：字段值重复，入库失败。
• 1130：连接数据库失败，没有连接数据库的权限。
• 1142：当前用户无权访问数据表。
• 1203：当前用户和数据库建立的连接已到达数据库的最大连接数，请增大可用的数据库连接数或重启数据库。
• 1216：外键约束检查失败，更新子表记录失败。
• 1217：外键约束检查失败，删除或修改主表记录失败。

示例

定义一个名为 my_custom_condition 的条件，对应 SQLSTATE 值 ‘45000’：

DECLARE my_custom_condition CONDITION FOR SQLSTATE '45000';

定义一个名为 duplicate_entry 的条件，对应 MySQL 错误代码 1062（表示重复键错误）：

DECLARE duplicate_entry CONDITION FOR MYSQL_ERROR_CODE 1062;

定义处理程序

处理程序（Handlers）允许你在特定条件发生时执行特定的操作。处理程序可以处理条件、警告、未捕获的异常等。定义处理程序的一般语法如下：

DECLARE handler_type HANDLER FOR 
	[condition_name | SQLSTATE | MYSQL_ERROR_CODE | SQLWARNING | NOT FOUND | SQLEXCEPTION]
    [procedure_statement];

• DECLARE：用于声明变量、条件或处理程序的关键字。
• handler_type：指定处理程序的类型，通常是 CONTINUE、EXIT 和 UNDO。
  • CONTINUE：表示遇到错误不处理，继续执行。
  • EXIT：表示遇到错误马上退出。
  • UNDO：表示遇到错误后撤回之前的操作，MySQL 中暂时不支持此操作。
• HANDLER：指定这是一个处理程序声明。
• FOR：后面跟的是触发处理程序的条件。
• condition_name：自定义的条件名，该条件需要在之前的 DECLARE CONDITION 语句中定义。
• sqlstate_value：一个五位数的SQLSTATE代码，用于表示SQL标准的错误或警告类型。
• MYSQL_error_code：MySQL特有的错误代码，用于表示MySQL数据库内部的错误类型。
• SQLWARNING：表示所有SQLSTATE代码以01开头的错误条件。
• NOT FOUND：表示SQLSTATE代码以02开头的错误条件。
• SQLEXCEPTION：表示除SQLWARNING和NOT FOUND之外的所有SQLSTATE代码的条件，即所有异常条件。
• procedure_statement：当指定的条件发生时，要执行的SQL语句或语句块。这可以是一个简单的SQL语句，也可以是一个BEGIN...END块，其中可以包含多个语句。

定义处理程序的几种方法

方法1：捕获SQLSTATE值

DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info = 'NO_SUCH_TABLE';

这里，处理程序被设置为在SQLSTATE值为’42S02’（表示表或视图不存在）时触发。CONTINUE关键字表示处理程序执行后，控制流将继续执行当前块的剩余语句。

方法2：捕获MySQL错误代码

DECLARE CONTINUE HANDLER FOR 1146 SET @info = 'NO_SUCH_TABLE';

与方法1类似，但这里使用的是MySQL特定的错误代码1146（也是表示表或视图不存在）。注意，在MySQL中，你可以直接使用错误代码而不需要前缀MYSQL_ERROR_CODE。

方法3：先定义条件，再调用

DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info = 'NO_SUCH_TABLE';

在这个例子中，首先使用DECLARE CONDITION语句定义了一个名为no_such_table的自定义条件，并将其与MySQL错误代码1146关联。然后，定义了一个处理程序，该处理程序在no_such_table条件被触发时执行。

方法4：使用SQLWARNING

DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

这里定义了一个处理程序，它会在任何SQL警告（即SQLSTATE代码以01开头的条件）发生时触发。

方法5：使用NOT FOUND

DECLARE EXIT HANDLER FOR NOT FOUND SET @info = 'NO_SUCH_TABLE';

这个处理程序会在游标操作返回“未找到”结果（即 SQLSTATE代码以02开头的条件）时触发。

方法6：使用SQLEXCEPTION

DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR';

这个处理程序会在除SQLWARNING和NOT FOUND之外的所有SQL异常（即所有其他 SQLSTATE 代码的条件）发生时触发。它是处理一般错误的通用方式。

总结

每种方法都有其特定的用途和适用场景。在选择使用哪种方法时，你应该根据具体的需求和错误处理策略来决定。例如，如果你想要处理特定的错误代码或条件，方法1、2和3可能更适合。如果你想要捕获和处理所有类型的警告或异常，方法4、5和6可能更合适。同时，你还需要考虑处理程序应该继续执行还是退出当前块，这取决于你的业务逻辑和错误恢复策略。

示例

定义一个 CONTINUE 处理程序，当发生 SQLSTATE ‘23000’（表示约束违反）时，输出一条消息：

DECLARE CONTINUE HANDLER FOR SQLSTATE '23000'
    SELECT 'Constraint violation occurred';

定义一个 EXIT 处理程序，当发生重复键错误（错误代码 1062）时，退出存储过程并返回一个错误：

DECLARE EXIT HANDLER FOR MYSQL_ERROR_CODE 1062
BEGIN
    -- 可以在这里进行一些清理操作
    ROLLBACK;
    -- 返回用户定义的错误代码和消息
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate entry error';
END;

综合示例

下面是一个综合示例，展示如何在存储过程中使用条件和处理程序：

DELIMITER //

CREATE PROCEDURE example_procedure()
BEGIN
    DECLARE duplicate_entry CONDITION FOR MYSQL_ERROR_CODE 1062;
    DECLARE CONTINUE HANDLER FOR duplicate_entry
        SELECT 'Duplicate entry encountered, continuing...';
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        -- 处理所有未分类的 SQL 异常
        ROLLBACK;
        SELECT 'An SQL exception occurred, exiting procedure.';
    END;
    
    START TRANSACTION;
    
    -- 假设这里有一些插入操作，可能会触发重复键错误
    INSERT INTO example_table (id, name) VALUES (1, 'John Doe');
    INSERT INTO example_table (id, name) VALUES (1, 'Jane Doe'); -- 这将触发重复键错误
    
    COMMIT;
    
    SELECT 'Procedure completed successfully.';
    
END//

DELIMITER ;

在这个示例中，如果插入操作导致重复键错误，将触发定义的 CONTINUE HANDLER，输出一条消息并继续执行。如果遇到其他 SQL 异常，将触发 EXIT HANDLER，进行回滚并输出异常消息。

通过定义条件和处理程序，你可以更好地控制存储过程和存储函数中的错误处理和流程控制。