SQL Mode of MySQL

Introduction to SQL Mode
In MySQL, SQL Mode is often used to solve the following problems:
1. By setting the SQL Mode, you can complete the data verification of different rigor to effectively ensure the accuracy of data.

2. By setting the SQL Mode to ANSI mode, to ensure that most SQL is in accordance with the standard SQL syntax, so that when applied between different databases, there is no need to make major changes to the business SQL.

3. When performing data migration in different databases, setting the SQL Mode can make the data on MySQL easier to migrate to the target database

On MySQL5.7.18, the default SQL Mode (@@sql_mode) is STRICT_TRANS_TABLES, NO_ENGINE_SUBSTITUTION

STRICT_TRANS_TABLES is a strict mode, which implements strict verification of data. Error data that does not meet the data type cannot be inserted into the table, ensuring the accuracy of the data.

If you change the SQL Mode to ANSI mode, the wrong data will also be inserted into the table, and the value exceeding the length will be intercepted.

Common functions of SQL Mode
1. Verify the legality of the date format
2. When MOD(x, 0) is executed in ANSI mode, there is no error, and the value becomes NULL during insertion.
3. Enable NO_BACKSLASH_ESCAPES to make the backslash into ordinary characters.
4. Enable PIPES_AS_CONTACT mode. Treat "||" as a string concatenation symbol (same as CONCAT)

Common SQL Mode
1. ANSI     Equivalent to REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BY, ANSI
2. STRICT_TRANS_TABLES is suitable for transaction processing, and throws errors directly for illegal data instead of warning
3. TRADITIONAL is equivalent to STRICT_TRANS_TABLES, STRICT_ALL_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, TRADITIONAL, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, it is a strict mode, the error is directly thrown in the transaction, and the error is directly thrown in the transaction.