Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use MySQL Strict SQL MODE". In daily operation, I believe many people have doubts about how to use MySQL Strict SQL MODE. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts about "how to use MySQL Strict SQL MODE"! Next, please follow the editor to study!

The strict SQL schema controls how MySQL handles invalid or missing values in data change statements (insert or update). A value may be invalid for a variety of reasons. For example, it has the wrong data type for the column, or it is out of range of the column. Missing values occur when a new record is inserted and a column that is not NULL and does not explicitly specify a DEFAULT clause when defined does not contain a value. (for a NULL column, null values are inserted if missing values.) strict SQL schemas also affect DDL statements such as create table.

If the strict SQL mode does not work, MySQL inserts an adjustment value and generates a warning for invalid or missing values. In strict SQL mode, this behavior can be generated by using insert ignore or udpate ignore.

For statements such as select that do not change the data, invalid values in strict SQL mode generate a warning rather than an error.

Strict SQL mode generates an error when trying to create a key value that exceeds the maximum key length of the column. When strict SQL mode is not enabled, a warning is generated and the length of the key value is truncated to meet the maximum key length.

Strict SQL mode does not affect whether foreign key constraints are checked. Foreign_key_checks can be used.

Strict SQL mode will take effect if STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled, but the impact of these modes will be different:

. For transaction tables, an error occurs when the data is invalid or lost when STRICT_ALL_TABLES or STRICT_TRANS_TABLES is enabled. The statement is terminated and rolled back.

. For non-transactional tables, if the first row of records in an insert or update statement has bad values, the behavior of these patterns is the same: the statement is terminated and the table remains the same. If the statement inserts or modifies multiple rows of records and bad values occur in the second or subsequent row records, the result depends on whether strict SQL mode is enabled.

-an error is returned for STRICT_ALL_TABLES,MySQL and the remaining row records are ignored. However, because earlier row records have been inserted or updated, this will result in partial updates. To avoid this problem, using a single-line statement terminates without changing the table data.

-for STRICT_TRANS_TABLES,MySQL, an invalid value is converted to the nearest valid value and the adjustment value is inserted. If this value will be lost, MySQL inserts the implicit default value. In this case, MySQL generates a warning instead of an error and continues to process the statement.

The strict SQL mode handles the division of zeros, zero dates, and zeros in dates as follows:

. The strict SQL pattern affects the processing of division by zero, which includes MOD (Nd0): for data change operations (insert,update):

-if strict SQL mode is not enabled, dividing by zero inserts NULL and generates a warning.

-if strict SQL mode is enabled, dividing by zero generates an error unless ignore is specified. For insert ignore and update ignore operations, the division by zero operation inserts NULL and generates a warning.

For select, the division by zero operation returns NULL. Enabling strict SQL mode causes a warning.

. Strict SQL mode affects whether the server allows' 0000-00' as a valid date:

-if strict SQL mode is not enabled, '0000-00-00' is allowed and the insert operation does not generate a warning.

-if strict SQL mode is enabled, '0000-00' is not allowed and the insert operation will result in an error unless you specify ignore. For insert ignore or update ignore,'0000-00-00' is allowed and the insert operation generates a warning.

. Strict SQL mode affects whether the server allows non-zero year portions of the date, but zero for month and day portions (for example, '2010-00-01' or '2010-01-00'):

-if strict SQL mode is not enabled, zero dates are allowed and the insert operation does not generate a warning.

-if strict SQL mode is enabled, zero dates are not allowed and the insert operation produces an error unless ignore is specified. For insert ignore or update ignore, a date with zero will be inserted in the form of '0000-00-00' and generate a warning.

Comparison between IGNORE keyword and strict SQL mode

Here is a comparison between the IGNORE keyword (which demotes an error to a warning) and the strict SQL mode (which escalates warnings to errors) when the statement is executed. Describe which statements they affect and what errors they have in applying them.

The influence of IGNORE on statement execution

Some statements in MySQL support the optional IGNORE keyword. This keyword will cause the server to downgrade some types of errors and generate warnings. For multiline statements, IGNORE causes the statement to jump to the next line instead of aborting.

For example, if table T2 has a primary key column I, trying to insert the same I value in multiple rows of records will normally result in a duplicate key error:

Mysql > insert into T2 (I) values (1), (1); ERROR 1062 (23000): Duplicate entry'1' for key 'PRIMARY'

When using the IGNORE keyword, records containing duplicate key values are still not inserted, but warnings are used instead of errors:

