SQL Mode

To avoid illegal data that affects the change period and avoid modification of old code during code migration, set the following parameter to realize these functions.

sql_mode

Parameter description: sets the SQL mode.

Values: string or null

• sql_mode_strict: If data is illegal or the value is not specified, an error will be reported.

  The non-sql_mode_strict mode supports the following scenarios:

  • When value that does not conform to the column type is inserted, data conversion is needed. There are two types insert into table values(…) and insert into table select ….

    This mainly involves conversion of different data types. The data types that involve conversion includes tinyint (the data range of tinyint is different from that of mysql, so it is not considered currently), smallint, int, bigint, float, double, numeric, clob, char, and varchar.

  • When the length of the column value to be inserted exceeds the limited length, assign a maximum or minimum value to the column (the involved types include tinyint, smallint, int, bigint, float, double, numeric, clob, char, and varchar).

  • During insertion, insert values to the columns that are not null and do not have default values. For the columns that do not have values inserted, add default values to them (the involved data types include tinyint, smallint, int, bigint, float, double, numeric, clob, char, and varchar).

  • For columns that are not null and do not have default values, insert default values (the involved data types include numeric, clob, char, and varchar).

• sql_mode_full_group: Non-group-by columns should not exist in the select list unless they work as parameters in aggregate functions.

  Note: If the columns in the select list do not use aggregate functions and do not exist in the group by subquery, an error will be reported.

Default value: sql_mode_strict and sql_mode_full_group