MySQL第八讲·如何进行数学计算、字符串处理和条件判断？

你好，我是安然无虞。

如何进行数学计算、字符串处理和条件判断？

MySQL 提供了很多功能强大，而且使用起来非常方便的函数，包括数学函数、字符串处理函数和条件判断函数等。

在很多场景中 ，我们都会用到这些函数，比如说，在超市项目的实际开发过程中，会有这样的需求：

• 会员积分的规则是一元积一分，不满一元不积分，这就要用到向下取整的数学函数 floor()；
• 在打印小票的时候，收银纸的宽度是固定的，怎么才能让打印的结果清晰而整齐呢？这个时候，就要用到 concat() 等字符串处理函数；
• 不同数据的处理方式不同，怎么选择正确的处理方式呢？这就会用到 IF(表达式，V1，V2) 这样的条件判断函数；……

这些函数对我们管理数据库、提高数据处理的效率有很大的帮助。

数学函数

我们先来学习数学函数，它主要用来处理数值数据，常用的功能有三类，分别是取整函数round(),ceil(),floor(), 绝对值函数abs()和求余函数mod().

知道了这些函数，我们来看看超市经营者的具体需求。他们提出，为了提升销量，要进行会员营销，主要是给会员积分，并以积分数量为基础，给会员一定的优惠。

积分的规则也很简单，就是消费一元积一分，不满一元不积分，那我们就需要对销售金额的数值进行取整。

这里主要用到四个表，分别是销售单明细表、销售单头表、商品信息表和会员信息表。为了方便理解，对表结构和数据进行了简化。

销售单明细表：

销售单头表：

商品信息表：

会员信息表：

这个场景下，可以用到MySQL中数学函数的取整函数，主要有3种：

• 向上取整ceil(x) 和 ceiling(x): 返回大于等于x的最小int类型整数；
• 向下取整floor(x): 返回小于等于x的最大int型整数；
• 舍入函数round(x, d): x 表示要处理的数，d表示保留的小数位数，处理的方式是四舍五入。round(x)表示保留0位小数。

现在的积分规则是一元一积分，不满一元不积分，显然是向下取整，那就可以用floor()函数。

首先我们要通过关联查询，获取会员消费的相关信息：

mysql>  select
    -> c.membername as '会员',   -- 从会员表获取会员名称
    -> b.transactionno as '单号',-- 从销售单头表获取单号
    -> b.transdate as '交易时间', -- 从销售单头表获取交易时间
    -> d.goodsname as '商品名称', -- 从商品信息表获取商品名称
    -> a.salesvalue as '交易金额'
    -> from
    -> demo.transactiondetails a
    -> join
    -> demo.transactionhead b on (a.transactionid = b.transactionid)
    -> join
    -> demo.membermaster c on (b.memberid = c.memberid)
    -> join
    -> demo.goodsmaster d on (a.itemnumber = d.itemnumber);
+------+------------------+---------------------+----------+----------+
| 会员 | 单号             | 交易时间            | 商品名称 | 交易金额 |
+------+------------------+---------------------+----------+----------+
| 张三 | 0120201201000001 | 2020-12-01 14:25:56 | 书       |   176.22 |
| 张三 | 0120201201000001 | 2020-12-01 14:25:56 | 笔       |    24.75 |
| 李四 | 0120201202000001 | 2020-12-02 10:50:50 | 书       |   234.96 |
| 李四 | 0120201202000001 | 2020-12-02 10:50:50 | 笔       |    26.40 |
+------+------------------+---------------------+----------+----------+
4 rows in set (0.01 sec)

接着，我们用floor(a.salesvalue), 对销售金额向下取整，获取会员积分值，代码如下：

mysql> select
    -> c.membername as '会员',
    -> b.transactionno as '单号',
    -> b.transdate as '交易时间',
    -> d.goodsname as '商品名称',
    -> a.salesvalue as '交易金额',
    -> FLOOR(a.salesvalue) as '积分'  -- 使用FLOOR函数向下取整
    -> from
    -> demo.transactiondetails a
    -> join
    -> demo.transactionhead b on (a.transactionid = b.transactionid)
    -> join
    -> demo.membermaster c on (b.memberid = c.memberid)
    -> join
    -> demo.goodsmaster d on (a.itemnumber = d.itemnumber);
