记录一次SQL函数和优化的问题

• 2022 年 3 月 22 日
• 筆記
• Mysql学习

一、前言

上次在年前快要放假的时候记录的一篇安装SSL证书的内容，因为当时公司开始居家办公了，我也打算回个家
毕竟自己在苏州这半年一个人也是很想家的，所以就打算年过完来重新写博客。不巧的是，当时我2月中旬刚到苏州，
没想到苏州疫情爆发了，直接隔离十四天，自己平时就完成公司的开发任务以及自己的毕设，把写博客的事有点淡忘了，
最近也算自己手头的一些事都开始可以顺利进行了，想起也是时候去记录一下了。
很多新学的东西自己感觉就是入了个门，所以平时基本就直接放语雀上了，这次也是打算做一个简短的总结吧！

二、SQL函数

注：以下基于官方文档理解（ MySQL5.7文档 ）

官方的内容，可以利用好CTRL+F，进行网页内搜索，随时查看各种函数用法，而且也会避免一些不规范的问题，比较官方的例子和要求最致密。

内置函数和运算符参考

2.1字符串函数

FIND_IN_SET(param1，param2)

这是一个字符串相关的函数

FIND_IN_SET(needle,haystack) 里面有两个参数，我们可以简单的看作find_in_set(param1,param2)

第一个参数param1：它是我们要查找的某一个具体的值

第二个参数param2：它是我们要查找的字符串列表

• 当param2在param2这个列表中的话，函数返回一个正整数
• 当param1不在param2中，或者param2这个列表是个NULL，函数返回0
• 当两个参数param1或param2为NUll的时候，函数放回NULL

这里以LEFT JOIN举个“栗”子：

LEFT JOIN：说的简单点就是，左表记录会全部返回，同时如果与右表有记录相等的数据会返回右表的一些相关信息，如果没有，右表返回的记录就是NULL
（这个可以去参考CSDN此篇博客：Sql之left join（左关联）、right join（右关联）、inner join（自关联）的区别

这里假设有a和b两张表，a表中的id是个bigint类型，b表中的relate_a_id是个varchar类型，存放的是关联的a表中的id
（这里仅仅是举个栗子，表的设计一定要符合规范，比如这种关联的可以新增一张关联表的操作）
select a.id,
       a.name,
       IFNULL(b.id,0) AS flag,
       b.relate_a_id
       from a left join b on FIND_IN_SET(a.id,relate_a_id) and a.id = b.id
       
这样如果a.id在这个b表中relate_a_id这个字段的列表中的话就返回b.id，如果不在就放回0。这里起了个别名为flag作为判断量

单表的操作更简单，总而言之，这个函数就是为了判断一个值是否在一个字符串列表中的操作。

同理和NULL、NOT NULL一样，如果要判断不在当中就直接NOT FIND_IN_SET()就可以了

这里要提一点的就是，以上操作看起来和IN这个操作符很像，所以这里我的理解是：

虽然
1 IN(1,2,3)  和  FIND_IN_SET(1,"1,2,3")  最终的结果是一样的，但是如下：
IN它是“值”对“值”，而FIND_IN_SET(param1，param2)是“值”对“一个列表”，而且FIND_IN_SET这个函数有自己的固定的两个参数
+ 不同点一：比较内容不同
+ 不同点二：函数格式不同

以上也是自己的一些浅见，如有错误，请各位大佬虚心赐教！

其他

字符串函数其实还有很多比较常见的，比如：

CONCAT(param1,param2,……)

这个函数里面也是有参数的，就是把两个或多个参数组合到一起的函数，当然还有CONCAT_WS(seperator,param1,param2,……)
根据第一个参数“分隔符”，来组合参数列表。

对于这个函数比较熟悉的就是写动态SQL的时候与LIKE操作符的应用，比如：
select a.name from a where a.is_delete =0
<if test="param.serachName!=''">
    and a.name like concat('%',#{param.serachName},'%')
</if>

这也是为了单纯写like去传参数的话，会出现SQL注入的风险，所以采用这种方式来防止SQL注入

REPLACE(str,oldStr,newStr)

这里要注意的是mysql扩展中REPLACE是个插入更新语句，但它没有where字句，具体可以自行搜索查看

举个例子：
REPLACE('aaa.yuyueq.cn'，'a'，'w')
结果为：www.yuyueq.cn

SUBSTRING(str,index)

它会从一个特定长度的位置开始，提取一个子字符串。

也可以写为SUBSTRING(str FROM index),举个例子：
SUBSTRING('www.yuyueq.cn',5)
结果为：'yuyueq.cn'
要注意的是它不在遵循计算机的规律，也就是它是从1开始数的，并不是0，如果index参数是0.则返回一个空字符串

当然也可以截取字符串中字符串，比如

(substring(str,index,length)和下面这个是一样的)
SUBSTRING(str FROM index FROM length),举个例子：
SUBSTRING('www.yuyueq.cn',5,6)
结果为：'yuyueq'

TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str)

