kavin

MySQL属性SQL_MODE学习笔记

kavin web技术 2023-02-27 578浏览 0

最近在学习《MySQL技术内幕:SQL编程》并做了笔记,本博客是一篇笔记类型博客,分享出来,方便自己以后复习,也可以帮助其他人

SQL_MODE:MySQL特有的一个属性,用途很广,可以通过设置属性来实现某些功能支持

 # 全局的SQL_MODE 
 SELECT @@global.sql_mode;
 # 当前会话的SQL_MODE
 SELECT @@session.sql_mode; 

SQL_Mode默认值是为空的,对于SQL_mode的设置可以在mysql配置文件(my.ini或者my.cnf),或者直接通过命令设置

严格模式:是指将sql_mode设置为STRICT_TRANS_TABLES或者STRICT_ALL_TABLES,设置为严格模式是不允许非法的操作的,比如将Null值写到非空要求的字段里,或者写入不合法的日期数据,比如’2019-09-40′

SET GLOBAL sql_mode ='STRICT_TRANS_TABLES';
SET GLOBAL sql_mode ='STRICT_ALL_TABLES';

数据原本有数据的情况,就不要直接set,用concat连接起来:

set @@session.sql_mode=concat(@@sql_mode,',IGNORE_SPACE');
  • STRICT_TRANS_TABLES:启用了严格模式,只影响事务表,不影响非事务表,如果一个值不能写到事务表(例如存储引擎为InnoDB),就中断当前操作不影响非事务表(例如存储引擎为MYISAM)
  • STRICT_ALL_TABLES:启用STRICT_ALL_TABLES后,对所有引擎的表都启用严格模式
  • ANSI_QUOTES:启用ANSI_QUOTES后,不能用双引号来引用字符串,因为开启这个模式后,双引号被解释为识别符
  • ALLOW_INVALID_DATES:这个模式启用后,将开启对日期的不完全检验,比如只检验月份是否在112,日期是否在131,这种检验对于date、datetime类型的是可以的,但是对于timestamp是没效果的
  • ERROR_FOR_DIVISION_BY_ZERO:在insert或者update过程中,如果数据除以0(或者MOD(
    x,0))会产生错误,如果没开启改模式,则数据除以0时,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 NOT
  • IGNORE_SPACE:忽略函数名和括号之间的空格,这个属性默认是不开启的,一般是不建议开启的,比如某些特殊情况才可以开启,比如 select count (1) from t; count之间有空格会报错,开启后就不会报错,不过一般是不会在函数和括号直接加空格的,除非有表名或者列名也命名为count,这种情况就要加空格,表名,这个count不是表名,而是函数名
  • NO_AUTO_CREATE_USER:禁止GRANT创建密码为空的用户
  • NO_AUTO_VALUE_ON_ZERO:这个属性是设置对于自动增长的列不允许写0值,也写入了0或者null,不会写0,假如写入了0,数据表里本来没数据,就会写1,以此类推
  • NO_BACKSLASH_ESCAPES:反斜杆“\”作为普通字符而非转义符
  • NO_DIR_IN_CREATE:在创建表时忽略所有INDEX DIRECTORY和DATE DIRECTORY的选项
  • NO_ENGINE_SUBTRACTION:用到的存储引擎被禁用或者未编译,就用默认的存储引擎,并且抛出异常,
  • NO_UNSIGNED_SUBTRACTION:启用这个属性后,两个unsigned类型相减返回signed类型
  • NO_ZERO_DATE:不允许写入为0格式的日期,比如“0000-00-00 00:00:00”,启用这个属性后,写入这种类型数据就会抛异常
  • NO_ZERO_IN_DATE:在严格模式下,不允许日期和月份为零的情况
  • ONLY_FULL_GROUP_BY:如果select出现的列没有在group by中就会报错
  • PAD_CHAR_TO_FULL_LENGTH:对于char类型字段,查询时候不要截取空洞数据,所谓空洞数据就是自动填充0x20的数据
CREATE TABLE t (a CHAR(10));
INSERT INTO t SELECT 'a';

MySQL属性SQL_MODE学习笔记
在默认情况下查询是这样的,如果设置PAD_CHAR_TO_FULL_LENGTH,查询出来的是:
MySQL属性SQL_MODE学习笔记

  • REAL_AS_FLOAT:将REAL作为FLOAT的同义词,而不是double的同义词
  • PIPES_AS_CONCAT:将“||”视为字符串的连接操作符,而非或运算符,这个就和oracle是一样的

下面给出几种选项的组合:

  • ANSI:等同于RELA_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的组合

继续浏览有关 数据库技术文章/教程 的文章
发表评论