MySQL SQL Server Mode

MySQL SQL Server Mode

MySQLThe server can use differentSQLAnd can apply different modes to different clients. In this way, each applicationProgramYou can customize the server operation mode as needed.

Mode definitionMySQLWhat should be supported?SQLSyntax, and the data verification check to be performed. This makes it easier to use it in different environments.MySQLAnd use it with other database serversMySQL.

You can use-- SQL-mode ="Modes"Option to startMysqldComeSet DefaultSQLMode. This value can be empty if you want to reset it.(-- SQL-mode = "").

You can also useSet [session | Global] SQL _mode ='Modes'Statement settingsSQL _modeVariable to changeSQLMode. SetGlobalVariableSuperPermission, and affects the operations of all clients connected since then. SetSessionVariables only affect the current client. Any client can change its own session at any timeSQL _modeValue.

ModesIsYes, use commas (,).(',')Separated by a series of different modes. You can useSelect @ SQL _modeStatement to query the current mode. The default value is null.(No mode is set).

 

Important SQL _mode values

·ANSI

Change syntax and behavior to make them more compliant with standardsSQL.

·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 in1To discard the statement. A more detailed description is provided later in this section.

·Traditional

Make MySQLBehavior like"Traditional"SQLDatabase System. A simple description of this mode is given when an incorrect value is inserted in the column.Error instead of warning".NoteRelease: Give up once an error is foundInsert/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"Strict Mode", Indicating at leastStrict _ trans_tablesOrStrict _ all_tablesEnabled mode.

All supported modes are described below:

·Allow_invalid_dates

Do not check all dates in strict mode. Check only1To12Between the month and1To31. InWebIn an application, when you obtain the year, month, and day from three different fields and want to exactly save the content inserted by the user(No date Verification)Is very important. This mode appliesDateAndDatetimeColumn. Not SuitableTimestampColumn,TimestampThe date on which the column needs to be verified.

After the strict mode is enabled, the server requires valid months and days, not just in1To12And1To31. For example, when strict mode is disabled'2017-04-31'It is valid, but it is invalid when strict mode is enabled. To enable fixed-date masking in strict modeAllow_invalid_dates.

·Ansi_quotes

Change'"'Is considered as an identifier quotation mark('`'Quotation mark character)Do not treat it as a string quotation mark character. InANSIMode, you can still use'`'To reference the identifier. EnableAnsi_quotesYou cannot use double quotation marks to reference a string because it is interpreted as an identifier.

·Error_for_division_by_zero

In strict modeInsertOrUpdateIf the process is divided by zero(OrMoD (x,0)).(Otherwise, it is a warning.). If this mode is not providedMySQLReturnNull. If you useInsert ignoreOrUpdate ignoreMedium,MySQLGenerates a zero-division warning, but the operation result isNull.

·High_not_precedence

NotThe priority of operators is the expression, for exampleNot a between B and CInterpretedNot (A between B and C). In earlier versionsMySQLIs interpreted(Not a) between B and C. EnableHigh_not_precedenceSQLMode to obtain the results with a higher priority.

Mysql> set SQL _mode =''; MySQL> Select not1Between-5And5;->0MySQL> Set SQL _mode ='Broken_not'; MySQL> Select not1Between-5And5;->1
 

·Ignore_space

Allowed function names'(. 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 exampleUser ()Function,MySQLIn the databaseUserTable Name andUserColumns are saved, so you must reference them:

 
Select"User"From MySQL."User";
 

·No_auto_create_user

PreventGrantAutomatically create a new user unless a password is specified.

·No_auto_value_on_zero

No_auto_value_on_zeroImpactAuto_incrementColumn processing. Generally, you can insert data into this column.NullOr0Generate the next serial number.No_auto_value_on_zeroDisable0, So onlyNullGenerate the next serial number.

If0Save to tableAuto_incrementColumn. This mode is useful.(This Convention is not recommended). For example, if you useMysqldumpDump and reload the table,MySQLEncounter0Generally, a new serial number is generated. The contents of the generated table are different from those of the dump table. Enable before reloading dump filesNo_auto_value_on_zeroThis problem can be solved.MysqldumpAuto include enabled in outputNo_auto_value_on_zero.

