ApsaraDB RDS:Modify the sql_mode parameter of an ApsaraDB RDS for MySQL instance

Combined SQL mode

Description

DB2

Equivalent to PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS

MSSQL

Equivalent to PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS

POSTGRESQL

Equivalent to PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS

ORACLE

Equivalent to PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER

MAXDB

Equivalent to PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER

MYSQL323

Equivalent to the combination of HIGH_NOT_PRECEDENCE and SHOW CREATE TABLE that is specific to MYSQL323

• The TIMESTAMP column does not have the DEFAULT or ON UPDATE attribute.

• The string column does not have the character set or collation attribute. If binary collation is used for the CHAR or VARCHAR column, the binary type is supported for the column.

• The ENGINE=engine_name table option is displayed as TYPE=engine_name.

• For MEMORY tables, the storage engine is displayed as HEAP.

MYSQL40

Equivalent to the combination of HIGH_NOT_PRECEDENCE and SHOW CREATE TABLE that is specific to MYSQL323

• The TIMESTAMP column does not have the DEFAULT or ON UPDATE attribute.

• The string column does not have the character set or collation attribute. If binary collation is used for the CHAR or VARCHAR column, the binary type is supported for the column.

• The ENGINE=engine_name table option is displayed as TYPE=engine_name.

ANSI

Equivalent to REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ONLY_FULL_GROUP_BY

TRADITIONAL

• Equivalent to STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION for MySQL 5.7.3 and earlier versions and for MySQL 5.7.8 and later versions

• Equivalent to STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_AUTO_CREATE _USER,NO_ENGINE_SUBSTITUTION for MySQL 5.7.4 to MySQL 5.7.7

Field

Version

Description

ALLOW_INVALID_DATES

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Allows you to insert or update invalid date values that do not conform to date formats.

In default strict SQL mode, MySQL requires that date values conform to a specified format, such as YYYY-MM-DD. If the format of the date value that you want to insert or update is invalid, MySQL rejects the operation and reports an error.

If you set the sql_mode parameter to ALLOW_INVALID_DATES, MySQL allows you to insert or update an invalid date value that does not conform to the date format and does not report an error.

Examples:

Assume that a date field named birthday exists. In strict SQL mode, only values in the YYYY-MM-DD format are allowed. If you insert values in other formats, an error is reported. If you set the sql_mode parameter to ALLOW_INVALID_DATES, you can insert an invalid date value, such as 2022-13-45, for the field, and MySQL stores the inserted value as 0000-00-00. This setting may be required in some scenarios, such as scenarios in which you want to record invalid dates.

ANSI_QUOTES

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Enables the ANSI_QUOTES mode. In this mode, MySQL uses double quotation marks ("") or grave accents (``) to quote identifiers.

When the default value of the sql_mode parameter is used, MySQL uses single quotation marks ('') to quote string values and grave accents (``) to quote identifiers, such as table names and column names. Examples:

SELECT * FROM users WHERE `name` = 'John';

If you set the sql_mode parameter to ANSI_QUOTES, MySQL uses single quotation marks ('') to quote string values and double quotation marks ("") or grave accents (``) to quote identifiers to comply with the ANSI SQL standard. Examples:

SELECT * FROM "users" WHERE `name` = 'John';

The ANSI_QUOTES mode improves compatibility with the ANSI SQL standard. The mode can better ensure the portability and consistency of statements especially when MySQL interacts with other database systems.

ERROR_FOR_DIVISION_BY_ZERO

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Reports a warning or an error instead of returning NULL for a division-by-zero operation.

The ERROR_FOR_DIVISION_BY_ZERO mode affects division-by-zero operations, including MOD(N,0).

For data change operations, such as INSERT and UPDATE, the effect of the ERROR_FOR_DIVISION_BY_ZERO mode also depends on whether the strict SQL mode is enabled.

• If the ERROR_FOR_DIVISION_BY_ZERO mode is disabled, NULL is returned for a division-by-zero operation, and no warnings are reported.

• If the ERROR_FOR_DIVISION_BY_ZERO mode is enabled, NULL is returned for a division-by-zero operation, and a warning is reported.

• If the ERROR_FOR_DIVISION_BY_ZERO mode and the strict SQL mode are enabled, an error is reported for a division-by-zero operation unless IGNORE is also given. For INSERT IGNORE and UPDATE IGNORE operations, NULL is returned for a division-by-zero operation, and a warning is reported.

For SELECT operations, NULL is returned for a division-by-zero operation. If the ERROR_FOR_DIVISION_BY_ZERO mode is enabled, a warning is reported regardless of whether the strict SQL mode is enabled.

