【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寶典今日頭條號地址： ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2017-12-01 09:00 ~ 2017-12-31 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店： ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群2     《DBA筆試面寶典》讀者群       小麥苗的微店 .............................................................................................................................................