A brief introduction to the SQL server mode of Mysql

mysql SQL Server Mode
MySQL server can operate in different SQL modes, and different modes can be applied to different clients. This way each application can customize the server's operating mode according to its own needs.
Schema defines which SQL syntax MySQL should support, and what kind of data validation checks should be performed. This makes it easier to use MySQL in different environments and use MySQL with other database servers. You can start mysqld with the --sql-mode="modes" option to set the default SQL mode. This value can also be left empty (--sql-mode = "") if you want to reset it.
You can also use the SET [SESSION|GLOBAL] sql_mode='modes' statement to set the sql_mode
variable after startup to change the SQL mode. Setting a GLOBAL variable requires SUPER permission and affects the operation of all clients connected from that point on. Setting the SESSION variable only affects the current client. Any client can change its session sql_mode value at any time. Modesis is a series of different modes separated by commas (‘,’). You can use the SELECT @@sql_mode statement
to query the current mode. The default value is empty (no mode is set).
Mainly important sql_mode value
・ANSIChange the syntax and behavior to make it more consistent with standard SQL.

・STRICT_TRANS_TABLESIf the given value cannot be inserted into the transaction table, the statement is abandoned. For non-transactional tables, if a value appears in row 1 of a single-row statement or a multi-row statement, the statement is discarded. A more detailed description is given later in this section.

・TRADITIONALMake MySQL behave like a "traditional" SQL database system. A simple description of this mode is to "give an error instead of a warning" when an incorrect value is inserted into a column.
Note: Give up INSERT/UPDATE as soon as an error is found. This is not what you want if you are using a non-transactional storage engine, because the data changes made before the error will not "roll over", and the result is that the update is "only partially made". This manual refers to "strict mode", which means a mode in which at least STRICT _TRANS_TABLES or STRICT _ALL_TABLES is enabled.
All supported modes are described below:

・ALLOW_INVALID_DATESDo not check all dates in strict mode. Only checks months between 1 and 12 and days between 1 and 31. This is important in web applications when you get the year, month, and day from three different fields and want to save exactly what the user inserted (without date validation). This mode works for DATE and DATETIME columns. Not suitable for TIMESTAMP columns, which require date validation.
When strict mode is enabled, the server requires a valid month and day, not just in the ranges of 1 to 12 and 1 to 31 respectively. For example, '2004-04-31' is legal when strict mode is disabled, but illegal when strict mode is enabled. To allow masking of fixed dates in strict mode, ALLOW_INVALID_DATES should also be enabled.

・ANSI_QUOTES Treat '"' as an identifier quote ('`' quote character), not as a quote character for
string. In ANSI mode, You can still quote the identifier using '`'. With ANSI_QUOTES enabled, you cannot use double quotes to quote the string because it is interpreted as an identifier ##・ERROR_FOR_pISION_BY_ZERO##.
#In strict mode, during INSERT or UPDATE, if divided by zero (or MOD(X, 0)), an error is generated (otherwise, a warning), MySQL is divided by zero. Returns NULL. If used in INSERT IGNORE or UPDATE IGNORE, MySQL generates a divide-by-zero warning, but the operation result is NULL. The precedence order of operators
is ExpressionFor example, NOT a BETWEEN b AND c is interpreted as NOT (a BETWEEN b AND c). In some older versions of MySQL, the expression is interpreted as (NOT). a) BETWEEN b AND c. Enable HIGH_NOT_PRECEDENCESQL mode to obtain the previous higher
priority result.

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
Allows spaces between functionnames and '('. Forces all function names to be treated as saved words. The result is that if you want To access a database, table or column name saved as a word, you must quote it. For example, because of the USER() function, the user table name in mysql database and the User column in the table are saved, so You must quote them:

SELECT "User" FROM mysql."user";

