MySQL SQL-Mode

A strange problem occurred recently when I was working on a project:

The code is directly checkout from SVN, and the database is directly dumped from another machine. After the environment is properly configured, the order cannot be placed on my machine. I don't understand why the same code, the same database

It can run normally on another machine, but it cannot run normally on my machine.

 

The reason for this problem is that MySQL runs in SQL-mode = "strict_trans_tables, no_auto_create_user, no_engine_substitution" mode on my machine, because our company's product SQL is not very standard, as a result, the SQL statement cannot be normally inserted into the database, leading to a failure in ordering the result.

 

For the sake of simplicity, I just modified the MySQL SQL-model on my machine to "no_auto_create_user, no_engine_substitution", and then restarted the msyql server and it was OK.

 

Since the database table field defines not null, why do I not set the default value for this field when writing insert ?? If you do not set the default value for a field, the initial value is not provided in SQL. What do Nb users want ??

 

I hope that the Nb users can write SQL statements in a more standardized way, at least let others run normally without running in the error mode ......

 

On fire ..

 

For more information about MySQL Server mode, see MySQL reference manual 5.3.2 SQL SERVER mode.

 

############################# Gorgeous splitting line ######### ##################

############################# Gorgeous splitting line ######### ##################

5.3.2. SQL Server Mode

The MySQL server can operate in different SQL modes, and can apply different modes to different clients. In this way, each application can customize the server operation mode as needed.

The mode defines which SQL syntaxes should be supported by MySQL and which data verification checks should be performed. This makes it easier to use MySQL in different environments and use it with other database servers.

You can use -- SQL-mode ="Modes"Option to startMysqldSet the default SQL mode. If you want to reset, this value can also be blank (-- SQL-mode = "").

You can also use set [session | Global] SQL _mode = 'After startup'Modes'Statement sets the SQL _mode variable to change the SQL mode. The super permission is required when setting global variables, and operations on all clients connected from that time will be affected. Setting session variables only affects the current client. Any client can change its own session SQL _mode value at any time.

ModesIs is a series of different modes separated by commas. You can use the select @ SQL _mode statement to query the current mode. The default value is null (NO mode is set ).

Important SQL _mode values:

· ANSI

Modify the syntax and behavior to make it more compliant with standard SQL.

· Strict_trans_tables

If the given value cannot be inserted into the transaction table, discard the statement. For non-transaction tables, if the value appears in the 1st rows of a single-row or multi-row statement, the statement is discarded. A more detailed description is provided later in this section.

· Traditional

Make MySQL acts like a "traditional" SQL database system. A simple description of this mode is to "give an error rather than warning" when an incorrect value is inserted in the column ".NoteRelease: if an error is found, immediately discard insert/update. If you use a non-transaction storage engine, this method is not what you want, because the data changes made before an error are not "rolled", and the result is that the update is "only a part ".

This manual refers to the "strict mode", indicating at least the mode in which strict _ trans_tables or strict _ all_tables is enabled.

All supported modes are described below:

· Allow_invalid_dates