The ERROR_FOR_DIVISION_BY_ZERO mode is not part of the strict SQL mode. However, it must be used in conjunction with the strict SQL mode and is enabled by default. If the ERROR_FOR_DIVISION_BY_ZERO mode is enabled while the strict SQL mode is disabled or the strict SQL mode is enabled while the ERROR_FOR_DIVISION_BY_ZERO mode is disabled, a warning is reported.

HIGH_NOT_PRECDENCE SQL

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Increases the precedence of the NOT operator.

By default, comparison operators precede the NOT operator. For example, NOT a BETWEEN b AND c is parsed as NOT (a BETWEEN b AND c).

In earlier MySQL versions, NOT a BETWEEN b AND c is parsed as (NOT a) BETWEEN b AND c.

You can use the HIGH_NOT_PRECEDENCE SQL mode to increase the precedence of the NOT operator.

IGNORE_SPACE

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Allows spaces between a function name and the opening parenthesis (. This causes built-in function names to be treated as reserved words.

The IGNORE_SPACE mode applies to built-in functions, not to loadable functions or stored functions. Spaces are always allowed after the name of a loadable function or stored function regardless of whether the IGNORE_SPACE mode is enabled.

NO_AUTO_VALUE_ON_ZERO

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Disables the use of 0 as the default value of an auto-increment column.

By default, when you insert a row of data, if the value of its auto-increment column is set to 0, MySQL automatically replaces the value 0 with the next usable auto-increment value. If you set the sql_mode parameter to NO_AUTO_VALUE_ON_ZERO, MySQL does not allow you to set the default value of an auto-increment column to 0.

This mode prevents the default value of an auto-increment column from being accidentally set to 0. In some cases, if the default value of an auto-increment column is set to 0, data inconsistency or errors may occur.

Examples:

CREATE TABLE mytable (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO mytable (id, name) VALUES (0, 'John');

If the NO_AUTO_VALUE_ON_ZERO mode is disabled, MySQL automatically replaces the value of the id column with the next usable auto-increment value, which is 1. If you do not specify NO_AUTO_VALUE_ON_ZERO for the sql_mode parameter, the mode is disabled.

If you set the sql_mode parameter to NO_AUTO_VALUE_ON_ZERO, take note of the following items:

SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
CREATE TABLE mytable (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50)
);
INSERT INTO mytable (id, name) VALUES (0, 'John');

MySQL does not allow you to use 0 as the default value of auto-increment columns and reports errors.

NO_BACKSLASH_ESCAPES

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Disables the use of backslashes (\) as escape characters.

By default, MySQL allows you to use backslashes (\) as escape characters to insert special characters or escape characters into strings. For example, you can use \' to represent single quotation marks ('), \" to represent double quotation marks ("), and \\ to represent backslashes (\).

If you set the sql_mode parameter to NO_BACKSLASH_ESCAPES, MySQL does not allow you to use backslashes (\) for escaping. In this case, backslashes (\) are treated as regular characters rather than escape characters.

This mode prevents possible confusions and errors caused by backslash-based escaping. In some cases, if you use backslashes (\) for escaping, unexpected results may occur, especially when you process the data that contains a large number of backslashes (\).

Examples:

SET sql_mode = 'NO_BACKSLASH_ESCAPES';
SELECT 'It\'s a test';

