SQL Server Mode

SQL Server Mode

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

· 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

Make MySQL behaves 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 . Note : Once the error is found, discard insert/update immediately. 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".

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

The following describes all of the supported modes:

· 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 ' ' ' as the identifier quotation mark (' ' ' quotation mark character), 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.

· 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. If you use Insert ignore or update ignore, 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.

SET sql_mode = ';

SELECT not 1 BETWEEN-5 and 5;

        0

SET sql_mode = ' broken_not ';

SELECT not 1 BETWEEN-5 and 5;

        1

· 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 mysqldumpunder 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 mysqldumpunder 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 mysqldumpunder 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. 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 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. Detailed instructions are available later.

· Strict_trans_tables

It is also possible to enable strict mode for the transaction store engine, or for non-transactional 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 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. 13.1.5 section, "CREATE table Syntax" describes the implicit default value.

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. See section 13.5, 4.22, "SHOW warnings Syntax".

The following special mode quickly combines 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. See section 1.8.3, "running MySQL in ANSI mode".

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