Mysql SQL Server Mode introduction _mysql

MySQL SQL Server mode

The MySQL server can operate in different SQL modes and can apply different modes for different clients. This allows each application to tailor the operating mode of the server to its own needs.

The schema defines which SQL syntax should be supported by MySQL and what kind of data validation check should be performed. This makes it easier to use MySQL in different environments and to use MySQL in conjunction with other database servers.

You can set the default SQL mode by starting mysqld with the--sql-mode= "modes" option. If you want to reset, the value can also be null (--sql-mode = "").

You can also use set after startup [session| GLOBAL] sql_mode= ' modes ' statement sets the Sql_mode variable to change the SQL schema. You need to have super permissions when you set up a global variable, and you can affect the operations of all clients that have been connected since then. Setting the session variable affects only the current client. Any client can change its own session Sql_mode value at any time.

Modesis is a series of different patterns that are separated by commas (', '). You can query the current pattern with the SELECT @ @sql_mode statement. The default value is empty (no mode is set).

Main important Sql_mode Value

· Ansi

Change 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, the statement is discarded. For non-transaction tables, the statement is discarded if the value appears at line 1th of a single-line statement or multiline statement. A more detailed description is given later in this section.

· Traditional

Make MySQL behaves like a "traditional" SQL database system. A simple description of the pattern is "give an error instead of a warning" when inserting an incorrect value into the column. Note: Discard insert/update as soon as you find the error. If you are using a non-transactional storage engine, this is not what you want, because the data changes made prior to the error will not "scroll", and the result is a "partial" update.

This manual refers to "strict mode", which means that at least strict _trans_tables or strict _all_tables are enabled.

All of the supported modes are described below:

· Allow_invalid_dates

Do not check the full date in strict mode. Check only between 1 and 12 months and between 1 and 31 days. This is important in Web applications when you get the year, month, and day from three different fields, and you want to exactly save what the user inserts (no date validation). This pattern applies to date and datetime columns. Not suitable for timestamp columns, timestamp columns require validation dates.

When strict mode is enabled, the server requires a legal month and day, not just in the range of 1 to 12 and 1 to 31. For example, when strict mode is disabled, ' 2004-04-31 ' is legal, but it is illegal to enable strict mode. To allow a fixed date to be masked in strict mode, you should also enable allow_invalid_dates.

· Ansi_quotes

Treats ' ' ' as the identifier quotation mark (' ' quote character '), not as a quote character for a string. In ANSI mode, you can still use ' ' to refer to identifiers. When Ansi_quotes is enabled, you cannot use double quotes to refer to a string because it is interpreted as a qualifier.

· Error_for_division_by_zero

In strict mode, in the INSERT or update process, an error (or warning) is generated if 0 is removed (or mod (x,0)). If this mode is not given, MySQL returns null when 0 is removed. If used in insert ignore or update ignore, the MySQL build is 0 in addition to the warning, but the result of the operation is null.

· High_not_precedence

The precedence of the NOT operator is that an expression such as not a BETWEEN B and C is interpreted as not (a BETWEEN B and C). In some older versions of MySQL, expressions are interpreted as (not a) BETWEEN B and C. Enabling the High_not_precedencesql mode allows you to obtain previous higher priority results.

Copy Code code as follows:

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

Allow the name of the function and ' (' there are spaces between them. Forces all function names to be treated as saved words. As a result, if you want to access a database, table, or column name that you save as a word, you must refer to it. For example, because of the user () function, the user table name in the MySQL database and the user column in the table are saved, so you must refer to them:

Copy Code code as follows:

SELECT "User" from MySQL. " User ";

No_auto_create_user

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

No_auto_value_on_zero

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

This pattern can be useful if you save 0 to a auto_increment column in a table. (This practice is not recommended). For example, if you use a mysqldump dump and load, MySQL encounters a 0 value that typically generates a new serial number, and the resulting table differs from the dump table. Enabling No_auto_value_on_zero before overloading a dump file can resolve the problem. Mysqldump automatically includes No_auto_value_on_zero-enabled statements in the output.

