2021-04-27日记——mysql

mysql的问题更新。

一：sum求和

因为测试需要，对数据应付的总金额进行求和，结果发现，出现了精度问题。

求和——都是做加法的情况下怎么会产生了精度问题，这就要看我求和的对象是什么类型的。

原因：

这里我求和的对象——“final_fee”是varchar。
mysql属性是float、double或者string，现在sum函数需要对这个列的数据进行累加操作，会隐式转化这个列的值为数值类型，在sum聚合函数使用后，就可能在结果中出来很多的小数点。
（注意，进行转化的时候，需要这个列的数据恰好是那种可以转化为数字的数据，当然也包括含有e级的数据。如果转化的对象不可转化为数据，那么mysql会抛错）。

处理方案

1:直接加上强制转化类型，并且指定精度

select sum(cast(final_fee as decimal(10,2))) from wms_car_receivable_daily_fee where car_unique in ("VNN6382D1F286B4E6") and settle_status =1 and status=1

在sum内置函数的计算对象外层，包了一层数据转换，并且指定列精度，（这里的10是指浮点数的整数部分+小数部分总和是10位，而里面的2是指其中的小数部分为2位）。
这种方式，其实是结果上的截断隐藏。

2:由于我们这里实际业务，这个字段记录的是金额，所以可以直接在数据中存储的数据，用分的形式进行存储。这样，记录的数据都是整数，sum()函数，计算整数的时候，即使是类型转化，也不会出现精度问题。

3:关于这个字段定义的类型，我感觉在定义的时候，就应该定义成demical而不是varchar，这样更省事儿，关于这个问题咨询了我司的BI大佬。他给的解释是：

具体的实现原理

感觉这块东西，google上搜索得到的好少，希望有好心人看到能帮我解解惑。（具体是在转格式的时候就有精度的差异了还是计算了之后出现的精度的差异，虽然我感觉像是转格式出现的精度差）

找来的mysql隐式转换格式优先级

隐式转换数据格式，是按照数据类型的优先级确定转换方向和顺序，常见的数据类型的优先顺序见上。

https://www.essentialsql.com/datatype-conversion-in-sql-using-cast-and-convert/

https://zhuanlan.zhihu.com/p/64341811 这个看起来写的很全面，但是现在看不懂，先收藏，要早点能看懂啊

二：ROW_NUMBER():获取行号。

选自mysql8.0参考手册
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html

row_number():用作窗口函数的非聚合函数。一般和窗口函数一起使用，查看此窗口/分区下的行号，因为窗口操作，不会将查询行组折叠为单个输出行，还是会为每一行产生一个结果。
比如有如下的数据表。

note：这里注意下，order by 排序，后面可以"，"分隔填写多个排序字段，然后是第一排序、第二排序……诸如此类。

那么在这个基础上，计算每个country的profit总和，有2种方式。
（一）：sum求和

select country, sum(profit) from sales group by country order by country;

（二）：使用窗口函数

select year, country, product, profit, sum(profit) over(partition by country) from sales order by country;

查询中的每个窗口操作，都通过包含一个over子句来实现，partition by country是指按照country 字段对行进行分区，同上述group by 的作用，但是区别是窗口函数得到的同组数据不会折叠，会保留展示每一行的数据，而第一种方式，会折叠。

窗口函数里面的over子句为空，那么分区对象是表中的所有数据，就把所有数据进行相加来。

（三）：窗口函数与获取行号相结合，求出每个分组分别的行号

select year, country, product, profit, row_number() over(partition by country) as row_num1 from sales;

查询这个表中按照country分区之后，在同一个country中每条数据对应的行数。由于在窗口函数的子句中未加上分组的组内排序，所以里面是mysql无序的状态。

可以在窗口函数的子句中增加排序，这样获取的行号更有序。

select year, country, product, profit, row_number() over(partition by country oder by year,product) as row_num2 from sales;

这样得到的排序，是每条数据在自己所在的country下，按照year（数字升序）和product（字母升序）排序后的对应的第几行的行号。

https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
例子取自mysql 8.0参考手册

三：explain：解析sql执行计划，查看具体执行过程。

在mysql语句最前面，添加explain，可以得到这个执行计划的具体执行过程步骤。
例如：

explain select cc_car.car_unique,cc_car.`asset_right_name` from cc_car inner join wms_car on wms_car.cc_car_id=cc_car.car_id where wms_car.store_status = 10 and wms_car.warehouse_id in (select  warehouse_id from wms_warehouse_basic where type in (2,3,4,6,7,8,9,10,11,12,13));

得到的执行过程如下。

从上到下的顺序就是这个SQL的执行顺序。
对于比较简单的sql语句，就无序测试计划，执行过程就比较简单。
例如：

`explain select * from wms_car_receivable_daily_fee where car_unique in ("VNN6382D1F286B4E6") and settle_status =1 and status =1

四：慢SQL优化（待完善）

select * ,like之类的语句，进行优化
union all 替换为 or
查找出走索引的字段，对走索引的字段进行优化处理

** ps: union all 与union的区别**
a union all b:获取a与b的全部数据，包括重复的行，也会多次显示，不去重结果也不排序，集最终的结果是a的行数+b的行数
a union b: 获取a与b的的数据并集，但是会进行去重，而且默认会对结果进行排序。
note：二者都需要处理的2个数据有相同个数且相同类型的列。

五：mysql查看具体的SQL执行时间（待完善）