是从字符串中删除不需要的字符

TRIM ( [ [位置] [要移除的字串] FROM ] 字串): [位置] 的可能值为 LEADING (起头), TRAILING (结尾), or BOTH (起头及结尾)。
这个函数将把 [要移除的字串] 从字串的起头、结尾，或是起头及结尾移除。如果我们没有列出 [要移除的字串] 是什么的话，那空白就会被移除

trim操作个人感觉多用于动态SQL中吧，可以看看简书的这篇：mybatis动态SQL – trim where set标签

FORMAT(N,D,locale)

格式化具有特定区域设置的数字，舍入到小数位数。

N是要格式化的数字。

D是要舍入的小数位数。

locale是一个可选参数，用于确定千个分隔符和分隔符之间的分组。如果省略locale操作符，MySQL将默认使用en_US。以下链接提供MySQL支持的所有区域名称：

LEFT()

获取指定长度的字符串的左边部分。

LENGTH()函数&CHAR_LENGTH()

它是以字节和字符获取字符串的长度。

2.2 聚合函数

COUNT()

首先官方已经说了,count(*)和count(1)没有区别

InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.

其次，count(字段)和count(*)、count(1)的区别是：

• count(字段)会进行全表扫描，效率会很差，不计算NULL值
• count()、count(1)会计算NULL值，而且count()等同于count(0)
• count如果没有匹配的行，count()它直接返回0

四个计算

AVG()：取平均值

SUM()：求和

这里要注意的是，如果没有匹配的行，则 SUM()返回 NULL。

min()：最小值

max()：最大值

GROUP_CONCAT()

此函数返回一个字符串结果

举个例子:
假设a是用户表，b是一张用户兴趣(id)关联表，c是一张兴趣表；
下面的意思就是我们查询这个用户相关信息的时候，将相关联的兴趣放到一个字符串字段中，相当于显示用户详情的操作
   SELECT
        group_concat(DISTINCT c.name) AS interestName
        FROM
        a
        left join b on a.id = b.user_id
        left join c on b.interest_id = c.id

官方例子：GROUP_CONCAT( [DISTINCT] expr [,expr …] [ORDER BY {unsigned_integer | col_name | expr}[ASC | DESC] [,col_name …] ] [SEPARATOR str_val])

由官方例子可以看出里面可以进行去重、排序、用特定的分隔符展示（默认是“,”）

其次还要注意的是，它是不可以和IN操作符使用的，原因就和find_in_set那个函数一样，IN的列表的是值列表，group_concat是个字符串列表

2.3 控制流函数

控制流 是计算机执行一个程序中语句的顺序。 程序会从第一行代码开始执行直至最后一行，除非遇到（实际中是非常普遍地）改变控制流的代码结构，比如条件语句和循环。

注：函数和sql语句的用法是不一样，所以要多注意一点，此处都是函数的用法。

IF(expr1,expr2,expr3)

官方例子最致命
如果expr1是TRUE (expr1 不等于0 和 expr1 IS NOT NULL)，则返回expr2，否则，返回expr3.
mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'

strcmp函数可以看这里：//dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html

IFNULL(expr1,expr2)

官方例子最致命
如果expr1不是 NULL， 则返回 expr1；否则返回 expr2。
mysql> SELECT IFNULL(1,0);
-> 1
mysql> SELECT IFNULL(NULL,10);
-> 10
mysql> SELECT IFNULL(1/0,10);
-> 10
mysql> SELECT IFNULL(1/0,'yes');
-> 'yes'

CASE

官方例子最致命
mysql> SELECT CASE 1 WHEN 1 THEN 'one'
->     WHEN 2 THEN 'two' ELSE 'more' END;
-> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
-> 'true'
mysql> SELECT CASE BINARY 'B'
->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
-> NULL

NULLIF(expr1,expr2)

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.
如果第一个参数等于第二个参数，则返回NULL，否则返回第一个参数

官方例子最致命
mysql> SELECT NULLIF(1,1);
-> NULL
mysql> SELECT NULLIF(1,2);
-> 1

2.4 日期函数

三、SQL优化

上面的内容其实也设计到了很多规范的问题，但毕竟我是为了举例子所以在这提一些规范的操作。

数据库设计

• 冷热数据的分离，从而可以减少表的宽度
• 列的字段类型尽量可小去满足ta，否则建立索引需要的空间会很大，影响性能
• 尽量不要使用TEXT,BLOB数据类型
• 尽可能把所有列定义为 NOT NULL，这也是为了防止查询的时候NullPointException异常的出现
• 及时给数据库表和字段增添注释
• TIMESTAMP(4 个字节) 或 DATETIME 类型 (8 个字节) 存储时间
  • 两者比时间戳更直观，但TIMESTAMP会有2038年的问题，
  • TIMESTAMP具有’1970-01-01 00:00:01’UTC 到’2038-01-19 03:14:07’UTC 的范围
  • 我个人是比较倾向于时间戳的，数据库中用bigint存储，编程中用Long值传递，至于前端展示就在前端做处理，很方便，就是数据库查看时间的时候不直观
  • mysql 数据库存时间最好是时间戳还是格式的时间

