mysql应用_MySQL实际应用的两个案例

01、alter table引发的一个问题

今天早晨，有个业务方提了个需求，是需要把一个表里的字段从date格式直接变成int格式，目的是为了去掉日期格式中间的短横线，类似2019-09-01变成20190901，也就是需要使用alter table的方法修改字段，这种操作一般在线上是不支持的，一般情况下，只有从tinyint改成int，或者从int改成unsigned int这种类型的转换，才会在线上的表做，这种跨类型的，一般不建议直接修改。

我首先询问了一下他表里面的数据量，得到的反馈是这个表是个配置表，数据量就30来条，这我就很放心了，这么小的数据量，即使出了问题，那么恢复起来也比较容易，为了以防万一，我现在测试环境上做了个测试：

mysql@127.0.0.1:yeyztest 10:47:18>>create table test14 (id int primary key,count_date date);

Query OK, 0 rows affected (0.08 sec)

mysql@127.0.0.1:yeyztest 10:47:50>>insert into test14 values (1,'2019-09-28');

Query OK, 1 row affected (0.01 sec)

mysql@127.0.0.1:yeyztest 10:48:28>>select * from test14;

+----+------------+

| id | count_date |

+----+------------+

| 1 | 2019-09-28 |

+----+------------+

1 row in set (0.00 sec)

mysql@127.0.0.1:yeyztest 10:48:34>>alter table test14 modify count_date int ;

Query OK, 1 row affected (0.02 sec)

Records: 1 Duplicates: 0 Warnings: 0

mysql@127.0.0.1:yeyztest 10:48:58>>select *from test14;

+----+------------+

| id | count_date |

+----+------------+

| 1 | 20190928 |

+----+------------+

1 row in set (0.00 sec)

创建了一个test14的表，里面包含两个字段，一个是id，一个是count_date，其中id是int类型的，count_date是date类型的，然后直接进行了一个alter表的修改操作，发现结果和预期的一致。这样就放心了，然后在线上的环境也操作了一下，结果傻眼了。。。

mysql@127.0.0.1 10:50:20>>select * from XXXX;

+----+------------+------------+----------+----------+----------+---------+---------+---------+

| id | count_date | count_type | count1 | count2 | count3 | count4 | count5 | count6 |

+----+------------+------------+----------+----------+----------+---------+---------+---------+

| 11 | 2019-09-28 | 1 | 2906.00 | 2123.00 | 1675.00 | 1347.00 | 1144.00 | 984.00 |

| 12 | 2019-09-28 | 2 | 0.83 | 0.81 | 0.65 | 0.61 | 0.54 | 0.36 |

| 13 | 2019-09-26 | 1 | 2532.00 | 1623.00 | 1245.00 | 942.00 | 789.00 | 681.00 |

----------------------

| 42 | 2019-10-09 | 2 | 0.97 | 0.98 | 0.94 | 0.85 | 0.62 | 0.35 |

+----+------------+------------+----------+----------+----------+---------+---------+---------+

32 rows in set (0.00 sec)

mysql@127.0.0.1 10:50:35>>alter table XXXX modify count_date int;

Query OK, 32 rows affected, 32 warnings (0.11 sec)

Records: 32 Duplicates: 0 Warnings: 32

mysql@127.0.0.1 11:04:42>>select * from XXXX ;

+----+------------+------------+----------+----------+----------+---------+---------+---------+

| id | count_date | count_type | count1 | count2 | count3 | count4 | count5 | count6 |

+----+------------+------------+----------+----------+----------+---------+---------+---------+

| 11 | 2019 | 1 | 2906.00 | 2123.00 | 1675.00 | 1347.00 | 1144.00 | 984.00 |

| 12 | 2019 | 2 | 0.83 | 0.81 | 0.65 | 0.61 | 0.54 | 0.36 |

| 13 | 2019 | 1 | 2532.00 | 1623.00 | 1245.00 | 942.00 | 789.00 | 681.00 |

| 14 | 2019 | 2 | 0.85 | 0.84 | 0.74 | 0.69 | 0.57 | 0.34 |

| 15 | 2019 | 1 | 2494.00 | 1610.00 | 1248.00 | 945.00 | 786.00 | 669.00 |

