MySQL's Sql-mode

SQL Mode is commonly used to solve the following types of problems:

1, by setting up SQL Mode, can complete different strict degree of data validation, effectively guarantee the accuracy of data.

2, by setting the SQL Mode ANSI mode, to ensure that most SQL conforms to the standard SQL syntax, so that when the application between different databases to migrate, there is no need to make large changes to the business SQL.

3. Before migrating data between different databases, you can make the data on MySQL easier to migrate to the target database by setting up SQL Mode.

First, commonly used Sql-mode

pipes_as_concat--this mode treats "| |" as a string connection operator.

no_backslash_escapes--makes the backslash a normal character.

ansi--is equivalent to the real_as_float, Pipes_as_concat, Ansi_quotes, Ignore_space, and ANSI combination modes, which make syntax and behavior more compliant with standard SQL.

strict_trans_tables--applies to both transactional and non-transactional tables, it is strict mode, does not allow illegal dates, and does not allow values that exceed the field length to be inserted into the field, giving an error instead of a warning for inserting an incorrect value.

traditional--is equivalent to Strict_trans_tables, Strict_all_talbes, No_zero_in_date, No_zero_date, ERROR_FOR_DIVISION_BY_ZERO, Taditional and No_auto_create_user combine modes, so it is also strict mode, for inserting incorrect values is an error rather than a warning. Can be applied to transaction tables and non-transactional tables, and is immediately rolled back whenever an error occurs when a transaction table is used

Second, the use of SQL Mode in data migration between other heterogeneous databases

1, Db2--pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, no_field_options

2, Maxdb--pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, no_table_options,NO_FIELD_O Ptions, No_auto_create_user

3, Mssql--pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, no_table_options,NO_FIELD_O Ptions

4, Oracle--pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, no_table_options,NO_FIELD_ OPTIONS, No_auto_create_user
5, Postgresql--pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, no_table_options,NO_FI Eld_options