Movatterモバイル変換

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 asINSERT,UPDATE, andLOAD DATA as well as DDL statements such asCREATE TABLE andALTER TABLE.

TheLIMIT clause has the same syntax as for theSELECT statement. SeeSection 13.2.9, “SELECT Statement”.

SHOW WARNINGS is also used followingEXPLAIN, to display the extended information generated byEXPLAIN. SeeSection 8.8.3, “Extended EXPLAIN Output Format”.

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).

TheSHOW COUNT(*) WARNINGS diagnostic statement displays the total number of errors, warnings, and notes. You can also retrieve this number from thewarning_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 aSELECT 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), andSHOW COUNT(*) ERRORS statement displays the total number of errors. SeeSection 13.7.5.17, “SHOW ERRORS Statement”.GET DIAGNOSTICS can be used to examine information for individual conditions. SeeSection 13.6.7.3, “GET DIAGNOSTICS Statement”.

Here is a simple example that shows data-conversion warnings forINSERT. The example assumes that strict SQL mode is disabled. With strict mode enabled, the warnings would become errors and terminate theINSERT.

mysql> CREATE TABLE t1 (a TINYINT NOT NULL, b CHAR(4));Query OK, 0 rows affected (0.05 sec)mysql> INSERT INTO t1 VALUES(10,'mysql'), (NULL,'test'), (300,'xyz');Query OK, 3 rows affected, 3 warnings (0.00 sec)Records: 3  Duplicates: 0  Warnings: 3mysql> SHOW WARNINGS\G*************************** 1. row ***************************  Level: Warning   Code: 1265Message: Data truncated for column 'b' at row 1*************************** 2. row ***************************  Level: Warning   Code: 1048Message: Column 'a' cannot be null*************************** 3. row ***************************  Level: Warning   Code: 1264Message: Out of range value for column 'a' at row 33 rows in set (0.00 sec)

Themax_error_count system variable controls the maximum number of error, warning, and note messages for which the server stores information, and thus the number of messages thatSHOW WARNINGS displays. To change the number of messages the server can store, change the value ofmax_error_count. The default is 64.

max_error_count controls only how many messages are stored, not how many are counted. The value ofwarning_count is not limited bymax_error_count, even if the number of messages generated exceedsmax_error_count. The following example demonstrates this. TheALTER TABLE statement produces three warning messages (strict SQL mode is disabled for the example to prevent an error from occuring after a single conversion issue). Only one message is stored and displayed becausemax_error_count has been set to 1, but all three are counted (as shown by the value ofwarning_count):

mysql> SHOW VARIABLES LIKE 'max_error_count';+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_error_count | 64    |+-----------------+-------+1 row in set (0.00 sec)mysql> SET max_error_count=1, sql_mode = '';Query OK, 0 rows affected (0.00 sec)mysql> ALTER TABLE t1 MODIFY b CHAR;Query OK, 3 rows affected, 3 warnings (0.00 sec)Records: 3  Duplicates: 0  Warnings: 3mysql> SHOW WARNINGS;+---------+------+----------------------------------------+| Level   | Code | Message                                |+---------+------+----------------------------------------+| Warning | 1263 | Data truncated for column 'b' at row 1 |+---------+------+----------------------------------------+1 row in set (0.00 sec)mysql> SELECT @@warning_count;+-----------------+| @@warning_count |+-----------------+|               3 |+-----------------+1 row in set (0.01 sec)

To disable message storage, setmax_error_count to 0. In this case,warning_count still indicates how many warnings occurred, but messages are not stored and cannot be displayed.

Thesql_notes system variable controls whether note messages incrementwarning_count and whether the server stores them. By default,sql_notes is 1, but if set to 0, notes do not incrementwarning_count and the server does not store them:

mysql> SET sql_notes = 1;mysql> DROP TABLE IF EXISTS test.no_such_table;Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> SHOW WARNINGS;+-------+------+------------------------------------+| Level | Code | Message                            |+-------+------+------------------------------------+| Note  | 1051 | Unknown table 'test.no_such_table' |+-------+------+------------------------------------+1 row in set (0.00 sec)mysql> SET sql_notes = 0;mysql> DROP TABLE IF EXISTS test.no_such_table;Query OK, 0 rows affected (0.00 sec)mysql> SHOW WARNINGS;Empty set (0.00 sec)

The MySQL server sends to each client a count indicating the total number of errors, warnings, and notes resulting from the most recent statement executed by that client. From the C API, this value can be obtained by callingmysql_warning_count(). Seemysql_warning_count().

In themysql client, you can enable and disable automatic warnings display using thewarnings andnowarning commands, respectively, or their shortcuts,\W and\w (seeSection 4.5.1.2, “mysql Client Commands”). For example:

mysql> \WShow warnings enabled.mysql> SELECT 1/0;+------+| 1/0  |+------+| NULL |+------+1 row in set, 1 warning (0.03 sec)Warning (Code 1365): Division by 0mysql> \wShow warnings disabled.