Set SQL _mode of mysql

Author: skate

Time: 2013/04/11

Properly set SQL _mode of mysql

SQL _mode is a variable that is easily ignored. The default value is a null value. In this setting, some illegal operations can be allowed, such as the insertion of some illegal data. In the production environment, this value must be set to the strict mode. Therefore, databases in the development and test environments must also be set so that problems can be found during the development and test phase.

Common SQL _mode values are as follows:

ONLY_FULL_GROUP_BY:

For group by aggregation, if the column in SELECT does not appear in group by, this SQL statement is invalid because the column is not in the GROUP BY clause.

NO_AUTO_VALUE_ON_ZERO:

This value affects the insertion of auto-increment columns. By default, insert 0 or NULL to generate the next auto-growth value. If you want to insert a value of 0, and the column is auto-incrementing, this option is useful.

STRICT_TRANS_TABLES:

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

NO_ZERO_IN_DATE:

In strict mode, zero date and month are not allowed.

NO_ZERO_DATE:

Set this value. mysql databases do not allow zero-date insertion. If zero-date insertion is performed, an error is thrown instead of a warning.

ERROR_FOR_DIVISION_BY_ZERO:

In the INSERT or UPDATE process, if the data is divided by zero, an error is generated instead of a warning. If this mode is not provided, MySQL returns NULL if the data is divided by zero.

NO_AUTO_CREATE_USER:

Prohibit GRANT from creating users with blank passwords

NO_ENGINE_SUBSTITUTION:

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

PIPES_AS_CONCAT:

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

ANSI_QUOTES:

When ANSI_QUOTES is enabled, strings cannot be referenced using double quotation marks because it is interpreted as an identifier.

The SQL _mode settings in ORACLE are equivalent to PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

If mysql is used, you can set SQL _mode of mysql as follows to keep everyone's oracle usage habits:

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'