MySQL sql_mode= "" Role description _mysql

Sql_mode= "", that is, forced not to set MySQL mode (such as no input detection, error prompts, syntax mode check, etc.) should improve performance, but the following problems:
If inappropriate data is inserted (error type or paranormal), MySQL sets the data to "best possible data" without an error, such as:
/number set to: 0/possible minimum/possible maximum value
/string set to: empty string/capable of storing maximum capacity strings
/Expression Set to: Returns an available value (1/0-null)
So, the solution is: all columns have default values, which is good for performance.

detailed description of Mysql_mode:

In MySQL 5, the default is Real_as_float,pipes_as_contact,ansi_quotes,gnore_space and ANSI,
In this mode, a value that exceeds the length of the field is allowed to be inserted, but after insertion, the warning is returned instead of the error, and when Strict_trans_tables is used,
It was a mistake, a strict warning.

No_auto_create_user

Prevents grant from automatically creating new users unless a password is also specified.
Here are some of the introductions in the manual

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.
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:
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. · 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:
· Returns an error for Strict_all_tables,mysql and ignores 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.
· 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 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. Describes the implicit default value.
Strict mode does not allow illegal dates, such as ' 2004-04-31′. It does not allow the prohibition date to use 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.
The following special patterns quickly combine the patterns listed previously.
This includes all the schema values in most recent versions of MySQL. In the older version, the combined mode does not include the inappropriate specific schema values that are not available in the new version.
· Ansi
Equal 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
Equal to No_field_options, high_not_precedence.
· MYSQL40
Equal 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