+------+------------------+---------------------+----------+----------+------+
| 会员 | 单号             | 交易时间            | 商品名称 | 交易金额 | 积分 |
+------+------------------+---------------------+----------+----------+------+
| 张三 | 0120201201000001 | 2020-12-01 14:25:56 | 书       |   176.22 |  176 |
| 张三 | 0120201201000001 | 2020-12-01 14:25:56 | 笔       |    24.75 |   24 |
| 李四 | 0120201202000001 | 2020-12-02 10:50:50 | 书       |   234.96 |  234 |
| 李四 | 0120201202000001 | 2020-12-02 10:50:50 | 笔       |    26.40 |   26 |
+------+------------------+---------------------+----------+----------+------+
4 rows in set (0.01 sec)

类似的，如果用户的积分规则改为“不满一元积一分”，其实就是对金额数值向上取整，这个时候，我们就可以用 CEIL() 函数。操作方法和前面是一样的，这里就不具体解释了。

接下来再讲讲舍入函数round()的用法：

超市经营者提出，收银的时候，应收金额可以被设定四舍五入到哪一位。比如，可以四舍五入到元、角，或者到分。

按照指定的位数，对小数进行四舍五入计算，这样的场景就要用到round(x, d)了。它的作用就是通过四舍五入，对数值x保留d位小数。

根据超市经营者的要求，我们把函数round(x, d)中的保留小数的位数d设置成为0， 1， 2

如果要精确到分，我们可以设置保留两位小数：

mysql> select round(salesvalue,2) -- D设置成2，表示保留2位小数，也就是精确到分
-> from demo.transactiondetails
-> where transactionid=1 and itemnumber=1;
+---------------------+
| ROUND(salesvalue,2) |
+---------------------+
| 176.22 |
+---------------------+
1rows in set (0.00 sec)

除了刚刚我们所学习的函数，MySQL 还支持绝对值函数 ABS（）和求余函数 MOD（），ABS（X）表示获取 X 的绝对值；MOD（X，Y）表示获取 X 被 Y 除后的余数。

这些函数使用起来都比较简单，重点掌握它们的含义就可以了，下面学习下字符串函数。

字符串函数

除了数学计算，我们还经常会遇到需要对字符串进行处理的场景，比如我们想要在金额前面加上“￥”的符号，就会用到中字符串拼接函数；再比如，我们需要把一组数字以字符串的形式在网上传输，就要用到类型转换函数。

常用的字符串函数又4个：

• concat(s1, s2, …): 表示把字符串s1, s2…拼接起来，组成一个字符串
• cast(表达式 as char): 表示将表达式的值转换成字符串
• char_length(字符串): 表示获取字符串的长度
• space(n): 表示获取一个由n个空格组成的字符串

接下来我还是借助超市项目中的实际应用场景，来说明一下怎么使用这些字符串函数。

顾客交了钱，完成交易之后，系统必须要打出一张小票。打印小票时，对格式有很多要求。比如说，一张小票纸，57 毫米宽，大概可以打 32 个字符，也就是 16 个汉字。用户要求一条流水打 2 行，第一行是商品信息，第二行要包括数量、价格、折扣和金额 4 种信息。那么，怎么才能清晰地在小票上打印出这些信息，并且打印得整齐漂亮呢？这就涉及对字符串的处理了。

首先，我们来看一下如何打印第一行的商品信息。商品信息包括：商品名称和商品规格，而且商品规格要包含在括号里面。这样就必须把商品名称和商品规格拼接起来，变成一个字符串。

我们可以使用合并字符串函数concat(), 如下所示：

mysql> select
-> concat(goodsname, '(', specification, ')') as 商品信息 -- 这里把商品名称、括号和规格拼接起来
-> from
-> demo.goodsmaster
-> where itemnumber = 1;
+----------+
| 商品信息 |
+----------+
| 书(16开) |
+----------+
1 row in set (0.00 sec)

这样我们就得到了商品编号是1的商品，它的商品信息是：“书（16开）”。

第二步，我们来看一下如何打印第二行。第二行包括数量、价格、折扣和金额，一共4种信息。

因为一行最多32个字符，所以我们给数量分配7个字符，价格分配7个字符，折扣分配6个字符，金额分配9个字符，加上中间3个字符，正好是32个字符。

解释一下为什么要这么分配？

• 数量 7 个字符，就是小数点前面给 3 位，小数点后面给 3 位，外加小数点 1 位，最大 999.999，基本满足零售的需求了
• 同样道理，价格给 7 位，意思是小数点前面 4 位，小数点后面 2 位，外加小数点，这样最大可以表示 9999.99
• 折扣 6 位，小数点后面 2 位，小数点前面 2 位，加上小数点和“%”，这样是够用的
• 金额 9 位，最大可以显示到 999999.99，也够用了

分配好了各部分信息的字符串大小，再讲解一下格式处理，因为数据的取值每次都会不同，如果直接打印，会参差不齐。

这里以数量为例：

