Set the SQL for MySQL

Author:skate

Time:2013/04/11

MySQL's sql_mode reasonable setting

Sql_mode is a very easy to ignore variable, the default value is null, in this setting can allow some illegal operations, such as allowing some illegal data insertion. This value must be set to strict mode in the production environment, so the database for developing and testing the environment must also be set so that problems can be found during the development testing phase

Sql_mode common values are as follows:

Only_full_group_by:

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

No_auto_value_on_zero:

This value affects inserts from the growth 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 the non-transaction table is not restricted

No_zero_in_date:

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

No_zero_date:

Set this value, the MySQL database is not allowed to insert the 0 period, insert the 0 period will throw an error instead of a warning.

Error_for_division_by_zero:

In the INSERT or update process, if the data is removed by 0, an error instead of a warning is generated. If the pattern is not given, MySQL returns null when data is removed by 0

No_auto_create_user:

Prohibit grant from creating a user with a blank password

No_engine_substitution:

If the desired storage engine is disabled or not compiled, an error is thrown. When this value is not set, replace with the default storage engine and throw an exception

Pipes_as_concat:

The "| |" The concatenation operator, rather than the operator, as a string, is the same as the Oracle database and is similar to the concatenation function concat of strings

Ansi_quotes:

When Ansi_quotes is enabled, you cannot refer to a string with double quotes 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 are using MySQL, you can set the MySQL Sql_mode to continue to retain the custom of using Oracle:

Add the following configuration to 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 '