MySQL中，SQL mode 常用来解决下面几类问题
通过设置 SQL Mode,可以完成不同严格程度的数据校验,有效地保障数据准确性。
通过设置 SQL Mode 为 ANSI 模式,来保证大多数 SQL 符合标准的 SQL 语法,这样应用在
不同数据库之间进行迁移时,则不需要对业务 SQL 进行较大的修改。
在不同数据库之间进行数据迁移之前,通过设置 SQL Mode 可以使 MySQL 上的数据更方
便地迁移到目标数据库中。
1 设置模式为严格模式
sql_mode 的 一 种 修 改 方 法 , 即 SET [SESSION|GLOBAL]sql_mode='modes',
其中 SESSION 选项表示只在本次连接中生效;而 GLOBAL 选项表示在本次不生效，而对于新的链接生效
SQL mode 的常见功能
(1)效验日期数据合法性
ANSI模式：宽松模式
在 ANSI 模式下,非法日期可以插入,但是插入值却变为“0000-00-00 00:00:00”,
并且系统给出了 warning;而在 TRADITIONAL 模式下,在直接提示日期非法,拒绝插入
2 (2)在 INSERT 或 UPDATE 过程中,如果 SQL MODE 处于 TRADITIONAL 模式,
运行 MOD(X,0)会产生错误,因为 TRADITIONAL 也属于严格模式,在非严格模式下 MOD(X,0)返回的结
果是 NULL,所以在含有 MOD 的运算中要根据实际情况设定好 sql_mode。
3 (3)启用 NO_BACKSLASH_ESCAPES 模式,使反斜线成为普通字符。在导入数据时,如
果数据中含有反斜线字符,启用 NO_BACKSLASH_ESCAPES 模式保证数据的正确性,是个不错
的选择
即将 NO_BACKSLASH_ESCAPES 添加，而不是只选这个
(4)启用 PIPES_AS_CONCAT 模式。将“||”视为字符串连接操作符,在 Oracle 等数据
库中,“||”被视为字符串的连接操作符,所以,在其他数据库中含有“||”操作符的 SQL
在 MySQL 中将无法执行,为了解决这个问题,MySQL 提供了 PIPES_AS_CONCAT 模式。
常用的SQL Mode
SQL mode 在迁移中的使用
与其他异构数据库之间有迁移需求，那么SQL mode 可以有帮助
例如，在数据迁移过程中,可以设置 SQL Mode 为 NO_TABLE_OPTIONS 模式,这样将去掉 show
create table 中的“engine”关键字,获得通用的建表脚本
小结
SQL Mode 的“严格模式”为 MySQL 提供了很好的数据校验功能,保证了数据的准确性,
TRADITIONAL、STRICT_TRANS_TABLES 是常用的两种严格模式。
SQL Mode 的多种模式可以灵活组合,组合后的模式可以更好地满足应用程序的需求。
尤其在数据迁移中,SQL Mode 的使用更为重要
常 用 S Q L 技 巧 和 常 见 问 题
正则表达式的使用
MySQL 利用 REGEXP 命令提供给用户扩展的正则表达式功能
1
2
3
4
5
使用RAND（）提取随机行
在MySQL中,产生随机数的方法是RAND()
函数。可以利用这个函数与ORDER BY子句一起完成随机抽取某些行的功能。它的原理其实
就是ORDER BY RAND()能够把数据随机排序
例如，随机提取 sales2 中的5行数据
mysql> select * from sales2 order by rand() limit 5;
利用 GROUP BY 的 WITH ROLLUP 子句做统计
在SQL语句中,使用GROUP BY的WITH ROLLUP字句可以检索出更多的分组聚合信息,它不仅
仅能像一般的GROUP BY语句那样检索出各组的聚合信息,还能检索出本组类的整体聚合信
息
其实 WITH ROLLUP 反映的是一种 OLAP 思想,也就是说这一个 GROUP BY 语句执行完成后可
以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。
注意: 1、当使用 ROLLUP 时, 不能同时使用 ORDER BY 子句进行结果排序。换言之, ROLLUP
和 ORDER BY 是互相排斥的
2、LIMIT 用在 ROLLUP 后面。
用 BIT GROUP FUNCTIONS 做统计
主要介绍如何共同使用 GROUP BY 语句和 BIT_AND、BIT_OR 函数完成统计工作。
这两个函数的一般用途就是做数值之间的逻辑位运算,但是,当把它们与 GROUP BY 子句联
合使用的时候就可以做一些其他的任务。
一个超市需要记录每个用户每次来超市都购买了哪些商品
下面给出一种新的解决办法,类似于上面讲到的第二种方案,仍然用一个字段表示顾客购买
商品的信息,但是这个字段是数值型的而不是字符型的,该字段存储一个十进制数字,当它
转换成二进制的时候,那么每一位代表一种商品,而且如果所在位是“1”那么表示顾客购
买了该种商品,
“0”表示没有购买该种商品。比如数值的第 1 位代表面包(规定从右向左开
始计算)、第 2 位代表牛奶、第 3 位代表饼干、第 4 位代表啤酒,这样如果一个用户购物单
的商品列的数值为 5,那么二进制表示为 0101,这样从右向左第 1 位和第 3 位是 1,那么就
可以知道这个用户购买了面包和饼干,而如果这个客户有多个这样的购物单(在数据库中就
是有多条记录),把这些购物单按用户分组做 BIT_OR()操作就可以知道这个用户都购买过什
么商品。
其中 customerid 是顾客编号,kind 是所购买的商品,初始化了两个顾客 1 和 2 的数据,他
们每人购物两次,前者购买的商品数值是 5 和 4,转化为二进制分别为 0101、0100,表示
这个顾客第一次购买了牛奶和啤酒,第二次购买了牛奶;后者购买的商品数值是 3 和 4,转
化为二进制分别为 0011、0100,表示这个顾客第一次购买了饼干和啤酒,第二次购买了牛
奶。
下面用 BIT_OR()函数与 GROUP BY 子句联合起来,统计一下这两个顾客在这个超市一共都购
买过什么商品,如下例:
数据库名、表名大小写问题
在 MySQL 中如何在硬盘上保存、使用表名和数据库名由 lower_case_tables_name 系统变量
决定,可以在启动 mysqld 时设置这个系统变量。 lower_case_tables_name 可以采用如表 17-2
所示的任一值
尽管在某些平台中数据库名和表名对大小写不敏感,但是最好养成在同一查询中使用
相同的大小写来引用给定的数据库名或表名的习惯。
使用外建需要注意的问题
在 MySQL 中,InnoDB 存储引擎支持对外部关键字约束条件的检查。而对于其他类型存储引
擎的表,当使用 REFERENCES tbl_name(col_name)子句定义列时可以使用外部关键字,但是
该子句没有实际的效果,只作为备忘录或注释来提醒用户目前正定义的列指向另一个表中的
一个列。
用 show create table 命令查看建表语句的时候,发现 MyISAM 存储引擎的并不显示外
键的语句,而 InnoDB 存储引擎的就显示外键语句
小结
应用正则表达式可以使用户能够在一段很大的字符串中找到符合自己规则的一些
语句,这个功能在进行匹配查找的时候非常有用。
RAND()函数与 ORDER BY 字句的配合使用能够实现随机抽取样本的功能,这个技巧
在进行数据统计的时候很方便。
GROUP BY 的 WITH ROLLUP 字句能够实现类似于 OLAP 的查询。
BIT 函数与 GROUP BY 子句的联合使用在某些应用场合可以大大降低存储量,提高
统计查询效率。
MySQL 数据库名和表名的大小写与操作系统对大小写的敏感性关系密切,因此需
要格外引起用户的注意。
MySQL 的外键功能仅对 InnoDB 存储引擎的表有作用,其他类型存储引擎的表虽然
可以建立外键,但是并不能起到外键的作用。
