【MySQL】SHOW WARNINGS和SHOW ERRORS的作用是什么?

真题1、SHOW WARNINGS和SHOW ERRORS的作用是什么?

答案:SHOW WARNINGS可以显示上一个命令的警告信息,SHOW ERRORS可以显示上一个命令的错误信息。其它用法见下表:

 

命令

解释

语法命令

SHOW WARNINGS [LIMIT [offset,] row_count]

查看警告信息的语法。

SHOW ERRORS [LIMIT [offset,] row_count]

查看错误信息的语法。

查看信息

SHOW WARNINGS

查看上一个命令的警告信息。

SHOW ERRORS

查看上一个命令的错误信息。

查看行数

SHOW COUNT(*) WARNINGS

SELECT @@warning_count;

查看上一个命令的警告数。

SHOW COUNT(*) ERRORS

SELECT @@error_count;

查看上一个命令的错误数。

参数

max_error_count

默认为64,控制可以记录的最大信息数,包括ERRORS和WARINGS。SHOW ERRORS和SHOW WARNINGS的显示结果不会超过该值,但是“SELECT @@error_count;”和“SELECT @@warning_count;”可以超过该值。可以设置该值为0来禁用信息存储,此时SHOW ERRORS和SHOW WARNINGS没有结果,但是“SELECT @@error_count;”和“SELECT @@warning_count;”依然有值。

sql_notes

控制是否记录错误和警告信息,默认为1,表示启用,0表示禁用。

是否自动显示警告信息的内容

\W或warnings

在每个SQL执行完后自动显示告警信息的内容。

\w或nowarning

默认值,在每个SQL执行完后不自动显示告警信息的内容,只显示数量。

 




   
官网:  
   https://dev.mysql.com/doc/refman/5.6/en/show-warnings.html  
https://dev.mysql.com/doc/refman/5.7/en/show-errors.html  




 mysql> ? SHOW WARNINGS;  
Name: 'SHOW WARNINGS'  
Description:  
Syntax:  
SHOW WARNINGS [LIMIT [offset,] row_count]  
SHOW COUNT(*) WARNINGS  


SHOW WARNINGS is a diagnostic statement that displays information about  
the conditions (errors, warnings, and notes) resulting from executing a  
statement in the current session. Warnings are generated for DML  
statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL  
statements such as CREATE TABLE and ALTER TABLE.  


The LIMIT clause has the same syntax as for the SELECT statement. See  
http://dev.mysql.com/doc/refman/5.7/en/select.html.  


SHOW WARNINGS is also used following EXPLAIN, to display the extended  
information generated by EXPLAIN. See  
http://dev.mysql.com/doc/refman/5.7/en/explain-extended.html.  


SHOW WARNINGS displays information about the conditions resulting from  
execution of the most recent nondiagnostic statement in the current  
session. If the most recent statement resulted in an error during  
parsing, SHOW WARNINGS shows the resulting conditions, regardless of  
statement type (diagnostic or nondiagnostic).  


The SHOW COUNT(*) WARNINGS diagnostic statement displays the total  
number of errors, warnings, and notes. You can also retrieve this  
number from the warning_count system variable:  


SHOW COUNT(*) WARNINGS;  
SELECT @@warning_count;  


A difference in these statements is that the first is a diagnostic  
statement that does not clear the message list. The second, because it  
is a SELECT statement is considered nondiagnostic and does clear the  
message list.  


A related diagnostic statement, SHOW ERRORS, shows only error  
conditions (it excludes warnings and notes), and SHOW COUNT(*) ERRORS  
statement displays the total number of errors. See [HELP SHOW ERRORS].  
GET DIAGNOSTICS can be used to examine information for individual  
conditions. See [HELP GET DIAGNOSTICS].  

 
URL: http://dev.mysql.com/doc/refman/5.7/en/show-warnings.html  

 



 
 mysql> ? show errors;  
Name: 'SHOW ERRORS'  
Description:  
Syntax:  
SHOW ERRORS [LIMIT [offset,] row_count]  
SHOW COUNT(*) ERRORS  


SHOW ERRORS is a diagnostic statement that is similar to SHOW WARNINGS,  
except that it displays information only for errors, rather than for  
errors, warnings, and notes.  


The LIMIT clause has the same syntax as for the SELECT statement. See  
http://dev.mysql.com/doc/refman/5.7/en/select.html.  


The SHOW COUNT(*) ERRORS statement displays the number of errors. You  
can also retrieve this number from the error_count variable:  


SHOW COUNT(*) ERRORS;  
SELECT @@error_count;  


SHOW ERRORS and error_count apply only to errors, not warnings or  
notes. In other respects, they are similar to SHOW WARNINGS and  
warning_count. In particular, SHOW ERRORS cannot display information  
for more than max_error_count messages, and error_count can exceed the  
value of max_error_count if the number of errors exceeds  
max_error_count.  


URL: http://dev.mysql.com/doc/refman/5.7/en/show-errors.html  


 
 





1、SHOW ERRORS 语句只是显示上一个语句的错误,不同时显示警告以及注意事项。  
举个例子:  

mysql> show dfdafsadf  
    -> ;  
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that  
corresponds to your MySQL server version for the right syntax to use near 'dfdaf  
sadf' at line 1  

这里就有个错误。关于如何显示她,已经很明显了。  

mysql> show errors  
    -> \G  
*************************** 1. row ***************************  
  Level: Error  
   Code: 1064  
Message: You have an error in your SQL syntax; check the manual that corresponds  
 to your MySQL server version for the right syntax to use near 'dfdafsadf' at li  
ne 1  
1 row in set (0.00 sec)  

如果一下子有好多错误,而你又想只显示第二条的话:  
show errorw limit 1,1;  
如果你想看到有错误的数目,前面的 1 rows in set 已经很明显了。  
不过还有办法:  
mysql> show count(*) errors;  
+-----------------------+  
| @@session.error_count |  
+-----------------------+  
|                     1 |  
+-----------------------+  
1 row in set (0.00 sec)  
注意:这里的count(*)不能写成count(1).  
你还可以这样:  
mysql> select @@error_count;  
+---------------+  
| @@error_count |  
+---------------+  
|             1 |  
+---------------+  
1 row in set (0.00 sec)  

2、SHOW WARNINGS 显示上一个语句的错误、警告以及注意。  
基本语法和SHOW ERRORS大同小异。  
不过要注意的是在MYSQL5后的大部分以前的WARNINGS直接被显示为ERRORS。