mysql - 在存储过程中更新和删除

Question

我在 mysql 数据库中有一个表，它包含值、开始日期和结束日期。这些值会不时更新，并根据结束日期更新。最后一次更新的结束日期始终为 NULL。例如:

ID   |   Value   |   Start_Date   |   End_Date
 1   |    0.1    |   2015-10-01   |   2015-10-10
 2   |    0.3    |   2015-10-05   |   2015-10-12
 2   |    0.4    |   2015-10-12   |     NULL
 1   |    0.5    |   2015-10-10   |     NULL
 3   |    0.2    |   2015-10-10   |     NULL

现在，假设我插入了一条没有意义的记录(值与以前相同 - 但“Start_Date”不同 - 对于 ID=1):

ID   |   Value   |   Start_Date   |   End_Date
 1   |    0.1    |   2015-10-01   |   2015-10-10
 2   |    0.3    |   2015-10-05   |   2015-10-12
 2   |    0.4    |   2015-10-12   |     NULL
 1   |    0.5    |   2015-10-10   |   2015-10-20
 **1   |    0.5    |   2015-10-20   |     NULL**

我想编写一个存储过程来查找这些行并“修复”它们。例如，运行 SP(对于 ID=1)后我想看到的内容:

ID   |   Value   |   Start_Date   |   End_Date
 1   |    0.1    |   2015-10-01   |   2015-10-10
 1   |    0.5    |   2015-10-10   |     NULL

意思是，我需要删除我插入的新行并将之前行的“End_Date”更新为“NULL”(我有更多字段 - 主键，进行比较以便我可以找到这两行 -问题是如何删除某一行并更新另一行)

谢谢!

Answer 1

好的，下面的怎么样。鉴于此示例数据:

CREATE TABLE t
    (`ID` int, `Value` decimal(5,2), `Start_Date` date, `End_Date` date)
;

INSERT INTO t
    (`ID`, `Value`, `Start_Date`, `End_Date`)
VALUES
    (1, 0.1, '2015-10-01', '2015-10-10'),
    (2, 0.3, '2015-10-05', '2015-10-12'),
    (2, 0.4, '2015-10-12', NULL),
    (1, 0.5, '2015-10-10', '2015-10-20'),
    (1, 0.5, '2015-10-20', NULL)
;

您现在可以做的是创建一个没有数据的表的副本:

CREATE TABLE tmp_t LIKE t;

然后插入表格的清理版本:

INSERT INTO tmp_t
SELECT MIN(ID), MIN(Value), MIN(Start_Date)
, IF(MIN(IFNULL(End_Date, '1970-01-01')) = '1970-01-01', NULL, MIN(IFNULL(End_Date, '1970-01-01')))
FROM (
    SELECT
    t.*
    , @gn := IF(@prev_value != `Value` OR @prev_id != ID, @gn + 1 , @gn) AS group_number
    , @prev_value := `Value`
    , @prev_id := ID
    FROM
    t
    , (SELECT @prev_value := NULL, @prev_id := NULL, @gn := 0) var_init_subquery
    ORDER BY Start_Date
) sq
GROUP BY group_number;

请注意，也可以这样做

CREATE TABLE tmp_t AS 
SELECT ...

但我选择了上面的版本，因为 CREATE TABLE ... LIKE ... 也像原始表一样创建主键、索引和外键约束等。 CREATE TABLE ... AS 不会这样做。

无论如何，你所要做的就是:

RENAME TABLE t TO t_backup, tmp_t TO t;

这将很快完成并且也是一个原子操作，因此即使在生产环境中使用也是安全的。

您的结果集将是:

mysql > SELECT * FROM t;
+------+-------+------------+------------+
| ID   | Value | Start_Date | End_Date   |
+------+-------+------------+------------+
|    1 |  0.10 | 2015-10-01 | 2015-10-10 |
|    2 |  0.30 | 2015-10-05 | 2015-10-12 |
|    1 |  0.50 | 2015-10-10 | 2015-10-20 |
|    2 |  0.40 | 2015-10-12 | NULL       |
|    1 |  0.50 | 2015-10-20 | NULL       |
+------+-------+------------+------------+

这是它的工作原理。我们在这里所做的就是扫描整个表并...
顺便说一句，这是我一开始使用的简化版本，假设您只关注一个 ID。保留它只是为了完整性，以防你想玩弄它。不妨忽略它。

SELECT MIN(ID), MIN(Value), MIN(Start_Date)
, IF(MIN(IFNULL(End_Date, '1970-01-01')) = '1970-01-01', NULL, MIN(IFNULL(End_Date, '1970-01-01')))
FROM (
    SELECT
    t.*
    , @gn := IF(@prev != `Value`, @gn + 1 , @gn) AS group_number
    , @prev := `Value`
    FROM
    t
    , (SELECT @prev := NULL, @gn := 0) var_init_subquery
    WHERE
    ID = 1
    ORDER BY Start_Date
) sq
GROUP BY group_number;

回到解释。 SELECT 子句逐行处理其中的每一行。所以 IF() 条件中的变量实际上持有它们的初始化值或上一行的值，因为当前行的值是在 IF() 之后赋值的功能进行了处理。所以我们所做的就是不断增加 @gn 变量，除非 Value(糟糕的列名)的值相同并且 ID 是相同(开始日期是“下一个”(我的英语很烂))。另请注意，这就是 ORDER BY 非常重要的原因。除非您指定，否则关系数据库中没有顺序，所以不要将其“优化”掉。

• here您可以阅读有关使用变量的更多信息