MySQL Learning Note-sql Server mode rollup

MySQL Learning Note-sql Server mode rollup

MySQL servers can operate in different SQL modes, and different modes can be applied to different clients. This allows each application to customize the operating mode of the server according to its own needs.

The schema defines which SQL syntax should be supported by MySQL and what data validation checks should be performed. This makes it easier to use MySQL in different environments and 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 empty (–sql-mode = "").

You can also use set after startup [session| GLOBAL] sql_mode= ' modes ' statement set the Sql_mode variable to change the SQL mode. Setting the global variable requires super privilege and affects the operation of all clients connected from then on. Setting the session variable affects only the current client. Any client can change its session Sql_mode value at any time.

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

The main important Sql_mode values are:

·ANSI

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

The above operation shows that after setting Sql_mode to ANSI, the supported SQL syntax, as well as the data validation check and other properties are changed accordingly.

·STRICT_TRANS_TABLES

If the given value cannot be inserted into the transaction table, the statement is discarded. For non-transactional tables, if the value appears in line 1th of a single statement or multiline statement, the statement is discarded. A more detailed description is given later in this section.

·TRADITIONAL

The Sql_mode mode makes MySQL behave like a "traditional" SQL database system. A simple description of the pattern is "give an error instead of a warning" when an incorrect value is inserted in the column. Discard insert/update As soon as errors are found. If you use a non-transactional storage engine, this is not what you want, because the data changes made before the error do not "scroll" and the result is "only part of the update".

Strict mode, which indicates that at least strict _trans_tables or strict _all_tables is enabled.

All modes supported by Sql_mode:

·         ALLOW_INVALID_DATES

Do not check the full date in strict mode. Only check the month between 1 and 12 and the day between 1 and 31. This is important in a Web application when you get the year, month, and day from three different fields, and you want to save exactly what the user is inserting (no date validation). This pattern applies to date and datetime columns. Not suitable for timestamp column, timestamp column requires validation date.

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

·         ANSI_QUOTES

Treat the double quotation mark "" as the identifier quotation mark, not as the quotation mark character of the string. In ANSI mode, you can still use "' to refer to identifiers. When Ansi_quotes is enabled, you cannot use double quotation marks to reference a string because it is interpreted as a qualifier. Such as:

·         ERROR_FOR_DIVISION_BY_ZERO

In strict mode, if 0 is removed (or mod (x,0)) during insert or update, an error is generated (otherwise, warning). If this mode is not given, MySQL returns null when 0 is removed. For example, if you use Insert ignore or update ignore mode, the MySQL build is 0 apart from 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.

For example:

·         IGNORE_SPACE

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

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. In general, you can insert null or 0 to the column to generate the next sequence number. No_auto_value_on_zero disables 0, so only null can generate the next sequence 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 use mysqldump to dump a table and reload it, MySQL encounters a value of 0 that typically generates a new serial number, and the contents of the resulting table are different from the dump table. The problem can be resolved by enabling No_auto_value_on_zero before overloading the dump file. Mysqldump automatically includes No_auto_value_on_zero-enabled statements in the output.

·         NO_BACKSLASH_ESCAPES

Disables the backslash character (' \ ') as an exit character within a string. When this mode is enabled, the backslash becomes the normal character.

·         NO_DIR_IN_CREATE

When you create a table, all index directory and data directory directives are ignored. This option is useful for copying from a server.

·         NO_ENGINE_SUBSTITUTION

If the storage engine you need is disabled or not compiled, you can prevent the storage engine from being automatically replaced.

·         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 index 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 dedicated 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 is unsigned, do not mark the result as unsigned. Note that this allows unsigned bigint to be 100% in context.

·         NO_ZERO_DATE

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

·         NO_ZERO_IN_DATE

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

·         ONLY_FULL_GROUP_BY

Do not allow queries in the Group by section to point to columns that are not selected.

·         PIPES_AS_CONCAT

Will | | Treated as a string join operator (+) (same as concat ()), not as or.

·         REAL_AS_FLOAT

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

·         STRICT_TRANS_TABLES

Enable strict mode for all storage engines. Illegal data value is denied.

· Strict_trans_tables

It is also possible to enable strict mode for the transaction store engine, or for non-transactional storage engines.

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 wrong, is not suitable for columns, or is out of range. The value is lost when the newly inserted row does not contain a column that does not display a value that defines the default clause.

For transactional tables, an error occurs if there are illegal or missing values in the statement when Strict_all_tables or Strict_trans_tables mode is enabled. The statement is discarded and scrolled.

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

• Returns an error for Strict_all_tables,mysql and ignores the remaining rows. However, in this case, the preceding line 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.

• For Strict_trans_tables,mysql, convert the illegal value to the closest legal value to the column and insert the adjusted value. If the value is missing, MySQL inserts an implicit default value in the column. In any case, MySQL generates a warning instead of giving an error and continuing to execute the statement.

Strict mode does not allow illegal dates, such as ' 2004-04-31 '. It does not allow a forbidden date to use the "0" part, such as ' 2004-04-00 ' or ' 0 ' date. 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 will insert the adjusted value and give a warning. In strict mode, you can do this through the insert ignore or update ignore.

The following special modes quickly combine one or more of the previously listed patterns.

This includes all of the schema values in most recent versions of MySQL. In older versions, the combined mode does not include the non-applicable specific pattern values that are not available in the new version.

• Ansi
  Equivalent to Real_as_float, Pipes_as_concat, Ansi_quotes, Ignore_space.

• DB2
  Equivalent to Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options.

• MAXDB
  Equivalent to Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options, NO_AUTO_CREATE_ USER.

• MSSQL
  Equivalent to Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options.

• MYSQL323
  Equivalent to No_field_options, high_not_precedence.

• MYSQL40
  Equivalent to No_field_options, high_not_precedence.

• ORACLE
  Equivalent to Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options, NO_AUTO_CREATE_ USER.

• POSTGRESQL
  Equivalent to Pipes_as_concat, Ansi_quotes, Ignore_space, No_key_options, No_table_options, No_field_options.

• Traditional
  Equivalent to Strict_trans_tables, Strict_all_tables, No_zero_in_date, No_zero_date, Error_for_division_by_zero, NO_AUTO_CREATE _user.

If you want to assemble different patterns yourself, simply separate the different patterns with commas when set:

MySQL Learning Note-sql Server mode rollup