mysql异常信息捕获

https://www.jb51.net/article/263743.htm

CREATE DEFINER=`root`@`%` PROCEDURE `p1`()
BEGIN
    -- Declare variables to hold diagnostics area information
    DECLARE errcount INT;
    DECLARE errno INT;
    DECLARE msg TEXT;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        --获取当前诊断区的异常信息
        -- rollback;//在这里只要有类如declare、rollback、call、insert等语句都会清空当前诊断区的信息，即清空MYSQL_ERRNO和MESSAGE_TEXT的值
        GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;//GET CURRENT DIAGNOSTICS语句也会清空当前诊断区的信息
        SELECT 'current DA before mapped insert' AS op, errno, msg;
                set errno = 0;//是用来测试的
        GET STACKED DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
        SELECT 'stacked DA before mapped insert' AS op, errno, msg;
                set errno = 2;
        -- rollback;
        -- INSERT INTO t1 (c1) VALUES('');//这条语句也会清空当前诊断区
        -- call p_bd_sql_log('存储过程执行过程中发生异常','p1','','','','','','','','','','');//这条语句也会清空当前诊断区

        GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
        SELECT 'current DA before mapped insert' AS op, errno, msg;
                set errno = 0;
                
        GET CURRENT DIAGNOSTICS errcount = NUMBER;
        IF errcount = 0 THEN
            SELECT 'mapped insert succeeded, current DA is empty' AS op;
            GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
            SELECT 'current DA after mapped insert666' AS op, errno, msg;
            set errno = 0;
        ELSE
            GET CURRENT DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
            SELECT 'current DA after mapped insert' AS op, errno, msg;
            set errno = 1;
        END IF;
                
            
        
        GET STACKED DIAGNOSTICS CONDITION 1 errno = MYSQL_ERRNO, msg = MESSAGE_TEXT;
        SELECT 'stacked DA after mapped insert' AS op, errno, msg;
        -- rollback;//如果需要手动回滚存储过程本体的sql语句，在这里写rollback
    END;
        -- START TRANSACTION;//如果需要手动回滚存储过程本体的sql语句，一定要开启事务
    INSERT INTO t1 (c1) VALUES('555');
    INSERT INTO t1 (c1) VALUES(NULL);
        -- COMMIT;//commit语句可写可不写
END