Shulou(Shulou.com)06/01 Report--

There was an error when inserting a set of data into the MySQL database last night! The database ruthlessly reported an error to me: ERROR 1365 (22012): Division by 0; it means: you cannot insert the result of an operation with a divisor of 0 in the database. So I went to Google and finally understood why: because MySQL's sql_mode mode restricts some of the so-called "illegal" operations.

Analysis

This sql_mode, in short, defines the sql syntax that your MySQL should support, validation of data, and so on.

How to view the sql_mode used by the current database:

Mysql > select @ @ sql_mode

The following is the current schema of my database:

The MySQL server can work in different SQL modes and can apply these modes in different ways for different clients. In this way, the application can tailor the server operation to meet its own needs. Such schemas 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. It is also possible to use set [session | GLOBAL] sql_mode='mode1,mode2 after MySQL 4.1or after startup.' Statement to change the mode by setting the sql_mode variable.

Common value of sql_mode

ONLY_FULL_GROUP_BY

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

For illegal SQL statements, the following error is reported during execution

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 the insertion of self-growing columns. By default, inserting 0 or NULL means the next self-growth value is generated. This option is useful if the user wants to insert a value of 0 and the column is self-growing.

STRICT_TRANS_TABLES

In this mode, if a value cannot be inserted into an event list, the current operation is interrupted and there are no restrictions on the non-item list.

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 can be accepted, but a warning is generated.

NO_ZERO_DATE

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

ERROR_FOR_DIVISION_BY_ZERO

During the INSERT or UPDATE process, if the data is divided by zero, an error is generated instead of a warning. If the pattern is not given, MySQL returns NULL when the data is divided by zero

NO_AUTO_CREATE_USER

Prohibit GRANT from creating users with empty passwords

NO_ENGINE_SUBSTITUTION

If the required storage engine is disabled or uncompiled, an error is thrown. When this value is not set, it is replaced with the default storage engine and an exception is thrown

PIPES_AS_CONCAT

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

ANSI_QUOTES

When ANSI_QUOTES is enabled, you cannot refer to a string in double quotes because it is interpreted as an identifier

MySQL5.0 and above support three sql_mode modes: ANSI, TRADITIONAL, and STRICT_TRANS_TABLES.

1. ANSI mode: loose mode, change the syntax and behavior to make it more in line with the standard SQL. Check the inserted data, if it does not meet the defined type or length, adjust or truncate the data type and save it, and report a warning warning. For the errors 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 value of the result divided by 0, the database will replace it with a null value.

2. TRADITIONAL mode: strict mode, when inserting data into mysql database, strictly checking the data to ensure that the wrong data can not be inserted, reporting error errors, not just warnings. When used in things, things will be rolled back. Note: discard INSERT/UPDATE as soon as an error is found. If you use a non-transactional storage engine, this approach is not what you want, because the data changes made before the error are not scrolled, and the result is that the update is "only partially carried out".

3. STRICT_TRANS_TABLES mode: strict mode, strict verification of data, error data cannot be inserted, error error is reported. If the given value cannot be inserted into the transaction table, the statement is discarded. For a non-transactional table, if the value appears in the first row of a single-line statement or multiple-row statement, the statement is discarded.

Set up 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 global sql_mode settings:

Mysql > select @ @ global.sql_mode

Set global. You need to reconnect before it takes effect.

Mysql > set global sql_mode='NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE'

Set the global sql_mode to 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, restarting the MySQL service takes effect.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

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.