MySQL 常踩的坑

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) 对应行的数据。