MySQL基础之SQL_MODE学习笔记

阳光穿透心脏的1/2处 2021-08-13 18:29 347阅读 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';

![在这里插入图片描述][20191010201106249.png]  
在默认情况下查询是这样的，如果设置PAD\_CHAR\_TO\_FULL\_LENGTH，查询出来的是：  
![在这里插入图片描述][20191010201121780.png]

*  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的组合

[20191010201106249.png]: /images/20210813/0415cd1917c34e8c898f23df3b6b7f70.png
[20191010201121780.png]: /images/20210813/9aa59f2e9e5e40c880052c843877c1d4.png