・NO_AUTO_CREATE_USER
防止GRANT自动创建新用户，除非还指定了密码。
・NO_AUTO_VALUE_ON_ZERO
NO_AUTO_VALUE_ON_ZERO影响AUTO_INCREMENT列的处理。一般情况，你可以向该列插入NULL或0生成下一个序列号。NO_AUTO_VALUE_ON_ZERO禁用0，因此只有NULL可以生成下一个序列号。
如果将0保存到表的AUTO_INCREMENT列，该模式会很有用。(不推荐采用该惯例)。例如，如果你用mysqldump转储表并重载，MySQL遇到0值一般会生成新的序列号，生成的表的内容与转储的表不同。重载转储文件前启用NO_AUTO_VALUE_ON_ZERO可以解决该问题。mysqldump在输出中自动包括启用NO_AUTO_VALUE_ON_ZERO的语句。
・NO_BACKSLASH_ESCAPES
禁用反斜线字符(‘\')做为字符串内的退出字符。启用该模式，反斜线则成为普通字符。
・NO_DIR_IN_CREATE
创建表时，忽视所有INDEX DIRECTORY和DATA DIRECTORY指令。该选项对从复制服务器有用。
・NO_ENGINE_SUBSTITUTION
如果需要的存储引擎被禁用或未编译，可以防止自动替换存储引擎。
・NO_FIELD_OPTIONS
不要在SHOW CREATE TABLE的输出中打印MySQL专用列选项。该模式在可移植模式（portability mode）下用于mysqldump。
・NO_KEY_OPTIONS
不要在SHOW CREATE TABLE的输出中打印MySQL专用索引选项。该模式在可移植模式（portability mode）下用于mysqldump。
・NO_TABLE_OPTIONS
不要在SHOW CREATE TABLE的输出中打印MySQL专用表选项（例如ENGINE）。该模式在可移植模式（portability mode）下用于mysqldump。
・NO_UNSIGNED_SUBTRACTION
在减运算中，如果某个操作数没有符号，不要将结果标记为UNSIGNED。请注意这样使UNSIGNED BIGINT不能100%用于上下文中。参见12.8节，“Cast函数和操作符”。
・NO_ZERO_DATE
在严格模式，不要将 '0000-00-00'做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式，可以接受该日期，但会生成警告。
・NO_ZERO_IN_DAT
在严格模式，不接受月或日部分为0的日期。如果使用IGNORE选项，我们为类似的日期插入'0000-00-00'。在非严格模式，可以接受该日期，但会生成警告。
・ONLY_FULL_GROUP_BY
不要让GROUP BY部分中的查询指向未选择的列。
・PIPES_AS_CONCAT
将||视为字符串连接操作符（＋）(同CONCAT())，而不视为OR。
・REAL_AS_FLOAT
将REAL视为FLOAT的同义词，而不是DOUBLE的同义词。
・STRICT_TRANS_TABLES
为所有存储引擎启用严格模式。非法数据值被拒绝。后面有详细说明。
・STRICT_TRANS_TABLES
为事务存储引擎启用严格模式，也可能为非事务存储引擎启用严格模式。后面有详细说明。
严格模式控制MySQL如何处理非法或丢失的输入值。有几种原因可以使一个值为非法。例如，数据类型错误，不适合列，或超出范围。当新插入的行不包含某列的没有显示定义DEFAULT子句的值，则该值被丢失。
对于事务表，当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式时，如果语句中有非法或丢失值，则会出现错误。语句被放弃并滚动。
对于非事务表，如果插入或更新的第1行出现坏值，两种模式的行为相同。语句被放弃，表保持不变。如果语句插入或修改多行，并且坏值出现在第2或后面的行，结果取决于启用了哪个严格选项：
・对于STRICT_ALL_TABLES，MySQL返回错误并忽视剩余的行。但是，在这种情况下，前面的行已经被插入或更新。这说明你可以部分更新，这可能不是你想要的。要避免这点，最好使用单行语句，因为这样可以不更改表即可以放弃。
・对于STRICT_TRANS_TABLES，MySQL将非法值转换为最接近该列的合法值并插入调整后的值。如果值丢失，MySQL在列中插入隐式 默认值。在任何情况下，MySQL都会生成警告而不是给出错误并继续执行语句。13.1.5节，“CREATE TABLE语法”描述了隐式默认值。
Strict mode does not allow illegal dates, such as '2004-04-31'. It does not allow forbidden dates using the 'zero' part, such as '2004-04-00' or 'zero' dates. To disable it, enable NO_ZERO_IN_DATE and NO_ZERO_DATE SQL modes based on strict mode.
If you do not use strict mode (i.e. do not enable STRICT_TRANS_TABLES or STRICT_ALL_TABLES mode), MySQL will insert adjusted values ​​and give a warning for illegal or missing values. In strict mode, you can do this via INSERT IGNORE or UPDATE IGNORE. See Section 13.5.4.22, “SHOW WARNINGS Syntax”.

The above is the detailed content of A brief introduction to the SQL server mode of Mysql. For more information, please follow other related articles on the PHP Chinese website!