明珠信息港网站建设专家,会计软件定制开发包括,美化网站公司,奥派网站建设前言
今天同事使用数据库时报错,排查问题时发现配置文件里的sql_mode配置被人注释了,所以通过查询资料对这个配置进行了下了解。
介绍
mysql为了支持在不同的环境下运行#xff0c;允许我们给它设置不同的运行模式#xff08;sql_mode#xff09;。 不同的运行模式#…前言
今天同事使用数据库时报错,排查问题时发现配置文件里的sql_mode配置被人注释了,所以通过查询资料对这个配置进行了下了解。
介绍
mysql为了支持在不同的环境下运行允许我们给它设置不同的运行模式sql_mode。 不同的运行模式mysql处理我们的sql语句也不相同mysql提供了18个运行模式它们可任意组合使用。
sql model 常用来解决下面几类问题
通过设置sql mode, 可以完成不同严格程度的数据校验有效地保障数据准备性。通过设置sql model 为宽松模式来保证大多数sql符合标准的sql语法这样应用在不同数据库之间进行迁移时则不需要对业务sql 进行较大的修改。在不同数据库之间进行数据迁移之前通过设置SQL Mode 可以使MySQL 上的数据更方便地迁移到目标数据库中。
下面对18种运行模式进行简单介绍
1. STRICT_ALL_TABLES
控制是否所有的mysql存储引擎均开启严格模式默认为OFF建议ON 严格模式开启后mysql会对更改数据的行为做严格的校验比如日期的有效性等一般和其他模式配合使用
2. STRICT_TRANS_TABLES 严格模式
这个也是严格模式不同的是上面的会对所有存储引擎开启严格模式而这个会对所有支持事务引擎开启并尽可能对非事务引擎开启。 默认为ON建议ON
3. ALLOW_INVALID_DATES
控制我们能否向date、datetime列插入2020-04-31这种数据4月怎么会有31号呢这是个非法数据。 当OFF时不能插入 当ON时能插入 默认为OFF建议OFF
4. ANSI_QUOTES
是否将双引号也当做对象名称的标识就像 一样。 当为OFF时 用来包裹字符串不能表示表名和列名等 当为ON时不能包裹字符串但它可以包裹表名和列名等
默认为OFF建议OFF在mysql中我们可以仅可以用包裹对象标识名当然也可以不包裹单引号包裹字符串。
5. HIGH_NOT_PRECEDENCE
控制 NOT运算符是否具有更高的优先级。 当OFF时select 1 between -5 and 5等同select not (1 between -5 and 5) 当ON时select 1 between -5 and 5等同select (not 1) between -5 and 5 默认为OFF建议OFF
6. IGNORE_SPACE
控制是否忽略函数名和(之间的空格。 当OFF时函数名和(之间不能有空格 当ON时函数名和(之间可以有空格 默认OFF建议OFF
7. NO_AUTO_VALUE_ON_ZERO
控制当把0插入到自增列时是否触发自增。 当为OFF时将0或null插入到自增列都会触发自增 当为ON时将0插入到自增列不会触发自增因为0会被当做正常数据对待直接插入进去插入null依然会触发自增 默认为OFF建议OFF
8. NO_BACKSLASH_ESCAPES
控制是否将反斜杠识别为转义字符。 当为OFF时反斜杠是转义字符 当为ON时反斜杠就是普通字符不会对其后的字符转义此时mysql中也就没有了转移字符 默认为OFF建议OFF
9. NO_DIR_IN_CREATE
控制是否忽略create table时后面的 DATA DIRECTORY 和 INDEX DIRECTORY 声明。
DATA DIRECTORY 和 INDEX DIRECTORY 声明是当mysql的datedir存储空间不够时允许用户为新建的表指定新的存储地址如 CREATE TABLE t_1 engineinnodb DATA DIRECTORY“/data/lottery/” INDEX DIRECTORY“/data/lottery/”
当为OFF时DATA DIRECTORY 和 INDEX DIRECTORY 继续有效 当为ON时DATA DIRECTORY 和 INDEX DIRECTORY 会被忽略
默认为OFF建议OFF
10. NO_ENGINE_SUBSTITUTION
控制是否自动将用户建表时声明的不可用的存储引擎替换成可用的存储引擎。 比如当我们建表时声明使用某一个存储引擎但是由于服务器问题这个引擎暂时不可用那么此时mysql应该怎么做呢
当为OFF时mysql会使用可用的默认殷勤替代 当为ON时mysql会直接报错
默认ON建议ON
11. NO_UNSIGNED_SUBTRACTION
控制当我们对unsigned int 等做减法时是否将结果转为对应的非unsigned修饰的类型。 当为OFF时unsigned int与int相减结果仍为unsigned int 当为ON时unsigned int与int相减结果为int 默认OFF建议OFF
12. NO_ZERO_DATE
控制是否判别 ‘0000-01-01’(年份为0)为合法的日期配合StrictMode作用。 当为OFF时 0000-01-01’是合法日期 当为ON时 0000-01-01’不是合法日期 默认为OFF建议ON
13. NO_ZERO_IN_DATE
和NO_ZERO_DATE相似不同的是前者控制的是年份后者控制的月份和天如2022-13-32。
默认为OFF建议ON
建议设置为set sql_mode“STRICT_ALL_TABLES,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE”
14. ONLY_FULL_GROUP_BY
控制mysql中 select … group by 语句的写法。
原因是这样的在sql92标准的时候规定如果使用了group by子句那么select子句的列一定被包含在group by 子句中或者使用sum等聚合函数包裹。 然而到了sql99的时候规定select 子句的列也可以不被包含在group by 子句中如select t_id,t_name,t_age from test group by t_id是可以的因为 t_id 是主键。
15. PAD_CHAR_TO_FULL_LENGTH
控制读取char(M)类型的列值时当实际值的长度不到M时是否在默认添加空格以补齐M的长度。 例如我们向char(10)中插入字符串’abc ‘abc3个空格那么我们读取时 当为OFF时读取到的是’abc’后面没有空格因为mysql会自动去掉末尾的空格。 当为ON时读取到的是’abc abc7个空格mysql会自动补齐到长度10; 默认OFF建议OFF。另外这是一个即将被弃用的模式。
16. PIPES_AS_CONCAT
控制是否将 ‘||’ 作为 string之间的链接符号就像 concat(string1,string2) 函数一样。 当为OFF时|| 是 or 的同义词不过mysql建议使用 or 而不是 || 当为ON时|| 和concat函数类似 默认OFF建议OFF
17. REAL_AS_FLOAT
控制是否将 real 当做float的同义词 当为OFF时real是double的同义词 当为ON时real是float的同义词 默认OFF建议OFF
18. TIME_TRUNCATE_FRACTIONAL
控制当时间的毫秒精度超出时是进行四设五入还是直接舍弃多余的位数。 举个例子当将’14:52:12.15’插入到time(1)的时候是插入’14:52:12.2’还是插入’14:52:12.1’ 当为OFF时进行四设五入插入14:52:12.2 当为ON时舍弃多余的位数插入14:52:12.1 默认为OFF建议OFF。
