Mysql之SQL Mode用法详解

2024-04-10 03:52:34

一、Mysql SQL Mode简介

通常来说MySQL服务器能够工作在不同的SQL模式下，并能针对不同的客户端以不同的方式应用这些模式。这样，应用程序就能对服务器操作进行量身定制以满足自己的需求。这类模式定义了MySQL应支持的SQL语法，以及应该在数据上执行何种确认检查。这样，就能在众多不同的环境下、与其他数据库服务器一起更容易地使用MySQL。可以使用“--sql-mode="modes"”选项，通过启动mysqld来设置默认的SQL模式。而从MySQL 4.1开始，也能在启动之后，使用SET [SESSION|GLOBAL] sql_mode='modes'语句，通过设置sql_mode变量更改其模式。

通常在linux下安装完mysql后，其默认的sql-mode值是空，在这种情形下mysql执行的是一种不严格的检查，例如日期字段可以插入'0000-00-00 00:00:00'这样的值，还有如果要插入的字段长度超过列定义的长度，那么mysql不会终止操作，而是会自动截断后面的字符继续插入操作，如下例：

mysql> create table t5 (c1 char(3));
mysql> insert into t5 values('abcd');
mysql> select * from t5;
+------+
| c1 |
+------+
| abc |
+------+
1 row in set (0.00 sec)

我们发现插入的字符被自动截断了，但是如果我们本意希望如果长度超过限制就报错，那么我们可以设置sql_mode为STRICT_TRANS_TABLES，如下：

mysql> set session sql_mode='STRICT_TRANS_TABLES'

这样我们再执行同样的操作，mysql就会告诉我们插入的值太长，操作被终止，如下：

mysql> insert into t5 values('abcd');
ERROR 1406 (22001): Data too long for column 'c1' at row 1

经常使用的sql_mode值：

Sql_mode值	描述
ANSI	更改语法和行为，使其更符合标准SQL。
STRICT_TRANS_TABLES	如果不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句。本节后面给出了更详细的描述。
TRADITIONAL	Make MySQL的行为象“传统”SQL数据库系统。该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。注释：一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎，这种方式不是你想要的，因为出现错误前进行的数据更改不会“滚动”，结果是更新“只进行了一部分”。

Sql_mode值描述

ANSI 更改语法和行为，使其更符合标准SQL。

STRICT_TRANS_TABLES 如果不能将给定的值插入到事务表中，则放弃该语句。对于非事务表，如果值出现在单行语句或多行语句的第1行，则放弃该语句。本节后面给出了更详细的描述。

TRADITIONAL Make MySQL的行为象“传统”SQL数据库系统。该模式的简单描述是当在列中插入不正确的值时“给出错误而不是警告”。注释：一旦发现错误立即放弃INSERT/UPDATE。如果你使用非事务存储引擎，这种方式不是你想要的，因为出现错误前进行的数据更改不会“滚动”，结果是更新“只进行了一部分”。

说明：如果把sql_mode的值设置成后面的两个值（也就是我们说的严格模式），那么当在列中插入或更新不正确的值时，mysql将会给出错误，并且放弃insert/update操作。在我们的一般应用中建议使用这两种模式，而不是使用默认的空或ANSI模式。但是需要注意的问题是，如果数据库运行在严格模式下，并且你的存储引擎不支持事务，那么有数据不一致的风险存在，比如一组sql中有两个dml语句，如果后面的一个出现了问题，但是前面的已经操作成功，那么mysql并不能回滚前面的操作。因此说设置sql_mode需要应用人员权衡各种得失，从而得到一个合适的选择。

Sql_mode的值还有很多，这里不再累述，可以参考相关的手册。

二、SQL Mode与可移植性

如果mysql与其它异构数据库之间有数据移植的需求的话，那么下面的sql_mode的组合设置可以达到相应的效果：

数据库	Sql_mode值
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
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

三、SQL Mode与数据效验

SQL Mode 还可以实现对数据效验和转移等功能如：

