时间戳

首页 > 解决方案 > 如何通过 Laravel 迁移为每一行触发 MySQL 过程

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

问题描述

我正在做一个关于 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, if newthen medium, if mediumthenpoor等等。

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

在我看来，活动的顺序：

现在，我看到触发器与BEFOREand一起使用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);
    }

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

标签： mysqllaravel

解决方案

如果示例中的所有日期都不相同，但注释的枚举已编入索引，请参阅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？

推荐阅读