mysql - 如何通过 Laravel 迁移触发每一行的 MySQL 过程

Question

我正在做一个关于 MySQL 的研究项目。现在，我正在尝试使用名为 Books 的表的过程来完成一项任务。如下:

        CREATE TABLE `books` (
          `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
          `title` VARCHAR(100) NOT NULL,
          `condition` ENUM('mint', 'new', 'medium', 'poor', 'needs replacement'),
          `date_added` DATE 
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 

在表中播种后，我的表中有 36 本书。我想做的是创建一个程序来修改 condition这本书的。它将检查此列中的值，如果 date_added列显示该书已添加>=一年前，其条件应更改为下一个 ENUM 条件。例如。如果这本书被添加为 mint一年前，它的状态应该更改为new ，如果new然后medium ，如果medium然后poor等等等等。

我写了一堆代码片段，但都失败了，这让我意识到，除了合成问题之外，我还面临着概念理解的问题，所以希望你能帮助我消除这些误解。

据我所知，事件的顺序是:

• 创建一个检查和更改单行值的过程；

• 创建一个循环来运行每个表行的过程。

现在，我看到触发器与 BEFORE 一起使用。和AFTER ，这意味着不可能手动触发该过程。这是正确的吗，还是有办法？我尝试在程序中使用循环，但没有得到结果。另外，我在 condition STRING 上不断收到错误，这并不奇怪，因为没有这样的类型。但是，我创建它的类型是 ENUM('mint', 'new', 'medium', 'poor', 'needs replacement') ，并且我不能在类型定义中使用它。希望得到帮助。

我目前遇到的变体如下:

    public function up(): void
    {
        $queryString = /** @lang text */
            "
                CREATE PROCEDURE updateBooksStatus(date DATE, condition ENUM('mint', 'new', 'medium', 'poor', 'needs replacement'))
                BEGIN
                    IF DATEDIFF('2019-08-27', date) >= 365
                        SELECT condition
                        CASE
                            WHEN condition = 'mint' THEN SET 'new'
                            WHEN condition = 'new' THEN SET 'medium'
                            WHEN condition = 'medium' THEN SET 'poor'
                        END
                        FROM `books`;
                    END IF; 
                END
            ";

        DB::connection()->getPdo()->exec($queryString);
    }

这是带有填充表 Books 的 DBfiddle:http://sqlfiddle.com/#!9/28176a/1

Answer 1

如果示例中的所有日期不相同，则不清楚会发生什么，但注释枚举已编入索引，请参阅 https://dev.mysql.com/doc/refman/8.0/en/enum.html

这意味着你可以做这样的事情

 select id,title,b.condition,b.date_added ,
            date_sub(date(now()),interval 1 year),
            b.`condition` + 0,
            case when date_added =  date_sub(date(now()),interval 1 year) then b.`condition` + 1 end as nxt, 
            b.`condition` + 1 newindex,
            s.condition newcondition
    from books b
    join
    (select distinct `condition`,`condition` + 0 cindex from books) s on s.cindex =     
                case when date_added =  date_sub(date(now()),interval 1 year) then b.`condition` + 1 end ;

+----+-------------------------------------------------------+-----------+------------+---------------------------------------+-------------------+------+----------+-------------------+
| id | title                                                 | condition | date_added | date_sub(date(now()),interval 1 year) | b.`condition` + 0 | nxt  | newindex | newcondition      |
+----+-------------------------------------------------------+-----------+------------+---------------------------------------+-------------------+------+----------+-------------------+
|  1 | White Fang                                            | mint      | 2018-08-27 | 2018-08-27                            |                 1 |    2 |        2 | new               |
|  2 | The Call of the Wild                                  | new       | 2018-08-27 | 2018-08-27                            |                 2 |    3 |        3 | medium            |
|  3 | Martin Eden                                           | new       | 2018-08-27 | 2018-08-27                            |                 2 |    3 |        3 | medium            |
|  4 | The Bishops Apron                                     | poor      | 2018-08-27 | 2018-08-27                            |                 4 |    5 |        5 | needs replacement |
|  5 | Six Stories Written in the First Person Singular      | new       | 2018-08-27 | 2018-08-27                            |                 2 |    3 |        3 | medium            |
|  6 | Judgment Seat                                         | mint      | 2018-08-27 | 2018-08-27                            |                 1 |    2 |        2 | new               |
|  7 | Princess September and the Nightingale                | poor      | 2018-08-27 | 2018-08-27                            |                 4 |    5 |        5 | needs replacement |
|  8 | Adventures of Huckleberry Finn                        | poor      | 2018-08-27 | 2018-08-27                            |                 4 |    5 |        5 | needs replacement |
|  9 | Adventures of Huckleberry Finn                        | poor      | 2018-08-27 | 2018-08-27                            |                 4 |    5 |        5 | needs replacement |
| 10 | Adventures of Huckleberry Finn                        | poor      | 2018-08-27 | 2018-08-27                            |                 4 |    5 |        5 | needs replacement |
| 11 | The Prince and the Pauper                             | new       | 2018-08-27 | 2018-08-27                            |                 2 |    3 |        3 | medium            |
| 12 | The Aleph and Other Stories                           | poor      | 2018-08-27 | 2018-08-27                            |                 4 |    5 |        5 | needs replacement |
| 13 | A Universal History of Iniquity                       | new       | 2018-08-27 | 2018-08-27                            |                 2 |    3 |        3 | medium            |
| 14 | Dreamtigers                                           | new       | 2018-08-27 | 2018-08-27                            |                 2 |    3 |        3 | medium            |
| 15 | Alices Adventures in Wonderland                       | new       | 2018-08-27 | 2018-08-27                            |                 2 |    3 |        3 | medium            |
| 16 | Through the Looking-Glass, and What Alice Found There | poor      | 2018-08-27 | 2018-08-27                            |                 4 |    5 |        5 | needs replacement |
| 17 | The Secret Adversary                                  | poor      | 2018-08-27 | 2018-08-27                            |                 4 |    5 |        5 | needs replacement |
| 18 | Murder on the Orient Express                          | poor      | 2018-08-27 | 2018-08-27                            |                 4 |    5 |        5 | needs replacement |
| 20 | The Shining                                           | mint      | 2018-08-27 | 2018-08-27                            |                 1 |    2 |        2 | new               |
| 21 | The Running Man                                       | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 22 | The Running Man                                       | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 23 | The Running Man                                       | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 24 | The Talisman                                          | mint      | 2018-08-27 | 2018-08-27                            |                 1 |    2 |        2 | new               |
| 25 | Hamlet                                                | poor      | 2018-08-27 | 2018-08-27                            |                 4 |    5 |        5 | needs replacement |
| 26 | MacBeth                                               | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 27 | Romeo and Juliet                                      | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 28 | Oliver Twist                                          | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 29 | The Pickwick Papers                                   | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 30 | David Copperfield                                     | mint      | 2018-08-27 | 2018-08-27                            |                 1 |    2 |        2 | new               |
| 32 | Animal Farm                                           | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 33 | Nineteen Eighty-Four                                  | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 34 | Nineteen Eighty-Four                                  | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 35 | Nineteen Eighty-Four                                  | medium    | 2018-08-27 | 2018-08-27                            |                 3 |    4 |        4 | poor              |
| 36 | Burmese Days                                          | mint      | 2018-08-27 | 2018-08-27                            |                 1 |    2 |        2 | new               |
+----+-------------------------------------------------------+-----------+------------+---------------------------------------+-------------------+------+----------+-------------------+

假设如果 5 年前添加了一本书作为新书，您将不得不根据日期差异添加 5 本书？