MySQL 常踩的坑【updating…】
1. 问题一
今天在执行一组SQL时，遇到了一个不可“逆天”的错误。导致数据库数据**“无故”**丢失！差点给公司带来无法挽回的损失！
2. SQL 过程
- 查看表
dim_shop的数据
mysql> select count(*) from dim_shop;
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.00 sec)
- 创建一个表
dim_shop_2
mysql> create table dim_shop_2
-> select
-> max(shop_key)
-> from dim_shop;
Query OK, 1 row affected (0.23 sec)
Records: 1 Duplicates: 0 Warnings: 0
注意创建临时表的SQL。可以看到dim_shop_2中只有一条数据。接着执行如下SQL：
mysql> delete from dim_shop
-> where shop_key in (select shop_key from dim_shop_2);
Query OK, 2 rows affected (0.11 sec)
最后查看dim_shop表的数据，发现为0条！
mysql> select count(*) from dim_shop;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
到底是什么问题导致 dim_shop 的数据为0 条呢？主要原因如下。
3. 原因
我们创建表dim_shop_2 时的SQL如下：
create table dim_shop_2
select
max(shop_key)
from dim_shop_temp;
注意这里的max(shop_key)，没有使用别名，导致出现的结果是下面这样：
mysql> select * from dim_shop_2;
+---------------+
| max(shop_key) |
+---------------+
| 22 |
+---------------+
1 row in set (0.00 sec)
结果出来的表字段就是 max(shop_key) 。而不是 shop_key 。所以在执行删除语句时：
delete from dim_shop
where shop_key in (select shop_key from dim_shop_2);
将where 之后的语句当做了true，所以将 dim_shop 中的数据全部删除了。
单独执行如下SQL
mysql> select shop_key from dim_shop_2;
ERROR 1054 (42S22): Unknown column 'shop_key' in 'field list'
会报一个错误，但是mysql 却对这个错误置之不理。而是将其翻译成true，直接删除了整个表数据。
2. 问题二
常见的group by操作会带来一些问题。
我们不能直接按照 group by 去取某一个字段对应的那一行值，这么取可能会是有问题的。如下：
mysql> select start_date,end_date,max(shop_key) from dim_shop_2 group by shop_id;
+---------------------+---------------------+---------------+
| start_date | end_date | max(shop_key) |
+---------------------+---------------------+---------------+
| 2017-05-24 13:11:58 | 2018-10-26 21:00:45 | 5 |
+---------------------+---------------------+---------------+
1 row in set (0.00 sec)
我们想取shop_key = 5这行的值，但是取出来的 start_date end_date 却是与如下的SQL 不同：
mysql> select start_date,end_date, shop_key from dim_shop_2 where shop_key = 5;
+---------------------+---------------------+----------+
| start_date | end_date | shop_key |
+---------------------+---------------------+----------+
| 2018-11-02 20:09:37 | 9999-12-31 00:00:00 | 5 |
+---------------------+---------------------+----------+
1 rows in set (0.00 sec)
即不能根据group by 取的 max(shop_key) 去获取 max(shop_key) 对应行的数据。
