Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL in reading exception error buffer aspects of what are the promotion methods, the text is very detailed, has a certain reference value, interested friends must read!

First, what is an error buffer?

In MySQL, the error buffer only records the last error that occurred, and as soon as a new error occurs, the old one is overwritten. So if you want to know what went wrong,

You'd have to follow every statement that could possibly go wrong with "show warnings" or "show errors." The easiest way to do this is to redirect the output to your own defined

Log file, so that you can easily check later. Of course, if we want to see these errors at any time, we have to do it through the API provided by MySQL.

Since MySQL 5.6, a revolutionary version, the problem has been initially solved. Even though it's still a lot less than we thought.

MySQL 5.6 provides a get diagnostic statement to get the contents of the error buffer, and then output these contents to variables in different scopes, so that we can operate flexibly later.

Come down, let's look at an example.

www.2cto.com

The table structure for is:

[sql]

CREATE TABLE `t_datetime` (

`id` int(11) NOT NULL,

`log_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

`end_time` datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

The stored procedure code is:

[sql]

DELIMITER $$

www.2cto.com

USE `new_t`$$

DROP PROCEDURE IF EXISTS `sp_do_update`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_do_update`(

IN f_id INT,

IN f_log_time VARCHAR(255),

IN f_end_time VARCHAR(255)

)

BEGIN

DECLARE i_con1 TINYINT DEFAULT 0;

DECLARE i_code CHAR(5) DEFAULT '00000';

DECLARE i_msg TEXT;

DECLARE i_rows INT;

DECLARE i_con1 CONDITION FOR 1048; --This error code indicates that the field limit cannot be NULL.

DECLARE CONTINUE HANDLER FOR i_con1

BEGIN

SET i_con1 = 1;

get diagnostics CONDITION 1

i_code = returned_sqlstate, i_msg = message_text;

END; www.2cto.com

UPDATE t_datetime

SET log_time = IFNULL(f_log_time,NULL),

end_time = IFNULL(f_end_time,NULL)

WHERE id = f_id;

IF i_con1 = 0 THEN

get diagnostics i_rows = ROW_COUNT;

SET @i_result = CONCAT("Update succeeded, affected ", i_rows,'. ');

ELSE

SET @i_result = CONCAT('Update failed, error code is 1042, related message is ',i_msg,'. ');

END IF;

SELECT @i_result;

END$$

DELIMITER ;

Let's execute the stored procedure above and save the error message to the SESSION variable @i_result. This makes it easier to perform various outputs later. www.2cto.com

[sql]

CALL sp_do_update(1,NOW(),DATE_ADD(NOW(),INTERVAL 1 DAY));

Result.

Update succeeded, affected 1.

CALL sp_do_update(1,NULL,NULL);

Result.

Update failed, error code is 1042, related message is Column 'log_time' cannot be null.

The above is "MySQL in reading exception error buffer what are the methods to improve" all the content of this article, thank you for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.