MySQL必知必会 10丨如何进行数学计算、字符串处理和条件判断？

数学函数

主要用来处理数值数据，常用的主要有 3 类，分别是

取整函数ROUND()、CEIL()、FLOOR()
绝对值函数 ABS()
求余函数 MOD()

数据准备：

demo.transactiondetail

mysql> select transactionid, itemnumber, quantity, price, discount, salesvalue
    -> from demo.transactiondetails
    -> order by transactionid, itemnumber;
+---------------+------------+----------+-------+----------+------------+
| transactionid | itemnumber | quantity | price | discount | salesvalue |
+---------------+------------+----------+-------+----------+------------+
|             1 |          1 |        2 | 89.00 |     0.99 |     176.22 |
|             1 |          2 |        5 |  5.00 |     0.99 |      24.75 |
|             2 |          1 |        3 | 89.00 |     0.88 |     234.96 |
|             2 |          2 |        6 |  5.00 |     0.88 |      26.40 |
|             3 |          1 |        1 | 89.00 |     0.95 |      84.55 |
|             3 |          2 |       10 |  5.00 |     0.95 |      47.50 |
+---------------+------------+----------+-------+----------+------------+

demo.transacionhead

mysql> select transactionid, transactionno, cashierid, memberid, operatorid, transdate
    -> from demo.transactionhead;
+---------------+------------------+-----------+----------+------------+---------------------+
| transactionid | transactionno    | cashierid | memberid | operatorid | transdate           |
+---------------+------------------+-----------+----------+------------+---------------------+
|             1 | 0120201201000001 |         1 |        1 |          1 | 2020-12-01 00:00:00 |
|             2 | 0120201202000001 |         1 |        2 |          2 | 2020-12-02 00:00:00 |
|             3 | 0120201202000002 |         1 |     NULL |          1 | 2020-12-01 01:00:00 |
+---------------+------------------+-----------+----------+------------+---------------------+

demo.goodsmaster

mysql> select * from demo.goodsmaster;
+------------+---------+-----------+---------------+------+-----------+
| itemnumber | barcode | goodsname | specification | unit | saleprice |
+------------+---------+-----------+---------------+------+-----------+
|          1 | 0001    | 书        |               | 本   |     89.00 |
|          2 | 0002    | 笔        |               | 支   |      5.00 |
+------------+---------+-----------+---------------+------+-----------+

demo.membermaster

mysql> select memberid, branchid, cardno, membername, address, phone, pid, memeberpoints, memberdeposit from demo.membermaster;
+----------+----------+----------+------------+---------+-------------+--------------------+---------------+---------------+
| memberid | branchid | cardno   | membername | address | phone       | pid                | memeberpoints | memberdeposit |
+----------+----------+----------+------------+---------+-------------+--------------------+---------------+---------------+
|        1 |        1 | 10000001 | 张三       | 天津    | 13698765432 | 475145197001012356 |          0.00 |          0.00 |
|        2 |        1 | 10000002 | 李四       | 上海    | 18758079161 | 123123199001012356 |          0.00 |          0.00 |
+----------+----------+----------+------------+---------+-------------+--------------------+---------------+---------------+

取整函数

向上取整 ceil(X) 和 ceiling(X)：返回大于等于 X 的最小 INT 型整数
向下取整 floor(X)：返回小于等于 X 的最大 INT 型整数
舍入函数 round(X,D)：X 表示要处理的数，D 表示保留的小数位数，处理的方式是四舍五入，ROUND(X) 表示保留 0 位小数

比如说，在超市项目的实际开发过程中，会有这样的需求：

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

通过关联查询，获得会员消费的相关信息：

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 (b.transactionid = a.transactionid)
    -> join demo.membermaster c on (c.memberid = b.memberid)
    -> join demo.goodsmaster d on (d.itemnumber = a.itemnumber);
+------+------------------+---------------------+----------+----------+
| 会员 | 单号             | 时间                | 商品名称 | 交易金额 |
+------+------------------+---------------------+----------+----------+
| 张三 | 0120201201000001 | 2020-12-01 00:00:00 | 书       |   176.22 |
| 张三 | 0120201201000001 | 2020-12-01 00:00:00 | 笔       |    24.75 |
| 李四 | 0120201202000001 | 2020-12-02 00:00:00 | 书       |   234.96 |
| 李四 | 0120201202000001 | 2020-12-02 00:00:00 | 笔       |    26.40 |
+------+------------------+---------------------+----------+----------+

用 floor(a.salesvalue)，对销售金额向下取整，获取会员积分值。如果用户的积分规则改为“不满一元积一分”，其实就是对金额数值向上取整，这个时候就可以用 CEIL() 函数。

select c.membername as '会员',
b.transactionno as '单号',
b.transdate as '时间',
d.goodsname as '商品名称',
floor(a.salesvalue) as '积分'
from demo.transactiondetails a
join demo.transactionhead b on (b.transactionid = a.transactionid)
join demo.membermaster c on (c.memberid = b.memberid)
join demo.goodsmaster d on (d.itemnumber = a.itemnumber);

