Mysql之SQL Mode用法详解
一、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的值设置成后面的两个值（也就是我们说的严格模式），那么当在列中插入或更新不正确的值时，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全文搜索match against的用法
对于大的数据库,将数据装载到一个没有 FULLTEXT 索引的表中,然后再使用 ALTER TABLE (或 CREATE INDEX) 创建索引,这将是非常快的.将数据装载到一个已经有 FULLTEXT 索引的表中,将是非常慢的.1.使用Mysql全文检索fulltext的先决条件 表的类型必须是MyISAM建立全文检索的字段类型必须是char,varchar,text 2.建立全文检索先期配置由于Mysql的默认配置是索引的词的长度是4,所以要支持中文单字的话,首先更改这个.*Un
-
mysql中event的用法详解
一.基本概念mysql5.1版本开始引进event概念.event既"时间触发器",与triggers的事件触发不同,event类似与linux crontab计划任务,用于时间触发.通过单独或调用存储过程使用,在某一特定的时间点,触发相关的SQL语句或存储过程. 二.适用范围对于每隔一段时间就有固定需求的操作,如创建表,删除数据等操作,可以使用event来处理. 例如:使用event在每月的1日凌晨1点自动创建下个月需要使用的三张表. 三.使用权限单独使用event调用SQL语句时,
-
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 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数据库INSERT、UPDATE、DELETE以及REPLACE语句的用法详解
MySQL数据库insert和update语句引:用于操作数据库的SQL一般分为两种,一种是查询语句,也就是我们所说的SELECT语句,另外一种就是更新语句,也叫做数据操作语句.言外之意,就是对数据进行修改.在标准的SQL中有3个语句,它们是INSERT.UPDATE以及DELETE. 用于操作数据库的SQL一般分为两种,一种是查询语句,也就是我们所说的SELECT语句,另外一种就是更新语句,也叫做数据操作语句.言外之意,就 是对数据进行修改.在标准的SQL中有3个语句,它们是INSERT.UP
-
mysql sql_mode="" 的作用说明
sql_mode="",即强制不设定MySql模式(如不作输入检测.错误提示.语法模式检查等)应该能提高性能,但有如下问题: 如果插入了不合适数据(错误类型或超常),mysql会将数据设为"最好的可能数据"而不报错,如: /数字 设为:0/可能最小值/可能最大值 /字符串 设为:空串/能够存储的最大容量字符串 /表达式 设为:返回一个可用值(1/0-null) 所以,解决办法是:所有列都要采用默认值,这对性能也好. mysql_mode的详细描述: 在mysql 5
-
mysql中explain用法详解
如果在select语句前放上关键词explain,mysql将解释它如何处理select,提供有关表如何联接和联接的次序. explain的每个输出行提供一个表的相关信息,并且每个行包括下面的列: 1,id select识别符.这是select的查询序列号.2,select_type 可以为一下任何一种类型simple 简单select(不使用union或子查询)primary 最外面的selectunion union中的第二个或后面的select语句dependent uni
-
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 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之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