MySQL sql_mode analysis and setting explanation

When inserting a set of data into the MySQL database last night, an error occurred! The database ruthlessly reported an error to me: ERROR 1365（22012）：Division by 0 ; which means: you cannot insert the result of an operation with a division by 0 into the database. So I went to Google and finally figured out the reason: it was because MySQL's sql_mode mode restricted some so-called 'illegal' operations.

Analysis

In short, sql_mode defines the SQL syntax that MySQL should support, data validation, etc. .

How to view the sql_mode used by the current database:

mysql> select @@sql_mode;

Here is the current schema of my database:

The MySQL server can operate in different SQL modes and can apply these modes in different ways for different clients. This allows applications to tailor server operations to meet their needs. Such modes define the SQL syntax that MySQL should support and what validation checks should be performed on the data. This makes it easier to use MySQL with other database servers in many different environments. Starting with MySQL 4.1, you can also change the mode by setting the sql_mode variable after startup using SET[SESSION|GLOBAL]sql_mode='mode1,mode2… ' statement.

sql_mode Common Values

ONLY_FULL_GROUP_BY

For GROUP BY aggregation operations, if the columns in the SELECT, HAVING, or ORDER BY clauses do not appear in GROUP BY, then the SQL is illegal.

For illegal SQL statements, the following error will be reported when executed:

ERROR 1055 (42000): Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'postscan.verifyDelayLog.auditor' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

NO_AUTO_VALUE_ON_ZERO

This value affects inserts into auto-increment columns. By default, inserting 0 or NULL will generate the next auto-increment value. This option is useful if the user wants to insert a value of 0 and the column is auto-incrementing.

STRICT_TRANS_TABLES

In this mode, if a value cannot be inserted into a transaction table, the current operation is interrupted, and no restrictions are imposed on non-transaction tables.

NO_ZERO_IN_DATE

In strict mode, dates with a month or day part of 0 are not accepted. If we use the IGNORE option, we insert '0000-00-00' for similar dates. In non-strict mode, the date is accepted but a warning is generated.

NO_ZERO_DATE

In strict mode, do not consider '0000-00-00' as a valid date. You can still insert zero dates with the IGNORE option. In non-strict mode, the date is accepted but a warning is generated.

ERROR_FOR_DIVISION_BY_ZERO

During an INSERT or UPDATE, if data is divided by zero, an error is generated rather than a warning. If this mode is not given, MySQL returns NULL if the value is divided by zero.

NO_AUTO_CREATE_USER

Prevent GRANT from creating users with empty passwords

NO_ENGINE_SUBSTITUTION

If a required storage engine is disabled or not compiled in, an error is thrown. If this value is not set, the default storage engine is used instead and an exception is thrown.

PIPES_AS_CONCAT

Treat "||" as a string concatenation operator rather than an OR operator, which is the same as Oracle database and similar to the string concatenation function Concat

ANSI_QUOTES

When ANSI_QUOTES is enabled, you cannot use double quotes to quote strings, because they are interpreted as identifiers.

MySQL 5.0 and above versions support three sql_mode modes: ANSI, TRADITIONAL, and STRICT_TRANS_TABLES.

1. ANSI mode: loose mode, changes syntax and behavior to make it more consistent with standard SQL. Verify the inserted data. If it does not conform to the defined type or length, adjust the data type or truncate it before saving, and issue a warning. For the error mentioned at the beginning of this article, you can first set sql_mode to ANSI mode, so that you can insert data, and for the field values ​​​​resulting from the divisor being 0, the database will replace them with NULL values.

2. TRADITIONAL mode: Strict mode. When inserting data into the MySQL database, strict data verification is performed to ensure that incorrect data cannot be inserted and an error is reported instead of just a warning. When used for transactions, the transaction will be rolled back. Note: Abort the INSERT/UPDATE immediately if an error is found. This is not what you want if you are using a non-transactional storage engine, because data changes made before the error will not be "rolled over", resulting in "partially executed" updates.

3. STRICT_TRANS_TABLES mode: Strict mode, strict data verification is performed, incorrect data cannot be inserted, and an error is reported. If the given value cannot be inserted into the transaction table, the statement is aborted. For nontransactional tables, if the value appears in a single-row statement or in row 1 of a multi-row statement, the statement is abandoned.

Setting sql_mode

View the sql mode of the current connection session:

mysql> select @@session.sql_mode;

Or from the environment variable

mysql> show variables like "sql_mode";

View the global sql_mode setting:

mysql> select @@global.sql_mode;

Set global, you need to reconnect to take effect

mysql> set global sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE';

Setting the global sql_mode can take effect without restarting MySQL

Settings in the configuration file

sql_mode=NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE

After modifying the configuration file, restart the MySQL service to take effect

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM. If you want to learn more about this, please check out the following links