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其实是没有歧义的，也就被允许了。
今天这两个小案例虽然看起来不起眼，但是在实际应用中，还是需要多注意，否则很容易出现问题，今天就到这里吧。
![【DEDE[sql执行]】DEDE数据库语句 DEDESQL命令批量替换 SQL执行语句](https://developer.qcloudimg.com/http-save/10011/1865ef7b3a8d9da9768a4d3b09e731f0.jpg)
腾讯云开发者