MySQL将TIMESTAMP值从当前时区转换为UTC进行存储，并从UTC返回到当前时区进行检索。
(默认情况下，每个连接的当前时区是服务器的时间。时区可以在每个连接的基础上设置。只要时区设置保持不变，你就会得到与你存储的相同的值。
如果你存储一个TIMESTAMP值，然后改变时区并检索该值，检索到的值与你存储的值不同。出现这种情况是因为在两个方向的转换中没有使用相同的时区。当前的时区可以作为time_zone系统变量的值。
要注意MySQL中日期值解释的某些属性。
MySQL允许对指定为字符串的值采用 “宽松 “格式，其中任何标点符号都可以用作日期部分或时间部分之间的分隔符。在某些情况下，这种语法可能具有欺骗性。例如，像’10:11:12’这样的值可能看起来像一个时间值，因为有:，但如果在日期上下文中使用，则被解释为年份’2010-11-12’。值’10:45:15’被转换为’0000-00-00’，因为’45’不是一个有效的月份。
在日期和时间部分与小数秒部分之间，唯一可识别的分隔符是小数点。
服务器要求月和日的值是有效的，而不仅仅是分别在1到12和1到31的范围内。在禁用严格模式的情况下，无效的日期如’2004-04-31’被转换为’0000-00-00’并产生一个警告。在启用严格模式的情况下，无效的日期产生一个错误。要允许这样的日期，请启用ALLOW_INVALID_DATES。参见第5.1.10节 “服务器SQL模式”，以了解更多信息。
MySQL不接受在日或月列中包含零的TIMESTAMP值或不是有效日期的值。这个规则的唯一例外是特殊的 “零 “值”0000-00-00 00:00:00″，如果SQL模式允许这个值。准确的行为取决于是否启用了严格的SQL模式和NO_ZERO_DATE SQL模式；参见章节5.1.10, “服务器SQL模式”。
包含2位数年值的日期是模糊的，因为世纪是未知的。MySQL使用这些规则解释2位数的年值。
00-69范围内的年值成为2000-2069。
在70-99范围内的年值成为1970-1999。

具体参见官方：//dev.mysql.com/doc/refman/5.6/en/date-and-time-types.html

SQL语句

• 尽量使用【select 字段】，而不要去使用【select *】
• 尽量将子查询变为JOIN语句，并且也要减少JOIN语句的使用，如果业务存在特殊要求，可以尝试使用虚拟表来提高查询效率
• where语句中，还是不要对列进行函数转换和计算
• 左右内连接要注意的是：ON后面的条件是为了生成两者临时表的条件，而where是为了筛选临时表中内容的条件
  • 而且不管on上的条件是否为真都会返回left或right表中的记录；但inner jion没有这个特殊性，当条件放在on中和where中，没有区别，返回的结果集是相同的
• 我们都知道union关键字后，可以获取去重后的数据，而union all关键字，获取的是所有数据，包含重复的数据
  • 所以当我们知道查出来的数据中没有重复值的时候选择union all，而且一般情况下尽可能的去选择union all，毕竟去重操作会遍历排序等等操作，消耗cpu资源。
• 以小表驱动大表
  • 小表并不是指数据量很小的表，而是与另一张表对比，在同一条件下，哪张表检索量小，才是小表

要注意的是具体查询的时候要根据业务需求来，确定主表，不能为了小表驱动大表，而破坏查询逻辑
当连接查询没有where条件时，左连接查询时，前面的表是驱动表，后面的表是被驱动表，右连接查询时相反，内连接查询时，哪张表的数据较少，哪张表就是驱动表
当连接查询有where条件时，带where条件的表是驱动表，否则是被驱动表
in 适用于左边大表，右边小表。
exists 适用于左边小表，右边大表。

• 尽量不要在group by后面使用having语句，通常都是where在前，group by在后的过滤筛选操作

• 对应同一列进行 or 判断时，使用 in 代替 or

  • in 的值不要超过 500 个，in 操作可以更有效的利用索引，or 大多数情况下很少能利用到索引

• 最后最重要的其实就是索引的问题，很多情况下要看sql到底有没有走索引，导致查询很慢，可以用explain命令去查看

  • 索引这块涉及点是比较多的，这里不作过多内容

• 索引失效的情况

  • 参见此篇文章：//zhuanlan.zhihu.com/p/338545029
  • 网上各种转载，还不给转载信息，也是替原作者可惜啊！

四、最后

有些情况下，我们要根据自己的业务来判断怎么使用SQL，但大多数情况下还是要遵循开发中默认好的规范操作。

这次是简单的记录了一下对与sql函数的应用理解，以及对于sql优化的应用，下次打算总结一下设计模式，和开发模型，

以前是因为很大程度上都是自己闷头学，企业级的项目也没有接触过，撑着这次实习把这些内容深刻的体会一下吧。