Mysql strict mode

From Wiki
Jump to: navigation, search

Setting the SQL Mode

The default SQL mode is empty (no modes set).

Note: As of MySQL 5.6.6 the default mode is NO_ENGINE_SUBSTITUTION; in MySQL 5.6.5 and earlier, it was empty (no modes set).

To set the SQL mode at server startup, use the --sql-mode="modes" option on the command line, or sql-mode="modes" in an option file such as my.cnf (Unix operating systems) or my.ini (Windows). modes is a list of different modes separated by commas. To clear the SQL mode explicitly, set it to an empty string using --sql-mode="" on the command line, or sql-mode="" in an option file.

Note: MySQL installation programs may configure the SQL mode during the installation process. If the SQL mode differs from the default or from what you expect, check for a setting in an option file that the server reads at startup.

To change the SQL mode at runtime, set the global or session sql_mode system variable using a SET statement:

SET GLOBAL sql_mode = 'modes';
SET SESSION sql_mode = 'modes';

For example, to *disable* strict mode set the sql_mode to NO_ENGINE_SUBSTITUTION.

SET GLOBAL sql_mode = 'NO_ENGINE_SUBSTITUTION';

To make this change permanent edit the /usr/my.cnf or /etc/my.cnf file. /usr/my.cnf *overrides* /etc/my.cnf.

Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Each client can change its session sql_mode value at any time.

To determine the current global or session sql_mode value, use the following statements:

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

See also: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html