By default, the backslash (\) between the two single quotation marks (') in the preceding query is used for escaping. However, if you set the sql_mode parameter to NO_BACKSLASH_ESCAPES, the backslash (\) is treated as a regular character rather than an escape character. As a result, the query returns It\'s a test rather than It's a test.

NO_DIR_IN_CREATE

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Disables the use of DIRECTORY in the CREATE TABLE statement.

By default, the CREATE TABLE statement allows you to specify DATA DIRECTORY and INDEX DIRECTORY. If you set the sql_mode parameter to NO_DIR_IN_CREATE, MySQL disables the use of DIRECTORY in the CREATE TABLE statement.

The mode limits directories or prevents you from specifying a specific directory in the CREATE TABLE statement to store table data. Directory options may pose security risks or cause poor data management.

Examples:

SET sql_mode = 'NO_DIR_IN_CREATE';
CREATE TABLE mytable (
id INT PRIMARY KEY,
name VARCHAR(50)
) DIRECTORY = '/path/to/directory';

If the NO_DIR_IN_CREATE mode is enabled, MySQL rejects the DIRECTORY option and reports an error. If you specify NO_DIR_IN_CREATE for the sql_mode parameter, this mode is enabled.

In primary/secondary replication scenarios, you can enable this mode on the secondary database.

NO_ENGINE_SUBSTITUTION

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Disables the use of the default storage engine as a substitution when you create or modify a table.

In MySQL, if the specified storage engine is unavailable or not supported when you create or modify a table, MySQL automatically uses the default storage engine. If you set the sql_mode parameter to NO_ENGINE_SUBSTITUTION, MySQL disables the use of the default storage engine as a substitution.

This mode ensures that the specified storage engine is used and prevents the accidental or inconsistent substitution of the storage engine.

Examples:

SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE TABLE mytable (
id INT PRIMARY KEY,
name VARCHAR(50)
) ENGINE = 'NonexistentEngine';

If the NO_ENGINE_SUBSTITUTION mode is enabled, MySQL rejects the use of the specified storage engine that does not exist and reports an error. If you specify NO_ENGINE_SUBSTITUTION for the sql_mode parameter, this mode is enabled. MySQL does not automatically use the default storage engine to create or modify a table.

NO_UNSIGNED_SUBTRACTION

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Disables the negative subtraction between unsigned integers.

By default, a subtraction operation between integers, where one is of the UNSIGNED type, produces an unsigned result. If the result is negative, an error occurs.

If you set the sql_mode parameter to NO_UNSIGNED_SUBTRACTION, negative results are allowed.

This mode prevents unexpected results when you perform a subtraction operation between unsigned integers. In some cases, subtraction operations between unsigned integers may produce unexpected negative results.

Examples:

SET sql_mode = 'NO_UNSIGNED_SUBTRACTION';
select cast(0 as unsigned)-1;

If the NO_UNSIGNED_SUBTRACTION mode is enabled, the result is -1. If you specify NO_UNSIGNED_SUBTRACTION for the sql_mode parameter, the mode is enabled.

NO_ZERO_DATE

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Disables the use of "0000-00-00" as a valid zero date value in the DATE or DATETIME column.

By default, MySQL allows you to use "0000-00-00" as a valid zero date value in the DATE or DATETIME column. If you set the sql_mode parameter to NO_ZERO_DATE, MySQL disables use of "0000-00-00" as a valid date value. The effect of the NO_ZERO_DATE mode also depends on whether the strict SQL mode is enabled.

• If the NO_ZERO_DATE mode is disabled, "0000-00-00" is allowed and no warning is reported.

• If the NO_ZERO_DATE mode is enabled, "0000-00-00" is allowed and a warning is reported.

• If the NO_ZERO_DATE mode and the strict SQL mode are enabled, "0000-00-00" is not allowed and an error is reported unless IGNORE is also given. For INSERT IGNORE and UPDATE IGNORE operations, "0000-00-00" is allowed and a warning is reported.

This mode prevents invalid or inappropriate date values. "0000-00-00" is not a real date. Disabling the use of "0000-00-00" prevents confusions and incorrect results.

Examples:

SET sql_mode = 'NO_ZERO_DATE';
INSERT INTO mytable (id, date_column) VALUES (1, '0000-00-00');

If the NO_ZERO_DATE mode is enabled, MySQL rejects the insertion of "0000-00-00" into the date_column column and reports an error. If you specify NO_ZERO_DATE for the sql_mode parameter, the mode is enabled.

NO_ZERO _IN_DATE

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Specifies whether the server allows dates in which the year part is not zero but the month or day part is 0.

The effect of the NO_ZERO_IN_DATE mode also depends on whether the strict SQL mode is enabled.

• If the NO_ZERO_IN_DATE mode is disabled, dates with zero parts are allowed and no warning is reported.

• If the NO_ZERO_IN_DATE mode is enabled, dates with zero parts are inserted as "0000-00-00" and a warning is reported.

• If the NO_ZERO_IN_DATE mode and the strict SQL mode are enabled, dates with zero parts are not allowed and an error is reported unless IGNORE is also given. For INSERT IGNORE and UPDATE IGNORE operations, dates with zero parts are inserted as "0000-00-00" and a warning is reported.

ONLY_FULL_GROUP_BY

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Sets the strict mode of the GROUP BY clause.

By default, MySQL allows columns for theSELECT statement not to be included in GROUP BY clauses in GROUP BY queries. This is considered a non-standard extension of MySQL. For example, the following query is valid in default mode:

SELECT id, name, MAX(score)
FROM mytable
GROUP BY id;

In this query, the name column is not included in the GROUP BY clause, and MySQL still returns the result. This may cause uncertainty in the results because different databases may use different aggregation rules.

If you set the sql_mode parameter to ONLY_FULL_GROUP_BY, MySQL enables the strict mode of GROUP BY. The strict mode requires that every non-aggregate column in the SELECT statement be included in the GROUP BY clause. If the requirements are not met, MySQL reports the "1055 (42000): SELECT list is not in GROUP BY clause and contains nonaggregated column" error.

For example, the following query returns an error in ONLY_FULL_GROUP_BY mode:

SET sql_mode = 'ONLY_FULL_GROUP_BY';
SELECT id, name, MAX(score)
FROM mytable
GROUP BY id;

In this query, the name column is not included in the GROUP BY clause, and MySQL reports an error.

The ONLY_FULL_GROUP_BY mode ensures that the query statements follow the standard syntax of GROUP BY and improve the accuracy and reliability of query results.

PAD_CHAR_TO_FULL_LENGTH

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Specifies that the trailing spaces of CHAR fields are not deleted.

When a CHAR field is stored, the column values are fixed-length values in the Compact format. By default, trailing spaces are deleted from CHAR column values on retrieval.

If the PAD_CHAR_TO_FULL_LENGTH mode is enabled, trailing spaces are not deleted and retrieved CHAR values are padded to their full length.

PIPES_AS_CONCAT

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Treats || as a string concatenation operator that functions the same as CONCAT() rather than as a synonym for OR.

REAL_AS_FLOAT

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Treats REAL as a synonym for FLOAT.

By default, MySQL treats REAL as a synonym for DOUBLE.

STRICT_ALL_TABLES

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Enables the strict SQL mode for all storage engines. Invalid data values are rejected.

By default, MySQL allows specific implicit data type conversions and insert operations and generate warnings for update operations. If you set the sql_mode parameter to STRICT_ALL_TABLES, MySQL enables the strict SQL mode that requires strict data type matching and insert and update operations.

After the STRICT_ALL_TABLES mode is enabled, errors are reported in scenarios, including but are not limited to:

• Data type mismatch: If you specify values for a column during an insert or update operation and the data types do not match, MySQL reports an error.

• Non-empty columns: If values are not specified for a non-empty column during an insert or update operation, MySQL reports an error.

• Invalid date or time: If an invalid date or time value is specified in an insert or update operation, MySQL reports an error.

• Non-zero default value: If the default value of a column is not 0 or NULL and no values are specified for the column in an insert operation, MySQL reports an error.

The STRICT_ALL_TABLES mode helps developers better follow data type specifications and limits in database operations to improve data integrity and consistency.

STRICT_TRANS_TABLES

Supported by MySQL 5.6, MySQL 5.7, and MySQL 8.0

Enables the strict SQL mode for storage engines. For more information, see the description of STRICT_ALL_TABLES.

NO_AUTO_CREATE_USER

• Supported by MySQL 5.6 and MySQL 5.7

• Not supported by MySQL 8.0

Disables automatic user creation in GRANT statements.

By default, if the user specified by the GRANT statement does not exist, MySQL automatically creates the user based on the GRANT statement. If you set the sql_mode parameter to NO_AUTO_CREATE_USER, MySQL does not automatically create the user.

This mode enhances database security. This mode ensures that only existing users are authorized to access databases.

Examples:

GRANT SELECT ON mydb.* TO 'new_user'@'localhost';

If the new_user user does not exist and the NO_AUTO_CREATE_USER mode is disabled, MySQL automatically creates a user named new_user and grants the SELECT permission. If you do not specify the field, the mode is disabled. If you do not specify NO_AUTO_CREATE_USER for the sql_mode parameter, the mode is disabled.

If you set the sql_mode parameter to NO_AUTO_CREATE_USER and the new_user user does not exist, MySQL refuses to create the user and reports an error.

SET sql_mode = 'NO_AUTO_CREATE_USER';
GRANT SELECT ON mydb.* TO 'new_user'@'localhost';

The NO_AUTO_CREATE_USER mode affects automatic user creation only in the GRANT statement. It does not affect manual user creation or other user-related operations.

NO_FIELD_OPTIONS

• Supported by MySQL 5.6 and MySQL 5.7

• Not supported by MySQL 8.0

Removes MySQL-specific column options from the output of the SHOW CREATE TABLE statement. This mode is used by the portability mode of mysqldump.

NO_KEY_OPTIONS

• Supported by MySQL 5.6 and MySQL 5.7

• Not supported by MySQL 8.0

Removes MySQL-specific index options from the output of the SHOW CREATE TABLE statement. This mode is used by the portability mode of mysqldump.

NO_TABLE_OPTIONS

• Supported by MySQL 5.6 and MySQL 5.7

• Not supported by MySQL 8.0

Removes MySQL-specific table options, such as ENGINE, from the output of the SHOW CREATE TABLE statement. This mode is used by the portability mode of mysqldump.