·No_backslash_escapes

Disable backslash characters('\')As the exit character in the string. If this mode is enabled, the backslash is a common character.

·No_dir_in_create

Ignore allINDEX DIRECTORYAndData DirectoryCommand. 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 notShow CREATE TABLEOutputMySQLSpecial Column options. This mode is in portable mode (Portability Mode)Mysqldump.

·No_key_options

Do notShow CREATE TABLEOutputMySQLPrivate Index option. This mode is in portable mode (Portability Mode)Mysqldump.

·No_table_options

Do notShow CREATE TABLEOutputMySQLSpecial table options (for exampleEngine). This mode is in portable mode (Portability Mode)Mysqldump.

·No_unsigned_subtraction

Do not mark the resultUnsigned. Note thatUnsigned bigintNo100%Used in context. See section 12.8 "cast functions and operators ".

 

·No _Zero_ Date

In strict mode, do not set'2017-00-00'As a legal date. You can still useIgnoreOption to insert zero date. In non-strict mode, this date is acceptable, but a warning is generated.

·No_zero_in_date

In strict mode, the monthly or daily part is not accepted0. If you useIgnoreOption, we insert for a similar date'2017-00-00'. In non-strict mode, this date is acceptable, but a warning is generated.

·Only_full_group_by

Don't letGroupThe query in the Section points to an unselected column.

·Pipes_as_concat

Set|Treated as a String concatenation operator (+)(SameConcat ())InsteadOr.

·Real_as_float

SetRealConsideredFloat, InsteadDouble.

·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.

Strict Mode ControlMySQLHow to handle 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. When the newly inserted row does not contain a column, no display definition is displayed.DefaultClause value, the value is lost.

For transaction tables, when enabledStrict_all_tablesOrStrict_trans_tablesIf the statement contains invalid or missing values, an error occurs. The statement is abandoned and rolled.

For non-transaction tables, if1The row has a bad value. The behavior of the two modes is the same. The statement is abandoned and the table remains unchanged. If the statement inserts or modifies multiple rows, and the bad value appears in2Or the following line, the result depends on which strict option is enabled:

·ForStrict_all_tables,MySQLReturns 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.

·ForStrict_trans_tables,MySQLConvert the invalid value to the legal value closest to the column and insert the adjusted value. If the value is lost,MySQLInsert the implicit default value into the column. In any situation,MySQLA warning is generated instead of an error and the statement is executed. Section 13.1.5, "create table Syntax" describes implicit default values.

Invalid date is not allowed in strict mode, such'2017-04-31'. It does not allow the date to use the "zero" section, for example'2014-04-00'Or"Zero"Date. In strict mode, enableNo_zero_in_dateAndNo_zero_dateSQLMode.

If you do not use the strict Mode(DisableStrict_trans_tablesOrStrict_all_tablesMode), For illegal or lost values,MySQLThe adjusted value is inserted and a warning is given. In strict mode, you can useInsert ignoreOrUpdate ignore. See section 13.5.4.22, "Show warnings Syntax ".

 

Special Mode Combination Mode

Including most of the latest versionsMySQLAll mode values in. In the old version, the combination mode does not include any unsuitable mode values in the new version.

·ANSI

EquivalentReal_as_float,Pipes_as_concat,Ansi_quotes,Ignore_space. See section 1.8.3, "run MySql in ANSI mode ".

·DB2

EquivalentPipes_as_concat,Ansi_quotes,Ignore_space,No_key_options,No_table_options,No_field_options.

·MaxDB

EquivalentPipes_as_concat,Ansi_quotes,Ignore_space,No_key_options,No_table_options,No_field_options,No_auto_create_user.

·MSSQL

EquivalentPipes_as_concat,Ansi_quotes,Ignore_space,No_key_options,No_table_options,No_field_options.

·Mysql323

EquivalentNo_field_options,High_not_precedence.

·Mysql40

EquivalentNo_field_options,High_not_precedence.

·Oracle

EquivalentPipes_as_concat,Ansi_quotes,Ignore_space,No_key_options,No_table_options,No_field_options,No_auto_create_user.

·PostgreSQL

EquivalentPipes_as_concat,Ansi_quotes,Ignore_space,No_key_options,No_table_options,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.