(go) MySQL sql_mode reasonable setup

MySQL Sql_mode reasonably set directory http://xstarcd.github.io/wiki/MySQL/MySQL-sql-mode.html

• Http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html
• http://blog.csdn.net/wyzxg/article/details/8787878

Current Sql-mode settings

• View current Sql-mode

  12	SELECT@@GLOBAL.sql_mode;SELECT@@SESSION.sql_mode;

Mysql> SELECT @ @GLOBAL. sql_mode;+--------------------------------------------+| @ @GLOBAL. Sql_mode                          |+--------------------------------------------+| Strict_trans_tables,no_engine_substitution |+--------------------------------------------+1 row in Set (0.00 sec) Mysql> SELECT @ @SESSION. sql_mode;+--------------------------------------------+| @ @SESSION. Sql_mode                         |+--------------------------------------------+| Strict_trans_tables,no_engine_substitution |+--------------------------------------------+1 row in Set (0.00 sec)

• Set Current Sql-mode

12	SETGLOBAL sql_mode = ‘modes...‘;SETSESSION sql_mode = ‘modes...‘;

• Configuring Sql-mode in MY.CNF

1234	[mysqld]#set the SQL mode to strict#sql-mode="modes..." sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Sql_mode Common values

• Only_full_group_by:

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

• No_auto_value_on_zero:

This value affects the insertion of the self-growing column. By default, inserting 0 or null represents the generation of the next self-growth value. 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 a transaction table, the current operation is interrupted and no restriction is made on the non-transactional table

• No_zero_in_date:

In strict mode, the date and month are not allowed to zero

• No_zero_date:

Setting this value, the MySQL database does not allow the 0 period to be inserted, and inserting the 0 period throws an error instead of a warning.

• Error_for_division_by_zero:

During the insert or update process, if the data is removed by 0, an error is generated instead of a warning. If the pattern is not given, then MySQL returns NULL when the data is removed by 0

• No_auto_create_user:

Disallow grant to create a user with a blank password

• 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

• Pipes_as_concat:

• Will "| |" The concatenation operator, rather than the operator, that is treated as a string, 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 use double quotation marks to refer to a string because it is interpreted as a qualifier

Oracle's Sql_mode settings are equivalent: Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, NO_FIELD_OPTIONS, No_auto_create_user.

If you use MySQL, in order to continue to keep everyone's use of Oracle, you can set the Sql_mode for MySQL as follows: Add the following configuration in MY.CNF

[Mysqld]sql_mode= ' Only_full_group_by,no_auto_value_on_zero,strict_trans_tables,no_zero_in_date,no_zero_date, Error_for_division_by_zero,no_auto_create_user,no_engine_substitution,pipes_as_concat,ansi_quotes '

(go) MySQL sql_mode reasonable setup