15.7.1 正则表达式的使用
正则表达式（regular expression）是指一个用来描述或者匹配一系列符合某个句法规则的字符串的单个字符串。在很多文本编辑器或其他工具里，正则表达式通常被用来检索和/或替换那些符合某个模式的文本内容。许多程序设计语言都支持利用正则表达式进行字符串操作。例如，在Perl中就内建了一个功能强大的正则表达式引擎。正则表达式这个概念最初是由UNIX中的工具软件（例如SED和GREP）普及开的，通常缩写成“RegEx”或者“RegExp”。
MySQL利用RegExp命令提供给用户扩展的正则表达式功能，RegExp实现的功能类似UNIX上GREP和SED的功能，并且RegExp在进行模式匹配时是区分大小写的。熟悉并掌握RegExp的功能可以使模式匹配工作事半功倍。
MySQL 5.7中可以使用的模式序列如表15-3所示。
表15-3 正则表达式中的模式
下面举一些例子来介绍常用正则表达式的使用方法。
- “^”在字符串的开始处进行匹配，返回结果为1表示匹配，返回结果为0表示不匹配。下例中尝试匹配字符串“abcdefg”是否以字符“a”开始。
-
mysql>select'abcdefg' REGEXP '^a'; +-----------------------+-
|'abcdefg' REGEXP '^a'| +-----------------------+-
|1| +-----------------------+-
1 row inset(0.39 sec)
- “$”在字符串的末尾处进行匹配。下例中尝试匹配字符串“abcdefg”是否以字符“g”结束。
-
mysql>select'abcdefg' REGEXP 'g$'; +-----------------------+-
|'abcdefg' REGEXP 'g$'| +-----------------------+-
|1| +-----------------------+-
1 row inset(0.01 sec)
- “.”匹配任意单个字符，包括换行符。下例中字符串“abcdefg”尝试匹配单字符“h”和“f”。
-
mysql>select'abcdefg' REGEXP '.h','abcdefg' REGEXP '.f'; +-----------------------+-----------------------+-
|'abcdefg' REGEXP '.h'|'abcdefg' REGEXP '.f'| +-----------------------+-----------------------+-
|0|1| +-----------------------+-----------------------+-
1 row inset(0.00 sec)
- “[…]”匹配出括号内的任意字符。下例中字符串“abcdefg”尝试匹配“fhk”中的任意一个字符，如果有一个字符能匹配上，则返回1。
-
mysql>select'abcdefg' REGEXP "[fhk]"; +--------------------------+-
|'abcdefg' REGEXP "[fhk]"| +--------------------------+-
|1| +--------------------------+-
1 row inset(0.01 sec)
- “[^…]”匹配不出括号内的任意字符，和“[…]”刚好相反。下例中字符串“efg”和“X”中如果有任何一个字符匹配不上“[XYZ]”中的任意一个字符，则返回0；如果全部都能匹配上，则返回1。
-
mysql>select'efg' REGEXP "[^XYZ]",'X' REGEXP "[^XYZ]"; +-----------------------+---------------------+-
|'efg' REGEXP "[^XYZ]"|'X' REGEXP "[^XYZ]"| +-----------------------+---------------------+-
|1|0| +-----------------------+---------------------+-
1 row inset(0.00 sec)
上文介绍了正则表达式的常见使用方法。但是，在实际工作中，正则表达式到底会在什么地方用到呢？下面给出一个实例，使用正则表达式查询出使用http://163.com邮箱的用户和邮箱。
（1）创建测试数据：
mysql> insert into customer (store_id, first_name, last_name, address_id, email) values-
(1,'188mail','beijing',605,'beijing@188.com'); -
Query OK,1 row affected,1 warning (0.06 sec) mysql> insert into customer (store_id, first_name, last_name, address_id, email) values-
(1,'126mail','beijing',605,'beijing@126.com'); -
Query OK,1 row affected,1 warning (0.05 sec) mysql> insert into customer (store_id, first_name, last_name, address_id, email) values-
(1,'163mail','beijing',605,'beijing@163.com'); -
Query OK,1 row affected,1 warning (0.03 sec)
（2）使用正则表达式“$”和“[…]”进行匹配：
-
mysql>select first_name, email from customer where email regexp "@163[,.]com$"; +------------+-----------------+| first_name | email |+------------+-----------------+-
|163mail| beijing@163.com | +------------+-----------------+-
1 row inset(0.00 sec)
从以上可以看出，如果不使用正则表达式而使用普通的LIKE语句，则WHERE条件需要写成如下格式：
-
email like "@163%.com"or email like "@163%,com"
显然，采用正则表达式可以使得代码更加简单易读。
小编把这本书的来源放在这里吧！！！
本文摘自：深入浅出MySQL 数据库开发 优化与管理维护 第3版，翟振兴，张恒岩，崔春华，黄荣，董骐铭 著
《深入浅出MySQL 数据库开发 优化与管理维护 第3版》(翟振兴，张恒岩，崔春华，黄荣，董骐铭)【摘要 书评 试读】- 京东图书item.jd.com
- MySQL技术内幕从入门到精通书籍
- 高性能MySQL数据库必知必会教程，畅销图书全新升级版本
- 涵盖MySQL8.0的重要功能，附带大量一线工程案例
15.7.2 巧用RAND()提取随机行
大多数数据库都会提供产生随机数的包或者函数，通过这些包或者函数可以产生用户需要的随机数，也可以用来从数据表中抽取随机产生的记录，这对一些抽样分析统计是非常有用的。例如ORACLE中用DBMS_RANDOM包产生随机数，而在MySQL中，产生随机数的方法是RAND()函数。可以利用这个函数与ORDER BY子句一起完成随机抽取某些行的功能。它的原理其实就是ORDER BY RAND()能够把数据随机排序。
例如，可按照随机顺序检索数据行：
-
mysql>select*from category order by rand(); +-------------+-------------+---------------------+| category_id | name | last_update |+-------------+-------------+---------------------+-
|12|Music|2006-02-1504:46:27| -
|9|Foreign|2006-02-1504:46:27| -
|13|New|2006-02-1504:46:27| -
|7|Drama|2006-02-1504:46:27| -
|1|Action|2006-02-1504:46:27| -
|3|Children|2006-02-1504:46:27| -
…
这样，如果想随机抽取一部分样本的时候，把数据随机排序后再抽取前n条记录就可以了，比如：
-
mysql>select*from category order by rand() limit 5; +-------------+-----------+---------------------+| category_id | name | last_update |+-------------+-----------+---------------------+-
|4|Classics|2006-02-1504:46:27| -
|9|Foreign|2006-02-1504:46:27| -
|2|Animation|2006-02-1504:46:27| -
|16|Travel|2006-02-1504:46:27| -
|7|Drama|2006-02-1504:46:27| +-------------+-----------+---------------------+-
5 rows inset(0.00 sec)
上面的例子从类别表category中随机抽取了5个样本，随机抽取样本对总体的统计具有十分重要的意义，因此这个函数非常有用。
15.7.3 利用GROUP BY的WITH ROLLUP子句
在SQL语句中，使用GROUP BY的WITH ROLLUP字句可以检索出更多的分组聚合信息，它不仅仅能像一般的GROUP BY语句那样检索出各组的聚合信息，还能检索出本组类的整体聚合信息，具体如下例所示。
在支付表payment中，按照支付时间payment_date的年月、经手员工编号staff_id列分组对支付金额amount列进行聚合计算如下：
-
mysql>select date_format(payment_date,'%Y-%m'), staff_id, sum(amount)from payment groupby date_format(payment_date,'%Y-%m'), staff_id; +------------------------------------+----------+-------------+-
| date_format(payment_date,'%Y-%m')| staff_id | sum(amount)| +------------------------------------+----------+-------------+-
|2005-05|1|2621.83| -
|2005-05|2|2202.60| -
|2005-06|1|4776.36| -
|2005-06|2|4855.52| -
|2005-07|1|14003.54| -
|2005-07|2|14370.35| -
|2005-08|1|11853.65| -
|2005-08|2|12218.48| -
|2006-02|1|234.09| -
|2006-02|2|280.09| +------------------------------------+----------+-------------+-
10 rows inset(0.06 sec) -
mysql>select date_format(payment_date,'%Y-%m'), IFNULL(staff_id,''), sum(amount)from payment -
groupby date_format(payment_date,'%Y-%m'), staff_id with rollup; +------------------------------------+---------------------+-------------+-
| date_format(payment_date,'%Y-%m')| IFNULL(staff_id,'')| sum(amount)| +------------------------------------+---------------------+-------------+-
|2005-05|1|2621.83| -
|2005-05|2|2202.60| -
|2005-05||4824.43| -
|2005-06|1|4776.36| -
|2005-06|2|4855.52| -
|2005-06||9631.88| -
|2005-07|1|14003.54| -
|2005-07|2|14370.35| -
|2005-07||28373.89| -
|2005-08|1|11853.65| -
|2005-08|2|12218.48| -
|2005-08||24072.13| -
|2006-02|1|234.09| -
|2006-02|2|280.09| -
|2006-02||514.18| -
| NULL||67416.51| +------------------------------------+---------------------+-------------+-
16 rows inset(0.05 sec)
从上面的例子中可以看到，第二个SQL语句的结果比第一个SQL语句的结果多出了很多行，而这些行反映出了更多的信息。例如，第二个SQL语句的结果的前两行表示2005-05月份各个员工（1、2）的经手的支付金额，而第三行表示2005-05月份总支付金额为4 824.43，这个信息在第一个SQL语句中是不能反映出来的，第16行表示总支付金额为67 416.51，这个信息在第一个SQL语句中是没有的。
其实WITH ROLLUP反映的是一种OLAP思想，也就是说这一个GROUP BY语句执行完成后可以满足用户想要得到的任何一个分组以及分组组合的聚合信息值。
注意：当使用ROLLUP时，不能同时使用ORDER BY子句进行结果排序。换言之，ROLLUP和ORDER BY是互相排斥的。此外，LIMIT用在ROLLUP后面。
15.7.4 用BIT GROUP FUNCTIONS做统计
本节主要介绍如何共同使用GROUP BY语句和BIT_AND、BIT_OR函数完成统计工作。这两个函数的一般用途就是做数值之间的逻辑位运算，但是，当把它们与GROUP BY子句联合使用的时候就可以做一些其他的任务。
假设现在有这样一个任务：一个超市需要记录每个用户每次来超市都购买了哪些商品。为了将问题简单化，假设该超市只有面包、牛奶、饼干、啤酒4种商品。那么通常该怎么做呢？一般先建立一个购物单表，里面记录购物发生的时间、顾客信息等；然后再建立一个购物单明细表，里面记录该顾客所购买的商品。这样设计表结构的优点是顾客所购买的商品的详细信息可以记录下来，比如数量、单价等，但是如果目前的这个任务只需要知道用户购买商品的种类和每次购物总价等信息，那么这种数据库结构的设计就显得太复杂了。一般还可能会想到用一个表实现这个功能，并且用一个字段以字符串的形式记录顾客所购买的所有商品的商品号，这也是一种方法，但是如果顾客一次购买商品比较多，需要很大的存储空间，而且将来做各种统计的时候也会捉襟见肘。
下面给出一种新的解决办法，类似于上面讲到的第二种方案，仍然用一个字段表示顾客购买商品的信息，但是这个字段是数值型的而不是字符型的，该字段存储一个十进制数字，当它转换成二进制的时候，那么每一位代表一种商品，而且如果所在位是“1”那么表示顾客购买了该种商品，“0”表示没有购买该种商品。比如数值的第一位代表面包（规定从右向左开始计算）、第二位代表牛奶、第三位代表饼干、第4位代表啤酒，这样如果一个用户购物单的商品列的数值为5，那么二进制表示为0101，这样从右向左第一位和第三位是1，那么就可以知道这个用户购买了面包和饼干，而如果这个客户有多个这样的购物单（在数据库中就是有多条记录），把这些购物单按用户分组做BIT_OR()操作就可以知道这个用户都购买过什么商品。
下面举例说明一下这个操作，首先初始化一组数据：
mysql> create table order_rab (id int,customer_id int,kind int);-
Query OK,0 rows affected (0.05 sec) mysql> insert into order_rab values (1,1,5),(2,1,4);-
Query OK,2 rows affected (0.00 sec) mysql> insert into order_rab values (3,2,3),(4,2,4);-
Query OK,2 rows affected (0.00 sec) -
mysql>select*from order_rab; +------+-------------+------+| id | customer_id | kind |+------+-------------+------+-
|1|1|5| -
|2|1|4| -
|3|2|3| -
|4|2|4| +------+-------------+------+-
4 rows inset(0.00 sec)
其中customerid是顾客编号，kind是所购买的商品，初始化了两个顾客1和2的数据，他们每人购物两次，前者购买的商品数值是5和4，转化为二进制分别为0101、0100，表示这个顾客第一次购买了牛奶和啤酒，第二次购买了牛奶；后者购买的商品数值是3和4，转化为二进制分别为0011、0100，表示这个顾客第一次购买了饼干和啤酒，第二次购买了牛奶。
下面用BIT_OR()函数与GROUP BY子句联合起来，统计一下这两个顾客在这个超市一共都购买过什么商品，如下例：
-
mysql>select customer_id,bit_or(kind)from order_rab groupby customer_id; +-------------+--------------+-
| customer_id | bit_or(kind)| +-------------+--------------+-
|1|5| -
|2|7| +-------------+--------------+-
2 rows inset(0.00 sec)
可以看到顾客1的BIT_OR()结果是5，即0101，表示这个顾客在本超市购买过牛奶和啤酒；顾客2的BIT_OR()结果是7，即0111，表示这个顾客在本超市购买过牛奶、饼干、啤酒。
下面解释一下数据库在处理这个逻辑时的计算过程，以第一个顾客举例，BIT_OR(kind)就相当于把kind的各个值做了一个“或”操作，最终结果是十进制的5。逻辑计算公式如下：
# ..0101# ..0100# OR ..0000# ---------# ..0101
同理，可以用BIT_AND()统计每个顾客每次来本超市都会购买的商品，具体如下：
-
mysql>select customer_id,bit_and(kind)from order_rab groupby customer_id; +-------------+---------------+-
| customer_id | bit_and(kind)| +-------------+---------------+-
|1|4| -
|2|0| +-------------+---------------+-
2 rows inset(0.01 sec)
顾客1的BIT_AND()结果是4，即0100，表示顾客1每次来本超市都会购买牛奶；顾客2的BIT_AND()结果是0，即0000，表示顾客2不是每次来本超市都会购买的商品。
数据库在处理BIT_AND()的时候就是把kind的各个值做了一个“与”操作，拿顾客1举例说明一下，逻辑计算公式如下：
# ..0101# ..0100# AND ..1111# -----------
# ..0100
从上面的例子可以看出，这种数据库结构设计的好处就是能用很简洁的数据表示很丰富的信息，这种方法能够大大地节省存储空间，而且能够提高部分统计计算的速度。不过需要注意的是，这种设计其实损失了顾客购买商品的详细信息，比如购买商品的数量、当时单价、是否有折扣、是否有促销等，因此还要根据应用的实际情况有选择地考虑数据库的结构设计。
15.7.5 数据库名、表名大小写问题
在MySQL中，数据库对应操作系统下的数据目录。数据库中的每个表至少对应数据库目录中的一个文件（也可能是多个，这取决于存储引擎）。因此，所使用操作系统的大小写敏感性决定了数据库名和表名的大小写敏感性。在大多数UNIX环境中，由于操作系统对大小写的敏感性导致了数据库名和表名对大小写敏感性，而在Windows中，由于操作系统本身对大小写不敏感，因此在Windows下的MySQL数据库名和表名对大小写也不敏感。
列、索引、存储子程序和触发器名在任何平台上对大小写不敏感。默认情况下，表别名在UNIX中对大小写敏感，但在Windows或macOS X中对大小写不敏感。下面的查询在UNIX中会报错，因为它同时引用了别名a和A：
-
mysql>select id from order_rab a where A.id =1; -
ERROR 1054(42S22):Unknown column 'A.id'in'where clause'
然而，该查询在Windows中是可以的。要想避免出现差别，最好采用一致的转换，例如，总是用小写创建并引用数据库名和表名。
在MySQL中，如何在硬盘上保存、使用表名和数据库名是由lower_case_tables_name系统变量决定的，用户可以在启动MySQL服务时设置这个系统变量。lower_case_tables_name可以采用如表15-4所示的任一值。
表15-4 lower_case_tables_name的取值范围
如果只在一个平台上使用MySQL，通常不需要更改lower_case_tables_name变量。然而，如果用户想要在对大小写敏感性不同的文件系统的平台之间转移表，就会遇到困难。例如，在UNIX中，my_tables和MY_tables是两个不同的表，但在Windows中，这两个表名相同。
在UNIX中使用lowercase_tables_name=0，而在Windows中使用lower_case_tables
注意：在UNIX中将lower_case_tables_name设置为1并且重启mysqld之前，必须先将旧的数据库名和表名转换为小写。尽管在某些平台中数据库名和表名对大小写不敏感，但是最好养成在同一查询中使用相同的大小写来引用给定的数据库名或表名的习惯。
15.7.6 使用外键需要注意的问题
在MySQL中，InnoDB存储引擎支持对外部关键字约束条件的检查。而对于其他类型存储引擎的表，当使用REFERENCES tbl_name(col_name)子句定义列时可以使用外部关键字，但是该子句没有实际的效果，只作为备忘录或注释来提醒用户目前正定义的列指向另一个表中的一个列。
例如，下面的myisam表外键就没有起作用：
mysql> create table users(id int,name varchar(10),primary key(id)) engine=myisam;-
Query OK,0 rows affected (0.03 sec) -
mysql> create table books(id int,bookname varchar(10),userid int,primary key(id),constraint fk_userid_id foreign key(userid) references users(id)) engine=myisam; -
Query OK,0 rows affected (0.03 sec) mysql> insert into books values(1,'book1',1);-
Query OK,1 row affected (0.00 sec)
如果用InnoDB存储引擎建表的话，外键就会起作用，具体如下：
mysql> create table users2(id int,name varchar(10),primary key(id)) engine=innodb;-
Query OK,0 rows affected (0.14 sec) -
mysql> create table books2(id int,bookname varchar(10),userid int,primary key(id),constraint fk_userid_id foreign key(userid) references users2(id)) engine=innodb; -
Query OK,0 rows affected (0.18 sec) mysql> insert into books2 values(1,'book1',1);-
ERROR 1452(23000):Cannot add or update a child row: a foreign key constraint fails ('sakila/books2', CONSTRAINT 'fk_userid_id' FOREIGN KEY ('userid') REFERENCES 'users2'('id'))
而且，用show create table命令查看建表语句的时候，发现MyISAM存储引擎并不显示外键的语句，而InnoDB存储引擎就会显示外键语句，具体如下：
mysql> show create table booksG;-
*********************************1. row ********************************* Table: books-
CreateTable: CREATE TABLE 'books'( -
'id'int(11) NOT NULL DEFAULT '0', 'bookname' varchar(10) DEFAULT NULL,-
'userid'int(11) DEFAULT NULL, PRIMARY KEY ('id'),-
KEY 'fk_userid_id'('userid') ) ENGINE=MyISAM DEFAULT CHARSET=gbk-
1 row inset(0.00 sec) mysql> show create table books2G;-
*********************************1. row ********************************* Table: books2-
CreateTable: CREATE TABLE 'books2'( -
'id'int(11) NOT NULL DEFAULT '0', 'bookname' varchar(10) DEFAULT NULL,-
'userid'int(11) DEFAULT NULL, PRIMARY KEY ('id'),-
KEY 'fk_userid_id'('userid'), -
CONSTRAINT 'fk_userid_id' FOREIGN KEY ('userid') REFERENCES 'users2'('id') ) ENGINE=InnoDB DEFAULT CHARSET=gbk-
1 row inset(0.00 sec)
15.8 小结
SQL优化问题是数据库性能优化最基础也是最重要的一个问题，实践表明很多数据库性能问题都是由不合适的SQL语句造成。本章通过实例描述了SQL优化的一般过程，从定位一个有性能问题的SQL语句到分析产生性能问题的原因，最后到采取什么措施优化SQL语句的性能。另外，针对特定的SQL（比如排序、join等）和MySQL 8.0引入的一些新功能（比如直方图）也做了一些介绍，希望能帮助读者拓宽优化的思路。
