澳门新萄京STCRUISERICT_TRANS_TABLES使用图解教程,
分类:数据库

sql_mode="",即强制不设定MySql模式应该能提高性能,但有如下问题: 如果插入了不合适数据,mysql会将数据设为“最好的可能数据”而不报错,如: /数字 设为:0/可能最小值/可能最大值 /字符串 设为:空串/能够存储的最大容量字符串 /表达式 设为:返回一个可用值 所以,解决办法是:所有列都要采用默认值,这对性能也好。 mysql_mode的详细描述: 在mysql 5中,默认的是REAL_AS_FLOAT,PIPES_AS_CONTACT,ANSI_QUOTES,GNORE_SPACE和ANSI, 在这种模式下,允许插入超过字段长度的值,只是插入后,返回的是警告而不是错误,当用STRICT_TRANS_TABLES时, 则是错误了,严格的警告. NO_AUTO_CREATE_USER 防止GRANT自动创建新用户,除非还指定了密码。 下面是手册中的一些介绍 下面描述了支持的所有模式: · ALLOW_INVALID_DATES 在严格模式下不要检查全部日期。只检查1到12之间的月份和1到31之间的 日。这在Web应用程序中,当你从三个不同的字段获取年、月、日,并且想要确切保存用户插入的内容时很重要。该模式适用于DATE和 DATETIME列。不适合TIMESTAMP列,TIMESTAMP列需要验证日期。 启用严格模式后,服务器需要合法的月和日,不仅仅是分别在1到12和1到31范围内。例如,禁用严格模式时'2004-04-31′是合法的,但启用严格模式后是非法的。要想在严格模式允许遮掩固定日期,还应启用ALLOW_INVALID_DATES。 · ANSI_QUOTES 将‘”'视为识别符引号,不要视为字符串的引号字符。在ANSI模式,你可以仍然使用‘`'来引用识别符。启用ANSI_QUOTES后,你不能用双引号来引用字符串,因为它被解释为识别符。 · ERROR_FOR_DIVISION_BY_ZERO 在严格模式,在INSERT或UPDATE过程中,如果被零除,则产生错误。如果未给出该模式,被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL。 · HIGH_NOT_PRECEDENCE NOT 操作符的优先顺序是表达式例如NOT a BETWEEN b AND c被解释为NOT 。在一些旧版本MySQL中, 表达式被解释为 BETWEEN b AND c。启用HIGH_NOT_PRECEDENCESQL模式,可以获得以前的更高优先级的结果。 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 允许函数名和‘('之间有空格。强制将所有函数名视为保存的字。结果是,如果你想要访问保存为字的数据库、表或列名,你必须引用它。例如,因为有USER()函数,mysql数据库中的user表名和该表内的User列被保存下来,因此你必须引用它们: 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专用列选项。该模式在可移植模式下用于mysqldump。 · NO_KEY_OPTIONS 不要在SHOW CREATE TABLE的输出中打印MySQL专用索引选项。该模式在可移植模式下用于mysqldump。 · NO_TABLE_OPTIONS 不要在SHOW CREATE TABLE的输出中打印MySQL专用表选项。该模式在可移植模式下用于mysqldump。 · NO_UNSIGNED_SUBTRACTION 在减运算中,如果某个操作数没有符号,不要将结果标记为UNSIGNED。请注意这样使UNSIGNED BIGINT不能100%用于上下文中。 · NO_ZERO_DATE 在严格模式,不要将 ‘0000-00-00′做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式,可以接受该日期,但会生成警告。 · NO_ZERO_IN_DATE 在严格模式,不接受月或日部分为0的日期。如果使用IGNORE选项,我们为类似的日期插入'0000-00-00′。在非严格模式,可以接受该日期,但会生成警告。 · ONLY_FULL_GROUP_BY 不要让GROUP BY部分中的查询指向未选择的列。 · PIPES_AS_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都会生成警告而不是给出错误并继续执行语句。描述了隐式默认值。 严格模式不允许非法日期,例如'2004-04-31′。它不允许禁止日期使用“零”部分,例如'2004-04-00′或“零”日期。要想禁止,应在严格模式基础上,启用NO_ZERO_IN_DATE和NO_ZERO_DATE SQL模式。 如果你不使用严格模式(即不启用STRICT_TRANS_TABLES或STRICT_ALL_TABLES模式),对于非法或丢失的值,MySQL将插入调整后的值并给出警告。在严格模式,你可以通过INSERT IGNORE或UPDATE IGNORE来实现。 下面的特殊模式快速组合了前面所列的模式。 其中包括大多数最新版本MySQL中的所有模式值。旧版本中,组合模式不包括新版本中没有的不适用的具体模式值。 · ANSI 等同REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE。 · DB2 等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS。 · MAXDB 等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER。 · MSSQL 等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS。 · MYSQL323 等同NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCE。 · MYSQL40 等同NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCE。 · ORACLE 等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER。 · POSTGRESQL 等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS。 · TRADITIONAL 等同STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER

MYSQL STRICT_TRANS_TABLES使用图解教程

sql_mode="",即强制不设定MySql模式(如不作输入检测、错误提示、语法模式检查等)应该能提高性能,但有如下问题:
如果插入了不合适数据(错误类型或超常),mysql会将数据设为“最好的可能数据”而不报错,如:
/数字 设为:0/可能最小值/可能最大值
/字符串 设为:空串/能够存储的最大容量字符串
/表达式 设为:返回一个可用值(1/0-null)
所以,解决办法是:所有列都要采用默认值,这对性能也好。

转载自:

   author:skate

 

mysql_mode的详细描述:

MySQL数据类型:SQL_MODE设置不容忽视

2012-08-23 00:05    it168网站原创  作者: 姜承尧 编辑: 王玉圆 

        【IT168 技术】SQL_MODE可能是比较容易让开发人员和DBA忽略的一个变量,默认为空。SQL_MODE的设置其实是比较冒险的一种设置,因为在这种设置下可以允许一些非法操作,比如可以将NULL插入NOT NULL的字段中,也可以插入一些非法日期,如“2012-12-32”。因此在生产环境中强烈建议开发人员将这个值设为严格模式,这样有些问题可以在数据库的设计和开发阶段就能发现,而如果在生产环境下运行数据库后发现这类问题,那么修改的代价将变得十分巨大。此外,正确地设置SQL_MODE还可以做一些约束(Constraint)检查的工作。

  对于SQL_MODE的设置,可以在MySQL的配置文件如my.cnf和my.ini中进行,也可以在客户端工具中进行,并且可以分别进行全局的设置或当前会话的设置。下面的命令可以用来查看当前SQL_MODE的设置情况。

  mysql> SELECT @@global.sql_modeG; //查询全局的sql_mode变量值。

  *************************** 1. row ***************************

  @@global.sql_mode:

  1 row in set (0.00 sec)

  mysql> SELECT @@session.sql_modeG; //查询当前会话的sql_mode值。

或  

    SELECT @@sql_modeG

  *************************** 1. row ***************************

  @@session.sql_mode: NO_UNSIGNED_SUBTRACTION

  1 row in set (0.00 sec)

  可以看到当前全局的SQL_MODE设置为空,而当前会话的设置为NO_UNSIGNED_SUBTRACTION。通过以下语句可以将当前的SQL_MODE设置为严格模式。

  mysql> SET GLOBAL sql_mode='strict_trans_tables'; //设置全局的sql_mode值。

  Query OK, 0 rows affected (0.00 sec)

  也可以设置当前会话的SQL_MODE的值:

  mysql>SET SESSION SQL_MODE='PAD_CHAR_TO_FULL_LENGTH'; 或者

  mysql>SET SQL_MODE='PAD_CHAR_TO_FULL_LENGTH';

  严格模式是指将SQL_MODE变量设置为STRICT_TRANS_TABLES或STRICT_ALL_TABLES中的至少一种。现在来看一下SQL_MODE可以设置的选项。

  STRICT_TRANS_TABLES:在该模式下,如果一个值不能插入到一个事务表(例如表的存储引擎为InnoDB)中,则中断当前的操作不影响非事务表(例如表的存储引擎为MyISAM)。

  ALLOW_INVALID_DATES:该选项并不完全对日期的合法性进行检查,只检查月份是否在1~12之间,日期是否在1~31之间。该模式仅对DATE和DATETIME类型有效,而对TIMESTAMP无效,因为TIMESTAMP总是要求一个合法的输入。

  ANSI_QUOTES:启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它将被解释为识别符,示例如下:

  mysql> CREATE TABLE z ( a VARCHAR(10))ENGINE=INNODB;

  Query OK, 0 rows affected (0.00 sec)

  mysql>INSERT INTO z SELECT "aaa";

  Query OK, 1 rows affected (0.00 sec)

  mysql> SET sql_mode='ANSI_QUOTES';

  Query OK, 0 rows affected (0.00 sec)

  mysql> INSERT INTO z SELECT "aaa";

  ERROR 1054 (42S22): Unknown column 'aaa' in 'field list'

  ERROR_FOR_DIVISION_BY_ZERO:在INSERT或UPDATE过程中,如果数据被零除(或MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,那么数据被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL。

  HIGH_NOT_PRECEDENCE NOT:操作符的优先顺序是表达式。例如,NOT a BETWEEN b AND c被解释为NOT(a BETWEEN b AND c),在一些旧版本MySQL中, 前面的表达式被解释为(NOT a)BETWEEN b AND c。启用HIGH_NOT_PRECEDENCE SQL模式,可以获得以前旧版本的更高优先级的结果。下面看一个例子:

  mysql> SELECT 0 BETWEEN -1 AND 1G;

  *************************** 1. row ***************************

  0 BETWEEN -1 AND 1: 1

  1 row in set (0.00 sec)

  0在-1到1之间,所以返回1,如果加上NOT,则返回0,过程如下:

  mysql> SELECT @@sql_modeG;

  *************************** 1. row ***************************

  @@sql_mode:

  1 row in set (0.00 sec)

  mysql> SELECT not 0 BETWEEN -1 AND 1G;

  *************************** 1. row ***************************

  NOT 0 BETWEEN -1 AND 1: 0

  1 row in set (0.00 sec)

  但是如果启用HIGH_NOT_PRECEDENCE模式,则SELECT NOT 0 BETWEEN -1 AND 1被解释为SELECT(NOT 0)BETWEEN -1 AND 1,结果就完全相反,如下所示:

  mysql> SELECT NOT 0 BETWEEN -1 AND 1G;

  *************************** 1. row ***************************

  NOT 0 BETWEEN -1 AND 1: 1

  1 row in set (0.00 sec)

  从上述例子中还能看出,在MySQL数据库中BETWEEN a AND b被解释为[a,b]。下面做两个简单的测试。

  mysql> SELECT 1 BETWEEN -1 AND 1G;

  *************************** 1. row ***************************

  1 BETWEEN -1 AND 1: 1

  1 row in set (0.00 sec)

  mysql> SELECT -1 BETWEEN -1 AND 1G;

  *************************** 1. row ***************************

  -1 BETWEEN -1 AND 1: 1

  1 row in set (0.00 sec)

  IGNORE_SPACE:函数名和括号“(”之间有空格。除了增加一些烦恼,这个选项好像没有任何好处,要访问保存为关键字的数据库、表或列名,用户必须引用该选项。例如某个表中有user这一列,而MySQL数据库中又有user这个函数, user会被解释为函数,如果想要选择user这一列,则需要引用。

  NO_AUTO_CREATE_USER:禁止GRANT创建密码为空的用户。

  NO_AUTO_VALUE_ON_ZERO:该选项影响列为自增长的插入。在默认设置下,插入0或NULL代表生成下一个自增长值。如果用户希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

  NO_BACKSLASH_ESCAPES:反斜杠“”作为普通字符而非转义符,示例如下:

  mysql> SET sql_mode='';

  Query OK, 0 rows affected (0.00 sec)

  mysql> SELECT '\'G;

  *************************** 1. row ***************************

  : 

  1 row in set (0.00 sec)

  mysql> SET sql_mode='NO_BACKSLASH_ESCAPES';

  Query OK, 0 rows affected (0.00 sec)

  mysql> SET '\'G;

  *************************** 1. row ***************************

  \: \

  1 row in set (0.00 sec)

  NO_DIR_IN_CREATE:在创建表时忽视所有INDEX DIRECTORY和DATA DIRECTORY的选项。

  NO_ENGINE_SUBSTITUTION:如果需要的存储引擎被禁用或未编译,那么抛出错误。默认用默认的存储引擎替代,并抛出一个异常。

  NO_UNSIGNED_SUBTRACTION:之前已经介绍过,启用这个选项后两个UNSIGNED类型相减返回SIGNED类型。

  NO_ZERO_DATE:在非严格模式下,可以插入形如“0000-00-00 00:00:00”的非法日期,MySQL数据库仅抛出一个警告。而启用该选项后,MySQL数据库不允许插入零日期,插入零日期会抛出错误而非警告。

  NO_ZERO_IN_DATE:在严格模式下,不允许日期和月份为零。如“2011-00-01”和“2011-01-00”这样的格式是不允许的。采用日期或月份为零的格式时MySQL都会直接抛出错误而非警告,示例如下:

  mysql> SET sql_mode='NO_ZERO_IN_DATE';

  Query OK, 0 rows affected (0.00 sec)

  mysql> CREATE TABLE a ( a DATETIME );

  Query OK, 0 rows affected (0.04 sec)

  mysql> INSERT INTO a SELECT '2011-01-00';

  ERROR 1292 (22007): Incorrect datetime value: '2011-01-00' for column 'a' at row 1

  ONLY_FULL_GROUP_BY:对于GROUP BY聚合操作,如果在SELECT中的列没有在GROUP BY中出现,那么这句SQL是不合法的,因为a列不在GROUP BY从句中,示例如下:

  mysql> SET sql_mode='ONLY_FULL_GROUP_BY';

  Query OK, 0 rows affected (0.00 sec)

  mysql> SELECT a,SUM(b) FROM t GROUP BY b;

  ERROR 1055 (42000): 'test.t.a' isn't in GROUP BY

  PAD_CHAR_TO_FULL_LENGTH:对于CHAR类型字段,不要截断空洞数据。空洞数据就是自动填充值为0x20的数据。先来看MySQL数据库在默认情况下的表现。

  mysql> CREATE TABLE t ( a CHAR(10) );

  Query OK, 0 rows affected (0.04 sec)

  mysql> INSERT INTO t SELECT 'a';

  Query OK, 1 row affected (0.01 sec)

  Records: 1 Duplicates: 0 Warnings: 0

  mysql>SELECT a,CHAR_LENGTH(a),HEX(a) FROM tG;

  *************************** 1. row ***************************

  a: a

  CHAR_LENGTH(a): 1

  HEX(a): 61

  1 row in set (0.04 sec)

  可以看到,在默认情况下,虽然a列是CHAR类型,但是返回的长度是1,这是因为MySQL数据库已经对后面的空洞数据进行了截断。若启用PAD_CHAR_TO_FULL_LENGTH选项,则反映的是实际存储的内容,例如:

  mysql> SELECT a,CHAR_LENGTH(a),HEX(a) FROM tG;

  *************************** 1. row ***************************

  a: a

  CHAR_LENGTH(a): 10

  HEX(a): 61202020202020202020

  1 row in set (0.00 sec)

  可以看到在CHAR列a中实际存储的值为0x61202020202020202020。

  PIPES_AS_CONCAT:将“||”视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似,例如:

  mysql> SET sql_mode='pipes_as_concat';

  Query OK, 0 rows affected (0.01 sec)

  mysql> SELECT 'a'||'b'||'c'G;

  *************************** 1. row ***************************

  'a'||'b'||'c': abc

  1 row in set (0.00 sec)

  REAL_AS_FLOAT:将REAL视为FLOAT的同义词,而不是DOUBLE的同义词。

  STRICT_ALL_TABLES:对所有引擎的表都启用严格模式。(STRICT_TRANS_TABLES只对支持事务的表启用严格模式)。

  在严格模式下,一旦任何操作的数据产生问题,都会终止当前的操作。对于启用STRICT_ALL_TABLES选项的非事务引擎来说,这时数据可能停留在一个未知的状态。这可能不是所有非事务引擎愿意看到的一种情况,因此需要非常小心这个选项可能带来的潜在影响。

  下面的几种SQL_MODE设置是之前讨论的几种选项的组合。

  ANSI:等同于REAL_AS_FLOAT、PIPES_AS_CONCAT和ANSI_QUOTES、IGNORE_SPACE的组合。

  ORACLE:等同于PIPES_AS_CONCAT、 ANSI_QUOTES、IGNORE_SPACE、 NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS和NO_AUTO_CREATE_USER的组合。

  TRADITIONAL:等同于STRICT_TRANS_TABLES、 STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、 ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER和 NO_ENGINE_SUBSTITUTION的组合。

  MSSQL:等同于PIPES_AS_CONCAT、 ANSI_QUOTES、 IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS和 NO_FIELD_OPTIONS的组合。

  DB2:等同于PIPES_AS_CONCAT、ANSI_QUOTES、 IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS和NO_FIELD_OPTIONS的组合。

  MYSQL323:等同于NO_FIELD_OPTIONS和HIGH_NOT_PRECEDENCE的组合。

  MYSQL40:等同于NO_FIELD_OPTIONS和HIGH_NOT_PRECEDENCE的组合。

  MAXDB:等同于PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、 NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS和 NO_AUTO_CREATE_USER的组合。

  time:2013/04/11

mysql 在insert的时候,常常提示没有默认值,MySQL 1364 错误提示:#1364 - Field "XXX" doesn't have a default value。XXX字段没有默认的数值;也就是说我们没有为其分配数值,而表中此字段也没有设置默认值。这是mysql模式开启存储引擎启用严格模式,非法数据值被拒绝, 我把操作实际问题贴一下图:

在mysql 5中,默认的是REAL_AS_FLOAT,PIPES_AS_CONTACT,ANSI_QUOTES,GNORE_SPACE和ANSI,
在这种模式下,允许插入超过字段长度的值,只是插入后,返回的是警告而不是错误,当用STRICT_TRANS_TABLES时,
则是错误了,严格的警告.

  mysql的sql_mode合理设置

澳门新萄京 1  

NO_AUTO_CREATE_USER

  sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,比如允许一些非法数据的插入。在生产环境必须将这个值设置为严格模式,所以开发、测试环境的数据库也必须要设置,这样在开发测试阶段就可以发现问题

而我的Tbl_User表里面字段:

防止GRANT自动创建新用户,除非还指定了密码。
下面是手册中的一些介绍

  sql_mode常用值如下:

   www.2cto.com  
澳门新萄京 2  

下面描述了支持的所有模式:
· ALLOW_INVALID_DATES
在严格模式下不要检查全部日期。只检查1到12之间的月份和1到31之间的 日。这在Web应用程序中,当你从三个不同的字段获取年、月、日,并且想要确切保存用户插入的内容(不进行日期验证)时很重要。该模式适用于DATE和 DATETIME列。不适合TIMESTAMP列,TIMESTAMP列需要验证日期。
启用严格模式后,服务器需要合法的月和日,不仅仅是分别在1到12和1到31范围内。例如,禁用严格模式时'2004-04-31′是合法的,但启用严格模式后是非法的。要想在严格模式允许遮掩固定日期,还应启用ALLOW_INVALID_DATES。
· ANSI_QUOTES
将‘”'视为识别符引号(‘`'引号字符),不要视为字符串的引号字符。在ANSI模式,你可以仍然使用‘`'来引用识别符。启用ANSI_QUOTES后,你不能用双引号来引用字符串,因为它被解释为识别符。
· ERROR_FOR_DIVISION_BY_ZERO
在严格模式,在INSERT或UPDATE过程中,如果被零除(或 MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL。
· HIGH_NOT_PRECEDENCE
NOT 操作符的优先顺序是表达式例如NOT a BETWEEN b AND c被解释为NOT (a BETWEEN b AND c)。在一些旧版本MySQL中, 表达式被解释为(NOT a) BETWEEN b AND c。启用HIGH_NOT_PRECEDENCESQL模式,可以获得以前的更高优先级的结果。
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
允许函数名和‘('之间有空格。强制将所有函数名视为保存的字。结果是,如果你想要访问保存为字的数据库、表或列名,你必须引用它。例如,因为有USER()函数,mysql数据库中的user表名和该表内的User列被保存下来,因此你必须引用它们:
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%用于上下文中。 · NO_ZERO_DATE
在严格模式,不要将 ‘0000-00-00′做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式,可以接受该日期,但会生成警告。
· NO_ZERO_IN_DATE
在严格模式,不接受月或日部分为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都会生成警告而不是给出错误并继续执行语句。描述了隐式默认值。
严格模式不允许非法日期,例如'2004-04-31′。它不允许禁止日期使用“零”部分,例如'2004-04-00′或“零”日期。要想禁止,应在严格模式基础上,启用NO_ZERO_IN_DATE和NO_ZERO_DATE SQL模式。
如果你不使用严格模式(即不启用STRICT_TRANS_TABLES或STRICT_ALL_TABLES模式),对于非法或丢失的值,MySQL将插入调整后的值并给出警告。在严格模式,你可以通过INSERT IGNORE或UPDATE IGNORE来实现。
下面的特殊模式快速组合了前面所列的模式。
其中包括大多数最新版本MySQL中的所有模式值。旧版本中,组合模式不包括新版本中没有的不适用的具体模式值。
· ANSI
等同REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE。
· DB2
等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS。
· MAXDB
等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER。
· MSSQL
等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS。
· MYSQL323
等同NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCE。
· MYSQL40
等同NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCE。
· ORACLE
等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER。
· POSTGRESQL
等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS。
· TRADITIONAL
等同STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER

  ONLY_FULL_GROUP_BY:

解决方法:就是在mysql的配置文件my.ini里面

如果插入了不合适数据...

  对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中

 

  NO_AUTO_VALUE_ON_ZERO:

XML/HTML代码

  该值影响自增长列的插入。默认设置下,插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0,而该列又是自增长的,那么这个选项就有用了。

01.# Set the SQL mode to strict # sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 

  STRICT_TRANS_TABLES:

02.改为: 

  在该模式下,如果一个值不能插入到一个事务表中,则中断当前的操作,对非事务表不做限制

03. 

  NO_ZERO_IN_DATE:

04.sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" 

  在严格模式下,不允许日期和月份为零

 

  NO_ZERO_DATE:

把严格模式去掉,得启mysql即可

  设置该值,mysql数据库不允许插入零日期,插入零日期会抛出错误而不是警告。

 

  ERROR_FOR_DIVISION_BY_ZERO:

贴一下网上有关模式详细介绍:

  在INSERT或UPDATE过程中,如果数据被零除,则产生错误而非警告。如 果未给出该模式,那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER

  NO_AUTO_CREATE_USER:

  www.2cto.com  

  禁止GRANT创建密码为空的用户

防止GRANT自动创建新用户,除非还指定了密码。

  NO_ENGINE_SUBSTITUTION:

NO_UNSIGNED_SUBTRACTION 

  如果需要的存储引擎被禁用或未编译,那么抛出错误。不设置此值时,用默认的存储引擎替代,并抛出一个异常

在减运算中,如果某个操作数没有符号,不要将结果标记为UNSIGNED。请注意这样使UNSIGNED BIGINT不能100%用于上下文中。

  PIPES_AS_CONCAT:

 

  将"||"视为字符串的连接操作符而非或运算符,这和Oracle数据库是一样的,也和字符串的拼接函数Concat相类似

再贴一下其它几种模式:

  ANSI_QUOTES:

在mysql 5中,默认的是REAL_AS_FLOAT,PIPES_AS_CONTACT,ANSI_QUOTES,GNORE_SPACE和ANSI,

  启用ANSI_QUOTES后,不能用双引号来引用字符串,因为它被解释为识别符

在这种模式下,允许插入超过字段长度的值,只是插入后,返回的是警告而不是错误,当用STRICT_TRANS_TABLES时,

  ORACLE的sql_mode设置等同:PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

则是错误了,严格的警告.

  如果使用mysql,为了继续保留大家使用oracle的习惯,可以对mysql的sql_mode设置如下:

 

  在my.cnf添加如下配置

NO_AUTO_CREATE_USER

  [mysqld]

 

  sql_mode='ONLY_FULL_GROUP_BY,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,

防止GRANT自动创建新用户,除非还指定了密码。

  ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,PIPES_AS_CONCAT,ANSI_QUOTES'

下面是手册中的一些介绍

time:2013/04/11 mysql的sql_mode合理设置 sql_mode是个很容易被忽视的变量,默认值是空值,在这种设置下是可以允许一些非法操作的,...

 

下面描述了支持的所有模式:

澳门新萄京STCRUISERICT_TRANS_TABLES使用图解教程,MySQL系统变量。· ALLOW_INVALID_DATES

 

在严格模式下不要检查全部日期。只检查1到12之间的月份和1到31之间的 日。这在Web应用程序中,当你从三个不同的字段获取年、月、日,并且想要确切保存用户插入的内容(不进行日期验证)时很重要。该模式适用于DATE和 DATETIME列。不适合TIMESTAMP列,TIMESTAMP列需要验证日期。

 

启用严格模式后,服务器需要合法的月和日,不仅仅是分别在1到12和1到31范围内。例如,禁用严格模式时’2004-04-31′是合法的,但启用严格模式后是非法的。要想在严格模式允许遮掩固定日期,还应启用ALLOW_INVALID_DATES。

  www.2cto.com  

· ANSI_QUOTES

 

将‘”’视为识别符引号(‘`’引号字符),不要视为字符串的引号字符。在ANSI模式,你可以仍然使用‘`’来引用识别符。启用ANSI_QUOTES后,你不能用双引号来引用字符串,因为它被解释为识别符。

 

· ERROR_FOR_DIVISION_BY_ZERO

 

在严格模式,在INSERT或UPDATE过程中,如果被零除(或 MOD(X,0)),则产生错误(否则为警告)。如果未给出该模式,被零除时MySQL返回NULL。如果用到INSERT IGNORE或UPDATE IGNORE中,MySQL生成被零除警告,但操作结果为NULL。

 

· HIGH_NOT_PRECEDENCE

 

NOT 操作符的优先顺序是表达式例如NOT a BETWEEN b AND c被解释为NOT (a BETWEEN b AND c)。在一些旧版本MySQL中, 表达式被解释为(NOT a) BETWEEN b AND c。启用HIGH_NOT_PRECEDENCESQL模式,可以获得以前的更高优先级的结果。

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

 

允许函数名和‘(’之间有空格。强制将所有函数名视为保存的字。结果是,如果你想要访问保存为字的数据库、表或列名,你必须引用它。例如,因为有USER()函数,mysql数据库中的user表名和该表内的User列被保存下来,因此你必须引用它们:

SELECT "User" FROM mysql."user"; 

· NO_AUTO_CREATE_USER

 

防止GRANT自动创建新用户,除非还指定了密码。

  www.2cto.com  

· 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

  www.2cto.com  

不要在SHOW CREATE TABLE的输出中打印MySQL专用表选项(例如ENGINE)。该模式在可移植模式(portability mode)下用于mysqldump。

 

· NO_UNSIGNED_SUBTRACTION

 

在减运算中,如果某个操作数没有符号,不要将结果标记为UNSIGNED。请注意这样使UNSIGNED BIGINT不能100%用于上下文中。 · NO_ZERO_DATE

 

在严格模式,不要将 ‘0000-00-00′做为合法日期。你仍然可以用IGNORE选项插入零日期。在非严格模式,可以接受该日期,但会生成警告。

 

· NO_ZERO_IN_DATE

 

在严格模式,不接受月或日部分为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子句的值,则该值被丢失。  www.2cto.com  

 

对于事务表,当启用STRICT_ALL_TABLES或STRICT_TRANS_TABLES模式时,如果语句中有非法或丢失值,则会出现错误。语句被放弃并滚动。

 

对于非事务表,如果插入或更新的第1行出现坏值,两种模式的行为相同。语句被放弃,表保持不变。如果语句插入或修改多行,并且坏值出现在第2或后面的行,结果取决于启用了哪个严格选项:

 

· 对于STRICT_ALL_TABLES,MySQL返回错误并忽视剩余的行。但是,在这种情况下,前面的行已经被插入或更新。这说明你可以部分更新,这可能不是你想要的。要避免这点,最好使用单行语句,因为这样可以不更改表即可以放弃。

 

· 对于STRICT_TRANS_TABLES,MySQL将非法值转换为最接近该列的合法值并插入调整后的值。如果值丢失,MySQL在列中插入隐式默认值。在任何情况下,MySQL都会生成警告而不是给出错误并继续执行语句。描述了隐式默认值。

 

严格模式不允许非法日期,例如’2004-04-31′。它不允许禁止日期使用“零”部分,例如’2004-04-00′或“零”日期。要想禁止,应在严格模式基础上,启用NO_ZERO_IN_DATE和NO_ZERO_DATE SQL模式。

  www.2cto.com  

如果你不使用严格模式(即不启用STRICT_TRANS_TABLES或STRICT_ALL_TABLES模式),对于非法或丢失的值,MySQL将插入调整后的值并给出警告。在严格模式,你可以通过INSERT IGNORE或UPDATE IGNORE来实现。

 

下面的特殊模式快速组合了前面所列的模式。

 

其中包括大多数最新版本MySQL中的所有模式值。旧版本中,组合模式不包括新版本中没有的不适用的具体模式值。

 

等同REAL_AS_FLOAT、PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE。

 

· DB2

 

等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS。

 

· MAXDB

 

等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、 NO_AUTO_CREATE_USER。

 

· MSSQL

 

等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、 NO_FIELD_OPTIONS。

 

· MYSQL323

 

等同NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCE。

 

· MYSQL40

  www.2cto.com  

等同NO_FIELD_OPTIONS、HIGH_NOT_PRECEDENCE。

 

· ORACLE

 

等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS、NO_AUTO_CREATE_USER。

 

· POSTGRESQL

 

等同PIPES_AS_CONCAT、ANSI_QUOTES、IGNORE_SPACE、NO_KEY_OPTIONS、NO_TABLE_OPTIONS、NO_FIELD_OPTIONS。

 

· TRADITIONAL

 

等同STRICT_TRANS_TABLES、STRICT_ALL_TABLES、NO_ZERO_IN_DATE、NO_ZERO_DATE、ERROR_FOR_DIVISION_BY_ZERO、NO_AUTO_CREATE_USER

 

(二)sql 代码

1.select @@sql_mode; 

2. 

3.CREATE TABLE `t` ( 

  1. `name` varchar(20), 

  2. `email` varchar(40) 

6.);   www.2cto.com  

7.desc t; 

8. 

9.set session sql_mode="REAL_AS_澳门新萄京STCRUISERICT_TRANS_TABLES使用图解教程,MySQL系统变量。FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI"; 

10. 

11.insert into t(`name`, `email`) values("12345678901234567890999999", "[email protected]"); 

12. 

13.set session sql_mode="STRICT_TRANS_TABLES"; 

 

 

本文来自于知行网

STRICT_TRANS_TABLES使用图解教程 mysql 在insert的时候,常常提示没有默认值,MySQL 1364 错误提示:#1364 - Field XXX doesnt have a default value。XXX字段...

本文由澳门新萄京发布于数据库,转载请注明出处:澳门新萄京STCRUISERICT_TRANS_TABLES使用图解教程,

上一篇:澳门新萄京MySql存储过程中limit传参,Stmt预处理提 下一篇:没有了
猜你喜欢
热门排行
精彩图文