1.效验日期数据合法性.
2.在INSERT或UPDATE过程中，如果被零除(或MOD(X，0))，则产生错误
3.将‘"'视为识别符引号(‘`'引号字符)
4.禁用反斜线字符(‘\')做为字符串内的退出字符。启用NO_BACKSLASH_ESCAPES模式，反斜线则成为普通字符。
5.将||视为字符串连接操作符（＋）(同CONCAT())，而不视为OR。

mysql sql_mode="" 的作用说明

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

一.基本概念mysql5.1版本开始引进event概念.event既"时间触发器",与triggers的事件触发不同,event类似与linux crontab计划任务,用于时间触发.通过单独或调用存储过程使用,在某一特定的时间点,触发相关的SQL语句或存储过程. 二.适用范围对于每隔一段时间就有固定需求的操作,如创建表,删除数据等操作,可以使用event来处理. 例如:使用event在每月的1日凌晨1点自动创建下个月需要使用的三张表. 三.使用权限单独使用event调用SQL语句时,
MySQL replace函数替换字符串语句的用法

MySQL replace函数我们经常用到,下面就为您详细介绍MySQL replace函数的用法,希望对您学习MySQL replace函数方面能有所启迪. 最近在研究CMS,在数据转换的时候需要用到mysql的MySQL replace函数,这里简单介绍一下. 比如你要将表 tb1里面的 f1字段的abc替换为def UPDATE tb1 SET f1=REPLACE(f1, 'abc', 'def'); REPLACE(str,from_str,to_str) 在字符串 str 中所有出现
Mysql字符串截取函数SUBSTRING的用法说明

感觉上MySQL的字符串函数截取字符,比用程序截取(如PHP或JAVA)来得强大,所以在这里做一个记录,希望对大家有用. 函数: 1.从左开始截取字符串 left(str, length) 说明:left(被截取字段,截取长度) 例:select left(content,200) as abstract from my_content_t 2.从右开始截取字符串 right(str, length) 说明:right(被截取字段,截取长度) 例:select right(content,200
MySQL数据库INSERT、UPDATE、DELETE以及REPLACE语句的用法详解

MySQL数据库insert和update语句引:用于操作数据库的SQL一般分为两种,一种是查询语句,也就是我们所说的SELECT语句,另外一种就是更新语句,也叫做数据操作语句.言外之意,就是对数据进行修改.在标准的SQL中有3个语句,它们是INSERT.UPDATE以及DELETE. 用于操作数据库的SQL一般分为两种,一种是查询语句,也就是我们所说的SELECT语句,另外一种就是更新语句,也叫做数据操作语句.言外之意,就是对数据进行修改.在标准的SQL中有3个语句,它们是INSERT.UP
mysql中explain用法详解

如果在select语句前放上关键词explain,mysql将解释它如何处理select,提供有关表如何联接和联接的次序. explain的每个输出行提供一个表的相关信息,并且每个行包括下面的列: 1,id select识别符.这是select的查询序列号.2,select_type 可以为一下任何一种类型simple 简单select(不使用union或子查询)primary 最外面的selectunion union中的第二个或后面的select语句dependent uni
mySQL中replace的用法

mysql replace实例说明: UPDATE tb1 SET f1=REPLACE(f1, 'abc', 'def'); REPLACE(str,from_str,to_str) 在字符串 str 中所有出现的字符串 from_str 均被 to_str替换,然后返回这个字符串这个函数用来批量替换数据中的非法关键字是很有用的!如下例子: 例1:UPDATE BBSTopic SET tcontents = replace(replace(tcontents,'共产党','') ,'找死'
MySQL Order by 语句用法与优化详解

MySQL Order By keyword是用来给记录中的数据进行分类的.MySQL Order By Keyword根据关键词分类ORDER BY keyword是用来给记录中的数据进行分类的. 复制代码代码如下: SELECT column_name(s) FROM table_name ORDER BY column_name 例子 SQL创建代码: 复制代码代码如下: CREATE TABLE IF NOT EXISTS mysql_order_by_test ( uid int
Mysql全文搜索match against的用法

对于大的数据库,将数据装载到一个没有 FULLTEXT 索引的表中,然后再使用 ALTER TABLE (或 CREATE INDEX) 创建索引,这将是非常快的.将数据装载到一个已经有 FULLTEXT 索引的表中,将是非常慢的.1.使用Mysql全文检索fulltext的先决条件表的类型必须是MyISAM建立全文检索的字段类型必须是char,varchar,text 2.建立全文检索先期配置由于Mysql的默认配置是索引的词的长度是4,所以要支持中文单字的话,首先更改这个.*Un
Mysql之SQL Mode用法详解

一.Mysql SQL Mode简介通常来说MySQL服务器能够工作在不同的SQL模式下,并能针对不同的客户端以不同的方式应用这些模式.这样,应用程序就能对服务器操作进行量身定制以满足自己的需求.这类模式定义了MySQL应支持的SQL语法,以及应该在数据上执行何种确认检查.这样,就能在众多不同的环境下.与其他数据库服务器一起更容易地使用MySQL.可以使用"--sql-mode="modes""选项,通过启动mysqld来设置默认的SQL模式.而从MySQL 4.
MYSQL中Truncate的用法详解

本文导读:删除表中的数据的方法有delete,truncate, 其中TRUNCATE TABLE用于删除表中的所有行,而不记录单个行删除操作.TRUNCATE TABLE 与没有 WHERE 子句的 DELETE 语句类似:但是,TRUNCATE TABLE 速度更快,使用的系统资源和事务日志资源更少.下面介绍SQL中Truncate的用法当你不再需要该表时, 用 drop:当你仍要保留该表,但要删除所有记录时, 用 truncate:当你要删除部分记录时(always with a WHE
MySQL游标概念与用法详解

本文实例讲述了MySQL游标概念与用法.分享给大家供大家参考,具体如下: 1.游标的概念(Cursor) 一条sql,对应N条资源,取出资源的接口,就是游标,沿着游标,可以一次取出1行.如果开发过安卓的同学应该知道有一个Api是Cursor,也是读取SQLite数据库用的,和这个有点类似. 2.使用游标的步骤 (1)声明使用declare进行声明 declare 游标名 cursor for select_statement (2)打开游标使用open进行打开 open 游标名 (3)从游标
mysql存储过程原理与用法详解

本文实例讲述了mysql存储过程原理与用法.分享给大家供大家参考,具体如下: 本文内容: 什么是存储过程存储过程的创建存储过程的使用查看存储过程修改存储过程删除存储过程首发日期:2018-04-17 什么是存储过程: 存储过程存储了一系列sql语句存储过程的需求场景:下边是一个经典的需求场景,很多Mysql的书都有: 存储过程存储了一系列sql语句,使得简化了操作,不要求重复执行一系列操作.只需要在需要的时候调用一下存储过程就行了. 一般来说,可以认为存储过程的功能与函数的功能类似
MySQL查询条件常见用法详解

本文实例讲述了MySQL查询条件常见用法.分享给大家供大家参考,具体如下: 条件使用where子句对表中的数据筛选,结果为true的行会出现在结果集中语法如下: select * from 表名 where 条件; 例: select * from students where id=1; where后面支持多种运算符,进行条件的处理比较运算符逻辑运算符模糊查询范围查询空判断比较运算符等于: = 大于: > 大于等于: >= 小于: < 小于等于: <= 不等于:
mysql update语句的用法详解

首先,单表的UPDATE语句: UPDATE [LOW_PRIORITY] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition] [ORDER BY ...] [LIMIT row_count] 其次,多表的UPDATE语句: UPDATE [LOW_PRIORITY] [IGNORE] table_references SET col_name1=expr1 [, col_n
MySQL的mysqldump工具用法详解

导出要用到MySQL的mysqldump工具,基本用法是: 复制代码代码如下: shell> mysqldump [OPTIONS] database [tables] 如果你不给定任何表,整个数据库将被导出. 通过执行mysqldump --help,你能得到你mysqldump的版本支持的选项表. 注意,如果你运行mysqldump没有--quick或--opt选项,mysqldump将在导出结果前装载整个结果集到内存中,如果你正在导出一个大的数据库,这将可能是一个问题. my
浅谈SQLServer的ISNULL函数与Mysql的IFNULL函数用法详解

SQL Serve的ISNULL函数: ISNULL(check_expression,replacement_value) 1.check_expression与replacement_value的数据类型必须一致. 2.如果check_expression为NULL,则返回replacement_value. 3.如果check_expression为NULL,则返回check_expression. Mysql的IFNULL函数: IFNULL(expr1,expr2) 如果expr1不是
MySQL两种临时表的用法详解

外部临时表通过CREATE TEMPORARY TABLE 创建的临时表,这种临时表称为外部临时表.这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭.这种临时表的命名与非临时表可以同名(同名后非临时表将对当前会话不可见,直到临时表被删除). 内部临时表内部临时表是一种特殊轻量级的临时表,用来进行性能优化.这种临时表会被MySQL自动创建并用来存储某些操作的中间结果.这些操作可能包括在优化阶段或者执行阶段.这种内部表对用户来说是不可见的,但是通过EXPLAIN或者SHOW S

Mysql之SQL Mode用法详解

相关推荐

随机推荐