Do not check all dates in strict mode. Only check the day between the month and the day between 1 and 12. This is important in Web applications when you get the year, month, and day from three different fields and want to exactly save the content inserted by the user (without date verification. This mode applies to date and datetime columns. It is not suitable for the timestamp column. The timestamp column requires the verification date.

After the strict mode is enabled, the server requires valid months and days, not only in the range of 1 to 12 and 1 to 31, respectively. For example, '2017-04-31 'is valid when strict mode is disabled, but it is invalid when strict mode is enabled. Allow_invalid_dates should also be enabled to allow the masking of fixed dates in strict mode.

· Ansi_quotes

Use '"' as an identifier quotation mark ('''). Do not use it as a string quotation mark. In ANSI mode, you can still use ''' to reference the identifier. When ansi_quotes is enabled, you cannot use double quotation marks to reference strings because it is interpreted as an identifier.

· Error_for_division_by_zero

In strict mode, if the insert or update process is divided by zero (or mod (x, 0), an error is generated (otherwise it is a warning ). If this mode is not provided, MySQL returns NULL if it is divided by zero. If insert ignore or update ignore is used, MySQL generates a zero division warning, but the operation result is null.

· High_not_precedence

The priority of the not operator is that the expression, for example, not a between B and C, is interpreted as not (A between B and C ). In some earlier versions of MySQL, the expression is interpreted as (not a) between B and C. Enable the high_not_precedencesql mode to obtain results with a higher priority.

mysql> SET sql_mode = '';

mysql> SELECT NOT 1 BETWEEN -5 AND 5;

        -> 0

mysql> SET sql_mode = 'broken_not';

mysql> SELECT NOT 1 BETWEEN -5 AND 5;

        -> 1

· Ignore_space

A space is allowed between the function name and. All function names are considered as saved words. The result is that if you want to access the database, table, or column name that is saved as a word, you must reference it. For example, because there is a user () function, the user table name in the MySQL database and the user column in the table are saved, so you must reference them:

SELECT "User" FROM mysql."user";

· No_auto_create_user

Prevent grant from automatically creating new users unless a password is specified.

· No_auto_value_on_zero

No_auto_value_on_zero affects the processing of the auto_increment column. Generally, you can insert null or 0 to this column to generate the next serial number. No_auto_value_on_zero disables 0, so only null can generate the next serial number.

This mode is useful if you save 0 to the auto_increment column of the table. (This Convention is not recommended ). For example, if you useMysqldumpDump the table and reload it. MySQL generates a new serial number when it encounters a value of 0. The contents of the generated table are different from those of the dump table. Enable no_auto_value_on_zero before reloading the dump file to solve this problem.MysqldumpThe output automatically contains the statement that enables no_auto_value_on_zero.

· No_backslash_escapes

Disables the backslash ('\') as the exit character in the string. If this mode is enabled, the backslash is a common character.

· No_dir_in_create

Ignore all index directory And data directory commands when creating a table. This option is useful for slave replication servers.

· No_engine_substitution

If the required storage engine is disabled or not compiled, the storage engine cannot be replaced automatically.

· No_field_options

Do not print the MySQL special column option in the output of show create table. This mode is used in portability ModeMysqldump.

· No_key_options

Do not print mysql-specific index options in the output of show create table. This mode is used in portability ModeMysqldump.

· No_table_options

Do not print the MySQL special table option (such as engine) in the output of show create table ). This mode is used in portability ModeMysqldump.

· No_unsigned_subtraction

In a subtraction operation, if an operand has no symbols, do not mark the result as unsigned. Note that the unsigned bigint cannot be 100% used in context. See section 12.8 "cast functions and operators ".

 

· No_zero_date

In strict mode, do not use '2017-00-00 'as a valid date. You can still use the ignore option to insert a zero date. In non-strict mode, this date is acceptable, but a warning is generated.

· No_zero_in_date

In strict mode, the date of month or day is not accepted. If the ignore option is used, we insert '2017-00-00 'for a similar date '. In non-strict mode, this date is acceptable, but a warning is generated.

· Only_full_group_by

Do not point the query in the group by section to an unselected column.

· Pipes_as_concat

| Is treated as a String concatenation operator (+) (same as Concat (), not as or.

· Real_as_float

Treat real as a synonym for float, rather than a synonym for double.

· Strict_trans_tables

Enable strict mode for all storage engines. The invalid data value is denied. Detailed descriptions are provided later.

· Strict_trans_tables

Enable strict mode for the transaction storage engine, or enable strict mode for the non-transaction storage engine. Detailed descriptions are provided later.

Strictly control how MySQL processes illegal or lost input values. One value can be invalid for several reasons. For example, the data type is incorrect. It is not suitable for columns or is out of range. If the newly inserted row does not contain a column that does not display the value defined by the default clause, the value is lost.

When the strict_all_tables or strict_trans_tables mode is enabled for a transaction table, an error occurs if the statement contains an invalid or missing value. The statement is abandoned and rolled.

For non-transaction tables, if the 1st rows inserted or updated have bad values, the two modes share the same behavior. The statement is abandoned and the table remains unchanged. If the statement inserts or modifies multiple rows and the bad value appears in the 2nd or later rows, the result depends on which strict option is enabled:

· For strict_all_tables, MySQL returns an error and ignores the remaining rows. However, in this case, the previous row has been inserted or updated. This indicates that you can update some data, which may not be what you want. To avoid this, it is best to use a single row statement, because you can give up without changing the table.

· For strict_trans_tables, MySQL converts invalid values to valid values closest to the column and inserts the adjusted values. If the value is lost, MySQL inserts an implicit default value in the column. In any case, MySQL generates a warning instead of an error and continues to execute the statement. Section 13.1.5, "create table Syntax" describes implicit default values.

Invalid date is not allowed in strict mode, for example, '2017-04-31 '. It does not allow the date to use the "zero" part, for example, '2017-04-00' or "zero" date. To disable it, enable the no_zero_in_date and no_zero_date SQL modes based on the strict mode.

If you do not use the strict mode (that is, do not enable strict_trans_tables or strict_all_tables mode), MySQL inserts the adjusted value and provides a warning for invalid or lost values. In strict mode, you can use insert ignore or update ignore. See section 13.5.4.22, "Show warnings Syntax ".

The following special modes quickly combine the modes listed above.

This includes all the mode values in most of the latest MySQL versions. In the old version, the combination mode does not include any unsuitable mode values in the new version.

· ANSI

It is equivalent to real_as_float, pipes_as_concat, ansi_quotes, and ignore_space. See section 1.8.3, "run MySql in ANSI mode ".

· DB2

Equivalent to pipes_as_concat, ansi_quotes, ignore_space, no_key_options, no_table_options, and no_field_options.

· MaxDB

Equivalent to pipes_as_concat, ansi_quotes, ignore_space, no_key_options, no_table_options, no_field_options, and no_auto_create_user.

· MSSQL

Equivalent to pipes_as_concat, ansi_quotes, ignore_space, no_key_options, no_table_options, and no_field_options.

· Mysql323

It is equivalent to no_field_options and high_not_precedence.

· Mysql40

It is equivalent to no_field_options and high_not_precedence.

· Oracle

Equivalent to pipes_as_concat, ansi_quotes, ignore_space, no_key_options, no_table_options, no_field_options, and no_auto_create_user.

· PostgreSQL

Equivalent to pipes_as_concat, ansi_quotes, ignore_space, no_key_options, no_table_options, and no_field_options.

· Traditional

It is equivalent to strict_trans_tables, strict_all_tables, no_zero_in_date, no_zero_date, error_for_division_by_zero, and no_auto_create_user.