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

最新推荐文章于 2024-11-10 09:15:00 发布

coco3600

最新推荐文章于 2024-11-10 09:15:00 发布

阅读量772

点赞数

文章标签：数据库面试

【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。

About Me

.............................................................................................................................................

● 本文作者：小麦苗，部分内容整理自网络，若有侵权请联系小麦苗删除

● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新

● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址：http://www.cnblogs.com/lhrbest

● 本文pdf版、个人简介及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/

● DBA宝典今日头条号地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826

.............................................................................................................................................

● QQ群号：230161599（满）、618766405

● 微信群：可加我微信，我拉大家进群，非诚勿扰

● 联系我请加QQ好友（646634621），注明添加缘由

● 于 2017-12-01 09:00 ~ 2017-12-31 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解

● 版权所有，欢迎分享本文，转载请保留出处

.............................................................................................................................................

● 小麦苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 小麦苗出版的数据库类丛书：http://blog.itpub.net/26736162/viewspace-2142121/

.............................................................................................................................................

使用微信客户端扫描下面的二维码来关注小麦苗的微信公众号（xiaomaimiaolhr）及QQ群（DBA宝典），学习最实用的数据库技术。

小麦苗的微信公众号小麦苗的DBA宝典QQ群2 《DBA笔试面宝典》读者群小麦苗的微店

.............................................................................................................................................