前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >【数据库】MySql的sql_mode模式说明

【数据库】MySql的sql_mode模式说明

原创
作者头像
参谋带个长
发布2023-04-28 09:24:50
1.6K0
发布2023-04-28 09:24:50
举报
文章被收录于专栏:服务器运维日常

什么是严格模式

mysql5.7+的版本中,sql_mode为严格模式。比如必须给字段默认值,更新数据的长度必须符合限制等。

参考:MySQL中和text相关的类型最大长度限制,请根据业务类型进行合理选择:

TINYTEXT 最大长度是 255 (2^8 – 1) 个字符。

TEXT 最大长度是 65535 (2^16 – 1) 个字符。

MEDIUMTEXT 最大长度是 16777215 (2^24 – 1) 个字符。

LONGTEXT 最大长度是 4294967295 (2^32 – 1) 个字符。

sql_mode的常见设置

ANSI模式 :宽松模式,对插入数据进行校验,如果不符合定义类型或长度,对数据类型调整或截断保存,报warning警告。

TRADITIONAL模式 :严格模式,当向mysql数据库插入数据时,进行数据的严格校验,保证错误数据不能插入,报error错误。用于事物时,会进行事物的回滚。

STRICT_TRANS_TABLES模式 :严格模式,进行数据的严格校验,错误数据不能插入,报error错误。只对支持事务的表有效。

STRICT_ALL_TABLES模式 :严格模式,进行数据的严格校验,错误数据不能插入,报error错误。对所有表都有效。

不同版本的Mysql的sql_mode默认值

Mysql 5.6 :Default Value 为 NO_ENGINE_SUBSTITUTION

Mysql 5.7 :Default Value 为 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Mysql 8.0 :Default Value 为 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

各参数值的说明

ALLOW_INVALID_DATES :字母意思允许不合法的数据。不对日期做全面的检查,仅仅检查月份是否在1~12之间,天数是否在1~31之间;这种模式可能是有用的对web应用来说去获取年,月,日在三个不同的字段并且准确存储用户的输入数值,没有验证数据的合法性。这种模式对date和datetime类型有作用,但是对timestamp类型不起作用,timestamp总是要合法的数据。当ALLOW_INVALID_DATES启用时,服务端要求年和月时合法的。如果strict模式禁用,不合法的数据如”2004-04-31″被存储为”0000-00-00″并且审查警告;若严格模式启用则会生成错误。(测试的时候,数据时原样插入的,没有转换为0000-00-00)

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

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

HIGH_NOT_PRECEDENCE : (高not优先级):在如下一个表达式中not a between b and c,在当前设置下被解析为not (a between b and c),在旧的行为中同一个表达式被解析为(not a) between b and c;启用了HIGH_NOT_PRECEDENCE这个模式,就是启用来旧的优先级设置,表达式将被解析为后一种行为。

IGNORE_SPACE :用于忽略mysql系统函数名与之后的括号之间的空格、列如:count () 通过设置ignore_space 这个sql_mode 就可以把空格给忽略变成count()

NO_AUTO_CREATE_USER :禁止grant语句自动创建用户,除非认证信息被指定,语句必须包含一个非空的密码使用identified by或使用认证插件identified with.

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

NO_BACKSLASH_ESCAPES :(禁用反斜线转义)这个模式启用,反斜线将会变成一个普通的字符串。

NO_DIR_IN_CREATE :在创建表时忽略所有的index directory和data directory选项。

NO_ENGINE_SUBSTITUTION :此模式指定当执行create语句或者alter语句指定的存储引擎没有启用或者没有编译时,控制默认默认存储引擎的自动切换。默认是启用的。当NO_ENGINE_SUBSTITUTION被禁用,当create表时的默认存储引擎不可使用则产生警告信息,对于alter语句产生警告并且表不会被alter。当NO_ENGINE_SUBSTITUTION启用,会生成错误并且表不会被创建或alter如果期望的存储引擎不可用。

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的。若是结果是一个负数,则会产生一个错误。

NO_ZERO_DATE :这个模式影响着插入的’0000-00-00’值是否作为合法的数值,这个结果和是否启用严格模式有关。如果这个模式禁用,则’0000-00-00’被允许并且插入没有警告,如果这个模式启用,则’0000-00-00’被运行并且插入产生一个警告信息;如果这个模式和严格模式被启用,则’0000-00-00’不被允许并且插入产生错误,除非ignore被使用。

NO_ZERO_DATE 不是严格模式的一部分,应该和严格模式一起被使用。因为NO_ZERO_DATE将会被放弃在将来的mysql中,它的影响将会被包含进严格模式中。 NO_ZERO_IN_DATE:这个模式影响着日期中的月份和天数是否可以为0(注意年份是非0的),这个模式也取决于严格模式是否被启用。如果这个模式未启用,date中的零部分被允许并且插入没有警告。如果这个模式启用,dates中的零部分插入被作为“0000-00-00”并且产生一个警告。如果这个模式和严格模式被启用,则dates中的零部分不被允许并且插入产生错误,除非ignore也被使用。这个模式也不是严格模式的一部分,应该和严格模式一起被使用。

ONLY_FULL_GROUP_BY :这个模式对查询的影响有点大。mysql5.7默认启用这个模式,这个模式是指在mysql的select查询不能出现除group by语句字段之外的其余字段。

PAD_CHAR_TO_FULL_LENGTH :虽然char 和 varchar 的存储方式不太相同,但是对两个字符串的比较,都只比较其值,忽略CHAR值存在的右填充,即使将SQL_MODE设置为PAD_CHAR_TO_FULL_LENGTH 也一样,但这不适用于like

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

REAL_AS_FLOAT :将real作为float的同义词。默认的,mysql将real作为double的同义词

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。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 什么是严格模式
  • sql_mode的常见设置
  • 不同版本的Mysql的sql_mode默认值
  • 各参数值的说明
相关产品与服务
云服务器
云服务器(Cloud Virtual Machine,CVM)提供安全可靠的弹性计算服务。 您可以实时扩展或缩减计算资源,适应变化的业务需求,并只需按实际使用的资源计费。使用 CVM 可以极大降低您的软硬件采购成本,简化 IT 运维工作。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档