| 16 | 2019 | 2 | 0.85 | 0.82 | 0.69 | 0.68 | 0.54 | 0.35 |

| 17 | 2019 | 1 | 2424.00 | 1515.00 | 1129.00 | 873.00 | 709.00 | 605.00 |

| 18 | 2019 | 2 | 0.84 | 0.84 | 0.74 | 0.72 | 0.60 | 0.41 |

| 19 | 2019 | 1 | 88.00 | 60.00 | 40.00 | 30.00 | 23.00 | 20.00 |

| 20 | 2019 | 2 | 0.84 | 0.82 | 0.65 | 0.43 | 0.48 | 0.30 |

| 21 | 2019 | 1 | 31332.00 | 17783.00 | 12596.00 | 9692.00 | 8311.00 | 7181.00 |

| 22 | 2019 | 2 | 0.84 | 0.84 | 0.77 | 0.92 | 0.72 | 0.36 |

| 23 | 2019 | 1 | 28173.00 | 16656.00 | 12052.00 | 9262.00 | 7722.00 | 6472.00 |

| 24 | 2019 | 2 | 0.95 | 0.95 | 0.91 | 0.85 | 0.61 | 0.34 |

第二列直接全部变成2019了，这下坏了，直接给截断了，这到底是为什么？来不及想了，先回复数据吧，因为只有30来条数据，就直接拼SQL的方法给恢复了。

后面静下心来想这个问题，为什么测试环境和线上环境的结果不一样呢？详细查看了一番之后，发现问题出在了数据库的版本上，线上的版本是老版本5.5.19，而测试环境是5.7.16，这样问题就明确了，在5.7.16上面，直接变更字段从date类型到int类型，则会去掉date类型的短横线，而在5.5.19版本上，如果进行变更，则会发生截断。

这让我想起了上个礼拜听一个报告的时候，一位行业内老师说的一句话：永远选择最好的机器，用最新的数据库版本，这样能解决你很大一部分问题。确实是，旧的版本还是有些问题处理的不到位，所以才会被迭代。

通过这个事情，有以下几个小收获吧，第一，在线上操作之前，需要现在测试环境搞一把，确认无误之后再进行操作，第二，任何操作之前，都要想到回滚方案，出故障没法解决的操作，我劝大家还是不要做。

02、关于distinct和order by 的一个问题

之前讲过关于MySQL5.7版本的一些参数，其中包含了sql_mode，这个参数是控制MySQL服务的SQL运行模式，5.7版本的模式更为严格，SQL mode参数里面包含大约7个项，今天我遇到的问题主要是关于only_full_group_by相关的，这个参数，说白了就是一个SQL，select出来的字段中需要包含group by出来的字段，关于这个问题，之前的6月27日的文章中有说到过。

今天遇到的问题是，一个业务方写了一个sql，类似于select distinct a,b from table order by c;

然后一直报错，内容是Expression #1 of ORDER BY clause is not in select list.也就是说，order by的字段c不在select的字段a和b当中，然而，去掉了这个SQL中的distinct之后，这个SQL执行起来就没有错误了。

一开始，对于这个错误，我还没有想明白为什么，后面举了个例子想了想，确实是不应该这样。为了方便理解，我写个例子：

-----------

|A B C|

-----------

|1 2 3|

-----------

|2 2 4|

-----------

|1 2 5|

-----------

例如这样的一个表，其中a b c为这个表的三个字段，上面的SQL：

select distinct a，b from table order by c；

首先要对a b这个组合去重，我们可以看到，这个组合中，a=1，b=2的记录有两条，而这两条记录的c值是不一样的，那么到底用哪一条记录呢？

如果使用第一条记录c=3，那么按照c字段进行order by 的时候，a=1，b=2的记录就排在a=2，b=2的记录前面；

如果使用第二条记录c=5，那么按照c字段进行order by 的时候，a=1，b=2的记录就排在a=2，b=2的记录后面；

使用不同的记录会产生不同的排序结果，那么MySQL怎么知道使用哪一条记录呢？答案是蒙圈了。所以这种SQL就是不被允许的，如果去掉了distinct，那么这样的SQL其实是没有歧义的，也就被允许了。

今天这两个小案例虽然看起来不起眼，但是在实际应用中，还是需要多注意，否则很容易出现问题，今天就到这里吧。