+------+------------------+---------------------+----------+------+
| 会员 | 单号             | 时间                | 商品名称 | 积分 |
+------+------------------+---------------------+----------+------+
| 张三 | 0120201201000001 | 2020-12-01 00:00:00 | 书       |  176 |
| 张三 | 0120201201000001 | 2020-12-01 00:00:00 | 笔       |   24 |
| 李四 | 0120201202000001 | 2020-12-02 00:00:00 | 书       |  234 |
| 李四 | 0120201202000001 | 2020-12-02 00:00:00 | 笔       |   26 |
+------+------------------+---------------------+----------+------+

或者：

-- 使用函数 mod() 计算浮点数的小数部分，并从原始值中减去该小数部分的结果
mysql> select 1.5 - mod(1.5, 1);
+-------------------+
| 1.5 - mod(1.5, 1) |
+-------------------+
|               1.0 |
+-------------------+

mysql> select 1.1 - mod(1.1, 1);
+-------------------+
| 1.1 - mod(1.1, 1) |
+-------------------+
|               1.0 |
+-------------------+

需求：收银的时候，应收金额可以被设定四舍五入到哪一位。比如，可以设定四舍五入到元、到角，或者到分。用 ROUND（X,D）了。它的作用是通过四舍五入，对数值 X 保留 D 位小数。

mysql> select round(salesvalue, 2)
    -> from demo.transactiondetails where transactionid=1 and itemnumber=1;
+----------------------+
| round(salesvalue, 2) |
+----------------------+
|               176.22 |
+----------------------+

mysql> select round(salesvalue, 1)
    -> from demo.transactiondetails where transactionid=1 and itemnumber=1;
+----------------------+
| round(salesvalue, 1) |
+----------------------+
|                176.2 |
+----------------------+

mysql> select round(salesvalue)
    -> from demo.transactiondetails where transactionid=1 and itemnumber=1;
+-------------------+
| round(salesvalue) |
+-------------------+
|               176 |
+-------------------+

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 种信息。那么，怎么才能清晰地在小票上打印出这些信息，并且打印得整齐漂亮呢？

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

mysql> select concat(goodsname, '(', specification, ')') as 商品信息
    -> from demo.goodsmaster where itemnumber=1;
+----------+
| 商品信息 |
+----------+
| 书(16开) |
+----------+

如何打印第二行。第二行包括数量、价格、折扣和金额，一共是 4 种信息。

把数量转为字符串，用到 CAST（）函数。

mysql> select cast(quantity as char)  -- 把数量转换成字符串，把decimal类型转换成字符串
    -> from demo.transactiondetails
    -> where transactionid=1 and itemnumber=1;
+------------------------+
| cast(quantity as char) |
+------------------------+
| 2                      |
+------------------------+

计算字符串的长度，用到 CHAR_LENGTH（）函数。

需要注意的是，虽然每个汉字打印的时候占 2 个字符长度，但是这个函数获取的是汉字的个数。因此，如果字符串中有汉字，函数获取的字符串长度跟实际打印的长度是不一样的，需要用空格来补齐。
1
2
3
4
5
6
7
mysql> select char_length(cast(quantity as char)) as 长度
-> from demo.transactiondetails where transactionid=1 and itemnumber=1;
+------+
| 长度 |
+------+
| 1 |
+------+

空格补齐 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       |
+---------+

条件判断函数

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

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 | 笔() |
+-----------+---------------+----------+

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

如果用 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)

这个结果就是，如果规格为空，商品信息就是商品名称；如果规格不为空，商品信息是商品名称拼接商品规格，这就达到了我们的目的。

小结

数学计算函数

floor(x): 获取小于等于x的最大整数
ceil(x): 获取大于等于x的最小整数
round(x, d): 通过四舍五入，获得最接近x的，保留d位小数的数值
abs(): 获取x的绝对值
mod(x, y): 获取x被y整除后的余数

字符串函数

concat(s1,s2,…): 连接字符串s1，s2，…
char_length(s): 获取字符串s中的字符的个数，一个汉字算一个字符
space(n): 获取由n个空格组成的字符串
substr() 和 mid(): 获取字符串中的子字符串
trim(): 删除字符串开头两端的子字符串
ltrim(): 删除字符串开头部分的空格
rtrim(): 删除字符串结尾部分的空格

条件判断函数

ifnull(v1, v2): 如果v1为空，返回v2，否则返回v1
if(表达式, v1, v2): 如果表达式为真，返回v1，否则返回v

函数也有一些坑，比如round(x)对x四舍五入后不一定比原来的x大：

mysql> select round(-1.5);
+-------------+
| round(-1.5) |
+-------------+
|          -2 |
+-------------+

mysql> select round(-1.5, 0);
+----------------+
| round(-1.5, 0) |
+----------------+
|             -2 |
+----------------+

mysql> select round(-1.5, 1);
+----------------+
| round(-1.5, 1) |
+----------------+
|           -1.5 |
+----------------+

Channing's Blog

MySQL必知必会 10丨如何进行数学计算、字符串处理和条件判断？

数学函数

取整函数

字符串函数

条件判断函数

小结

数学计算函数

字符串函数

条件判断函数