第一步，把数字转换成为字符串，这里我们需要用到把数值转换成为字符串的cast() 函数，如下所示：

mysql> select
-> CAST(quantity as CHAR) -- 把decimal类型转换成字符串
-> from
-> demo.transactiondetails
-> where
-> transactionid = 1 and itemnumber =1;
+---------------------+
| CAST(price as CHAR) |
+---------------------+
| 2.000 |
+---------------------+
1 rows in set (0.00 sec)

第二步，计算字符串的长度，这里我们要用到char_length()函数。

需要注意的是，虽然每个汉字打印的时候占2个字符长度，但是这个函数获取的是汉字的个数。因此，如果字符串中有汉字，函数获取的字符串长度跟实际长度是不一样的，需要用空格来补齐。

我们可以通过下面的查询，获取数量字段转换成字符串后的字符串长度。

mysql> select
-> char_length(CAST(quantity as char)) as 长度
-> from
-> demo.transactiondetails
-> where
-> transactionid = 1 and itemnumber =1;
+---------------------+
| 长度 |
+---------------------+
| 5 |
+---------------------+
1 rows in set (0.00 sec)

第三步，用空格补齐7位长度。这时，我们要用到space()函数。

因为我们采用左对齐的方式打印（左对齐表示字符串从左边开始，右边空余的位置用空格补齐），所以就需要先拼接字符串，再在字符串的后面补齐空格：

mysql> select
-> concat(CAST(quantity as char),
-> space(7 - char_length(cast(quantity as char)))) as 数量
-> from
-> demo.transactiondetails
-> where
-> transactionid = 1 and itemnumber = 1;
+----------+
| 数量 |
+----------+
| 2.000 |
+----------+
1 row in set (0.00 sec)

除此以外，MySQL 还支持 SUBSTR（）、MID（）、TRIM（）、LTRIM（）、RTRIM（）。

条件判断函数

我们刚才在对商品信息字符串进行拼接的时候，会有一种例外的情况，那就是当规格为空的时候，商品信息会变成“NULL”。这个结果显然不是我们想要的，因为名称变成 NULL，顾客会觉得奇怪，也不知道买了什么商品。我们希望，如果规格是空值，就不用加规格了。怎么实现呢？这就要用到条件判断函数了。

条件判断函数的作用，就是根据特定的条件返回不同的值，常用的有两种：

• ifnull(v1, v2): 表示如果v1的值不为空值，则返回v1, 否则返回v2;
• if(表达式, v1, v2): 如果表达式为真(TRUE)，则返回v1, 否则返回v2

我们希望规格是空的商品，拼接商品信息字符串的时候，规格不要是空。这个问题，可以通过ifnull(specification, ‘’)函数来解决。具体点说就是，对字段’specification’是否为空进行判断，如果是空就返回空字符串，否则就返回商品规格specification的值，代码如下：

mysql> select
    -> goodsname,
    -> specification,
    -> concat(goodsname,'(', ifnull(specification, ''),')') as 拼接 -- 用条件判断函数，如果规格是空，则括号中是空字符串
    -> from
    -> demo.goodsmaster;
+-----------+---------------+----------+
| goodsname | specification | 拼接     |
+-----------+---------------+----------+
| 书        | 16开          | 书(16开) |
| 笔        | NULL          | 笔()     |
+-----------+---------------+----------+
2 rows in set (0.00 sec)

结果是，如果规格为空，商品信息就变成了“商品信息（）”，好像还不错。但是也存在一点问题：商品名称后面的那个空括号“()”会让客人觉得奇怪，能不能去掉呢？

如果用 IFNULL（V1，V2）函数，就不容易做到，但是没关系，我们可以尝试用另一个条件判断函数 IF（表达式，V1，V2）来解决。这里表达式是 ISNULL(specification)，这个函数用来判断字段"specificaiton"是否为空，V1 是返回商品名称，V2 是返回商品名称拼接规格。代码如下所示：

mysql> select
    -> goodsname,
    -> specification,
    -> -- 这里做判断，如果是空值，返回商品名称，否则就拼接规格
    -> if(isnull(specification),
    -> goodsname,
    -> concat(goodsname, '(', specification, ')')) as 拼接
    -> from
    -> demo.goodsmaster;
+-----------+---------------+----------+
| goodsname | specification | 拼接     |
+-----------+---------------+----------+
| 书        | 16开          | 书(16开) |
| 笔        | NULL          | 笔       |
+-----------+---------------+----------+
2 rows in set (0.02 sec)

总结

上面只是介绍了一些常用到的函数，但在MySQL还有很多函数。

遇见安然遇见你，不负代码不负卿。

谢谢老铁的时间，咱们下篇再见～