Mysql > insert ignore into T2 (I) values (1), (1); Query OK, 1 row affected, 1 warning (0.15 sec) Records: 2 Duplicates: 1 Warnings: 1mysql > show warnings +-+ | Level | Code | Message | +-- -+ | Warning | 1062 | Duplicate entry'1' for key 'PRIMARY' | | Warning | 1062 | Duplicate entry' 1' for key 'PRIMARY' | +-+ 2 rows in set (0.00 sec)

The following statement supports the IGNORE keyword:

.create table... Select:ignore cannot be applied to the create table or select portion of a statement, but it can be applied to a record provided by a select statement to execute an insert statement. Records that repeat for unique key values are discarded.

.delete: ignore causes MySQL to ignore errors when processing deleting records.

.insert: with ignore, records that repeat for unique key values are discarded. Row records for duplicate key values cause the data to be converted to the nearest valid value to be inserted.

For partition tables, when a partition that does not match the specified value is found, ignore causes the insert operation to fail that contains records that do not match.

.load data,load xml: with ignore, records that repeat for unique key values are discarded.

.update: with ignore, records that have duplicate key-value conflicts at unique key values are not updated. The updated record may cause the data to be converted to the nearest valid value to be inserted.

The ignore keyword applies to the following error:

ER_BAD_NULL_ERROR

ER_DUP_ENTRY

ER_DUP_ENTRY_WITH_KEY_NAME

ER_DUP_KEY

ER_NO_PARTITION_FOR_GIVEN_VALUE

ER_NO_PARTITION_FOR_GIVEN_VALUE_SILENT

ER_NO_REFERENCED_ROW_2

ER_ROW_DOES_NOT_MATCH_GIVEN_PARTITION_SET

ER_ROW_IS_REFERENCED_2

ER_SUBQUERY_NO_1_ROW

ER_VIEW_CHECK_FAILED

The influence of strict SQL Mode on sentence execution

The MySQL server can operate in different SQL modes and can apply these different modes to different guest ends, depending on the sql_mode system variable. In strict SQL mode, the server escalates specific warnings to errors.

For example, in non-strict SQL mode, the result of inserting the string 'abc' into an integer type column converts the string value to 0 and generates a warning:

Mysql > SET sql_mode =''; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > insert into T2 (I) values ('abc'); Query OK, 1 row affected, 1 warning (0.02 sec) mysql > show warnings +-+ | Level | Code | Message | +-+- -+-+ | Warning | 1366 | Incorrect integer value: 'abc' for column 'i' at row 1 | + -- + 1 row in set (0.00 sec)

In strict SQL mode, invalid values are rejected and an error is generated:

Mysql > SET sql_mode = 'STRICT_ALL_TABLES';Query OK, 0 rows affected, 1 warning (0.00 sec) mysql > insert into T2 (I) values (' abc'); ERROR 1366 (HY000): Incorrect integer value: 'abc' for column 'i' at row 1

Under certain conditions, some values may be out of range or invalid rows may be inserted or deleted from the table, and strict SQL mode applies to the following statements:

Alter table

Create table

Create table... Select

Delete (single table and multiple tables)

Insert

Load data

Load xml

Select sleep ()

Update (single table and multiple tables)

In a stored program, if the program is defined when strict mode takes effect, the statements of the types just listed will be executed in strict SQL mode.

The strict SQL pattern applies to the following errors, indicating that the input value may be invalid or missing. If the wrong data type is used for the column value or exceeds the value

The range then the value is invalid. If the new row being inserted does not contain a NOT NULL column value but except that the DEFAULT clause is explicitly specified at the time of the column definition, then

It's a missing value.

ER_BAD_NULL_ERROR

ER_CUT_VALUE_GROUP_CONCAT

ER_DATA_TOO_LONG

ER_DATETIME_FUNCTION_OVERFLOW

ER_DIVISION_BY_ZERO

ER_INVALID_ARGUMENT_FOR_LOGARITHM

ER_NO_DEFAULT_FOR_FIELD

ER_NO_DEFAULT_FOR_VIEW_FIELD

ER_TOO_LONG_KEY

ER_TRUNCATED_WRONG_VALUE

ER_TRUNCATED_WRONG_VALUE_FOR_FIELD

ER_WARN_DATA_OUT_OF_RANGE

ER_WARN_NULL_TO_NOTNULL

ER_WARN_TOO_FEW_RECORDS

ER_WRONG_ARGUMENTS

ER_WRONG_VALUE_FOR_TYPE

WARN_DATA_TRUNCATED

At this point, the study on "how to use MySQL Strict SQL MODE" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.