Configure operational characteristics of the MySQL server by using the SQL modes

SQL Modes

The SQL mode consists of optional values that control some aspect of query processing. When you set the SQL mode appropriately, a client can have some control over the following:

• Input data: SQL modes can be used tell the server how strict or forgiving to be about accepting input data.
• Standard SQL conformance:SQL modes can be used to enable or disable behaviors relating to standard SQL conformance.
• Compatibility:SQL modes can be used to provide better compatibility with other database systems.

Many server operation characteristics can be configured by setting the SQL mode:

• Specify how strict or forgiving MySQL is about accepting input data.
• Set compatibility with other database systems.
• Control query processing – Enable or disable behavior relating to SQL conformance.
• Override SQL “empty” default mode – Empty mode does not enable restrictions or conformance behaviors.

The default SQL mode is NO_ENGINE_SUBSTITUTION. The default configuration file adds STRICT_TRANS_TABLES.

Setting the SQL Mode

Individual clients can configure the SQL mode for their own requirements, but you can also set the default SQL mode at server startup with the –sql-mode option. You may want to do this to run the server with a mode that is more cautious about accepting invalid data or creating MySQL user accounts.

If no modifier is present, SET changes the session SQL mode. You can invoke the SET statement with an empty string or with one or more mode names separated by commas. If the value is empty or contains more than one mode name, you must quote the values. If the value contains a single mode name, quoting is optional. SQL mode values are not case-sensitive.

Here are some SET examples:

1. Set the SQL mode by using a single mode value:

SET sql_mode = ANSI_QUOTES;
SET sql_mode = 'TRADITIONAL';

2. Set the SQL mode by using multiple mode names:

sql_mode = 'IGNORE_SPACE,ANSI_QUOTES,NO_ENGINE_SUBSTITUTION';

3. Check the current sql_modesetting by using this SELECT statement:

SELECT @@sql_mode;

4. Set at startup from the command line:

shell> mysqld --sql-mode=[mode_value]

5. Set within an option file:

[mysqld]
sql-mode=IGNORE_SPACE

6. SET statement - Within mysql, after startup:

SET [SESSION|GLOBAL] sql_mode=[mode_value]

- Clear the current SQL mode:

SET sql_mode=''

Common SQL Modes

Below are some of the most commonly used SQL modes:

• STRICT_TRANS_TABLES, STRICT_ALL_TABLES:Without these modes, MySQL is forgiving with values that are missing, out of range, or malformed. Enabling STRICT_TRANS_TABLESsets “strict mode” for transactional tables; it is also enabled in the default my.cnf file. Enabling STRICT_ALL_TABLESsets strict mode for all tables.
• TRADITIONAL:Enable this SQL mode to enforce restrictions on input data values that are similar to those of other database servers. With this mode, using the GRANT statement to create users requires that you specify a password.
• IGNORE_SPACE:By default, you must invoke functions with no space between the function name and the following parenthesis. Enabling this mode allows such spaces, and causes function names to be reserved words.
• ERROR_FOR_DIVISION_BY_ZERO:By default, division by zero produces a result of NULL. A division by zero when inserting data with this mode enabled causes a warning, or an error in strict mode.
• ANSI:Use this composite mode to cause the MySQL server to be more “ANSI-like.” That is, it enables behaviors that are more like standard SQL, such as ANSI_QUOTES and PIPES_AS_CONCAT.
• NO_ENGINE_SUBSTITUTION:When you specify an unavailable storage engine while creating or altering a table, MySQL substitutes the default storage engine unless this mode is enabled. This is the default SQL mode.