MySQL Sql_mode parsing and setting, SQL file Import error Resolution

An error occurred while inserting a set of data into the MySQL database! The database is relentless. Error: Error 1365 (22012): division by 0; meaning: You cannot insert the result of an operation with a divisor of 0 in the previous database. So go to Google, and finally understand the reason: because the MySQL sql_mode mode restricts some so-called ' illegal ' operation. Analytical

This sql_mode, in short, is: it defines the SQL syntax that you should support for MySQL, the checksum of the data, and so on.

First, how to view the Sql_mode used by the current database:

1. mysql> SELECT @ @sql_mode;

The following is the current mode of my database:

Second, the meaning of Sql_mode value:

Only_full_group_by:

For a group by aggregation operation, if the column in select does not appear in group by, the SQL is considered illegal because the column is not in the GROUP BY clause

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 made on the non-transactional table

No_zero_in_date:

In strict mode, the month or day part is not accepted as a 0 date. If you use the Ignore option, we insert ' 0000-00-00 ' for a similar date. In non-strict mode, the date can be accepted, but a warning is generated.

No_zero_date:

In strict mode, do not make ' 0000-00-00 ' a legal date. You can still insert the 0 period with the Ignore option. In non-strict mode, the date can be accepted, but a warning is generated

Error_for_division_by_zero:

In strict mode, if 0 is removed (or mod (x,0)) during insert or update, an error is generated (otherwise, warning). If this mode is not given, MySQL returns null when 0 is removed. If you use Insert ignore or update ignore, the MySQL build is 0 apart from the warning, but the result of the operation is null.

No_auto_create_user

Prevents grant from automatically creating new users unless a password is also specified.

No_engine_substitution:

Throws an error if the required storage engine is disabled or not compiled. When this value is not set, the default storage engine is substituted and an exception is thrown

There are also some, here only to my local current value to do interpretation analysis .....

Third, it is said that the MySQL5.0 version supports three sql_mode modes: ANSI, Traditional, and strict_trans_tables.

1. ANSI mode: Relaxed mode, changing syntax and behavior to make it more compliant with standard SQL. Checks the inserted data and, if it does not conform to the defined type or length, adjusts or truncates the data type to save the report warning warning. For the error mentioned at the beginning of this article, you can set Sql_mode to ANSI mode so that you can insert data, and the database will replace the value of the field with a null value for the result of a divisor of 0.

Set the current database mode to ANSI mode:

1. Mysql> SET @ @sql_mode =ansi;

2, Traditional mode: strict mode, when inserting data to the MySQL database, the strict verification of data, to ensure that the error data can not be inserted, report error errors, not just warnings. When used in things, things are rolled back. Note: Once you find an error, discard insert/update immediately. If you use a non-transactional storage engine, this is not what you want, because the data changes made before the error do not "scroll" and the result is "only part of the update".

Set the current database mode to traditional mode:

1. Mysql> SET @ @sql_mode =traditional;

3, Strict_trans_tables mode: Strict mode, the strict calibration of data, error data can not be inserted, reported error errors. If the given value cannot be inserted into the transaction table, the statement is discarded. For non-transactional tables, if the value appears in line 1th of a single statement or multiline statement, the statement is discarded.

Set the current database mode to Strict_trans_tables mode:

1. Mysql> SET @ @sql_mode =strict_trans_tables;

There is no best with the worst model, only the most suitable pattern. Need to according to their actual situation to choose the most suitable mode!!!

In addition, the change of database mode here is the session level, one-time, closed and then open will not count!!!

You can also configure file settings by: vim/etc/my.cnf
Add the following configuration in My.cnf (My.ini):
[Mysqld]
Sql_mode= ' The pattern you want '

MySQL Sql_mode parsing and setting, SQL file Import error Resolution