[Original] mysql error buffer stack _ MySQL

What is an error buffer stack? For example, execute the following statement: mysql & gt; INSERTINTOt_datetimeVALUES (22007, 5); ERROR1292 (): Incorrectdatetimevalue: in 4forcolumnlog_timeatrow1, what is the error buffer stack in 1292? For example, execute the following statement:

mysql>INSERTINTOt_datetimeVALUES(2,'4','5');ERROR1292(22007):Incorrectdatetimevalue:'4'forcolumn'log_time'atrow1

Where is the error message indicated by the code 1292 stored? It is saved in the error buffer stack and called diagnostics area in MySQL. This concept has been updated only in MySQL5.7.

Before MySQL5.5, to obtain data in this region, you can only obtain data through the c api, which cannot be retrieved from the SQL layer. MySQL5.5 first introduced this concept.

After MySQL5.6 is released, you can not only search this region, but also re-encapsulate it to obtain the desired data. However, this area can only save the error code once and can be easily reset.

After MySQL5.7 is released, it is easier to search this region and put the data in a STACK. The reset conditions are looser. The following is an example.

The structure of the example table is as follows,

CREATETABLE`t_datetime`(`id`int(11)NOTNULL,`log_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,`end_time`datetimeNOTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;

The log table used to record error data.

CREATETABLEtb_log(errornoint,errortextTEXT,error_timestampDATETIME);

In the MySQL5.6 environment, I want to write a complicated piece of code to get the error message.

DELIMITER $ USE 'new _ feature '$ DROPPROCEDUREIFEXISTS 'sp _ do_insert' $ CREATEDEFINER = 'root' @ 'localhost' PROCEDURE 'sp _ do_insert '(INf_idINT, INf_log_timeVARCHAR (255), INf_end_timeVARCHAR (255) BEGINDECLAREdone1TINYINTDEFAULT0; -- save the boolean value of whether an exception occurs. DECLAREiTINYINTDEFAULT1; items; -- get the number of error data entries DECLAREv_errnoINTDEFAULT0; -- get error code DECLAREv_msgTEXT; -- get error details; -- define an exception processing block BEGINSETdone1 = 1; -- an exception occurs, set to 1. getdiagnosticsv_errcount = number; SETv_msg = ''; WHILEi <= region = MYSQL_ERRNO, v_msg = MESSAGE_TEXT; SET @ stmt = CONCAT ('select', v_errno, ',"', v_msg, '","', NOW (), '"into @ errno', I, ', @ Msg', I, ', @ log_timestamp', I, ';'); PREPAREs1FROM @ stmt; EXECUTEs1; SETi = I + 1; ENDWHILE; DROPPREPAREs1; END; INSERTINTOt_datetime (id, log_time, end_time) VALUES (f_id, f_log_time, f_end_time); IFdone1 = 1THEN -- records the error data to the tb_log table. SETi = 1; WHILEi <= v_errcountDOSET @ stmt = CONCAT ('insertintotb _ log'); SET @ stmt = CONCAT (@ stmt, 'Select @ errno', I ,', @ msg ', I,', @ log_timestamp '); PREPAREs1FROM @ stmt; EXECUTEs1; SETi = I + 1; ENDWHILE; DROPPREPAREs1; ENDIF; END $ DELIMITER;

After MySQL5.7 is released, you can now streamline my code.

DELIMITER $ USE 'new _ feature '$ DROPPROCEDUREIFEXISTS 'sp _ do_insert' $ CREATEDEFINER = 'root' @ 'localhost' PROCEDURE 'sp _ do_insert '(INf_idINT, INf_log_timeVARCHAR (255), INf_end_timeVARCHAR (255) BEGINDECLAREiTINYINTDEFAULT1; records; -- get the number of error data entries DECLAREv_errnoINTDEFAULT0; -- get the error code declarev_msg; -- get error details DECLARECONTINUEHANDLERFORSQLEXCEPTION -- define an exception handling block using = number; WHILEi <= handle -- save the error data in the variable v_errno = MYSQL_ERRNO, v_msg = MESSAGE_TEXT; INSERTINTOtb_logVALUES (v_errno, v_msg, NOW (); SETi = I + 1; ENDWHILE; END; INSERTINTOt_datetime (id, log_time, end_time) VALUES (f_id, f_log_time, f_end_time ); END $ DELIMITER;

Run the following command:

mysql>callsp_do_insert(2,'4','5');QueryOK,1rowaffected(0.01sec)

To retrieve the data of the table tb_log.

mysql>select*fromtb_log\G***************************1.row***************************errorno:1265errortext:Datatruncatedforcolumn'log_time'atrow1error_timestamp:2015-11-1711:53:10***************************2.row***************************errorno:1265errortext:Datatruncatedforcolumn'end_time'atrow1error_timestamp:2015-11-1711:53:10***************************3.row***************************errorno:1062errortext:Duplicateentry'2'forkey'PRIMARY'error_timestamp:2015-11-1711:53:103rowsinset(0.00sec)

To sum up, if you first use the diagnostics area, it is best to write code in the stored procedure to encapsulate the SQL statement.

The above is the content of [original] mysql error buffer stack _ MySQL. For more information, see PHP Chinese network (www.php1.cn )!