No_backslash_escapes

Disables the backslash character (' \ ') as the exit character within the string. This mode is enabled, and the backslash becomes the normal character.

no_dir_in_create

All index directory and data directory directives are ignored when creating tables. This option is useful for copying servers.

no_engine_substitution

If the storage engine you want is disabled or not compiled, you can prevent automatic replacement of the storage engine.

no_field_options

Do not print the MySQL private column option in the output of show CREATE table. This mode is used for mysqldump under Portable mode (portability modes).

no_key_options

Do not print the MySQL private indexing option in the output of show CREATE table. This mode is used for mysqldump under Portable mode (portability modes).

no_table_options

Do not print the MySQL private table option (for example, engine) in the output of show CREATE table. This mode is used for mysqldump under Portable mode (portability modes).

no_unsigned_subtraction

In a subtraction operation, if an operand does not have a symbol, do not mark the result as unsigned. Note that this allows unsigned bigint 100% to be used in the context. See section 12.8, "Cast functions and operators".

no_zero_date

In strict mode, do not make ' 0000-00-00 ' a legal date. You can still insert the 0 issue with the Ignore option. In a non-strict mode, the date can be accepted, but a warning is generated.

no_zero_in_date

In strict mode, do not accept the month or day part is 0 of the date. If you use the Ignore option, we insert ' 0000-00-00 ' for a similar date. In a non-strict mode, the date can be accepted, but a warning is generated.

· Only_full_group_by

Do not let queries in the group by section point to columns that are not selected.

· Pipes_as_concat

Will | | is treated as a string concatenation operator (+) (same as concat ()) and not as an OR.

· Real_as_float

Treat real as a synonym for float, not a double.

· Strict_trans_tables

Enable strict mode for all storage engines. Illegal data value is denied. Detailed instructions are available later.

· Strict_trans_tables

Strict mode is enabled for the transaction storage engine or it may be enabled for non-transaction storage engines. Detailed instructions are available later.

Strict mode controls how MySQL handles illegal or missing input values. There are several reasons to make a value illegal. For example, the data type is incorrect, does not fit the column, or is out of range. The value is lost when the newly inserted row does not contain a value that does not show the definition default clause for a column.

For a transaction table, an error occurs when the Strict_all_tables or Strict_trans_tables mode is enabled, if there are illegal or missing values in the statement. Statement is discarded and scrolled.

For non-transaction tables, if a bad value occurs on line 1th of the insert or update, the behavior of both modes is the same. Statement is discarded and the table remains unchanged. If a statement inserts or modifies multiple rows, and a bad value appears in the 2nd or subsequent row, the result depends on which strict option is enabled:

• Return an error for Strict_all_tables,mysql and ignore the remaining rows. However, in this case, the preceding row has been inserted or updated. This means you can partially update it, which may not be what you want. To avoid this, it is best to use a single-line statement, because you can discard the table without changing it.

• Converts an illegal value to the closest legal value to the column and inserts an adjusted value for Strict_trans_tables,mysql. If the value is lost, MySQL inserts an implicit default value in the column. In any case, MySQL generates a warning instead of giving an error and continues executing the statement. 13.1.5 section, "CREATE table Syntax" describes implicit default values.

Strict mode does not allow illegal dates, such as ' 2004-04-31 '. It does not allow the prohibition of dates using the "0" section, such as ' 2004-04-00 ' or ' 0 ' dates. To prohibit, you should enable No_zero_in_date and no_zero_date SQL mode on a strict mode basis.

If you do not use strict mode (that is, do not enable Strict_trans_tables or Strict_all_tables mode), for illegal or missing values, MySQL inserts the adjusted value and gives a warning. In strict mode, you can implement it by insert ignore or update ignore. See section 13.5.4.22, "Show warnings Syntax".