Laravel使用笔记 —— migration

在使用 php artisan make:migration 创建migration时,可用 --path 指定创建migration文件的路径，

如果在执行的 php artisan migrate 命令，出现找不到对应class，

可以用 php artisan clear-compiled 和 php artisan optimize 命令 移除编译过的类文件和优化，

命令详细的参数可以通过 php artisan help [command] 命令来查看。

 

在migration中创建表以及数据库相关的，使用到原生的sql脚本时，可以用调用DB类中的unprepared方法实现：

 1 <?php
 2 
 3 use Illuminate\Database\Schema\Blueprint;
 4 use Illuminate\Database\Migrations\Migration;
 5 use Illuminate\Support\Facades\DB;
 6 
 7 class CreateItemTempTable extends Migration
 8 {
 9     /**
10      * Run the migrations.
11      *
12      * @return void
13      */
14     public function up()
15     {
16         Schema::create('item_temp', function (Blueprint $table) {
17             $table->engine = 'InnoDB';
18             $table->charset = 'utf8';
19             $table->collation = 'utf8_general_ci';
20 
21             $table->string('o_id',50)->notNull()->comment('订单编号');
22             $table->string('outer_oi_id',50)->default(null)->comment('子订单号');
23             $table->string('sku_id')->notNull()->comment('商家SKU');
24             $table->string('name',100)->default(null)->comment('商品名称');
25             $table->decimal('amount',12,2)->default(0.00)->comment('应付金额');
26             $table->decimal('base_price',12,2)->default(0.00)->comment('基本价（拍下价格）');
27             $table->decimal('price',12,2)->default(0.00)->comment('');
28             $table->string('properties_value',100)->default(null)->comment('属性');
29             $table->integer('qty')->default(0)->comment('购买数量');
30             $table->string('raw_so_id',50)->default(null)->comment('');
31             $table->string('refund_id',50)->default(null)->comment('退货ID');
32             $table->integer('refund_qty')->default(0)->comment('退货数量');
33             $table->string('refund_status',40)->default(null)->comment('退货状态');
34             $table->string('shop_sku_id')->default(null)->comment('网站对应的自定义SKU编号');
35 
36         });
37 
38        
39         DB::unprepared('
40             CREATE TRIGGER `sync_to_item_table`
41             BEFORE INSERT
42             ON `item_temp` FOR EACH ROW
43             BEGIN
44                 IF NOT EXISTS(SELECT id FROM `item` WHERE o_id = new.o_id AND sku_id = new.sku_id ) THEN
45                     INSERT INTO `item`(o_id,outer_oi_id,sku_id,name,amount,base_price,price,properties_value,qty,raw_so_id,refund_id,refund_qty,refund_status,shop_sku_id)
46                     VALUES(new.o_id,new.outer_oi_id,new.sku_id,new.name,new.amount,new.base_price,new.price,new.properties_value,new.qty,new.raw_so_id,new.refund_id,new.refund_qty,new.refund_status,new.shop_sku_id);
47                 ELSE
48                     UPDATE `item` SET
49                     outer_oi_id = new.outer_oi_id,
50                     name = new.name,
51                     amount = new.amount,
52                     base_price = new.base_price,
53                     price = new.price,
54                     properties_value = new.properties_value,
55                     qty = new.qty,
56                     raw_so_id = new.raw_so_id,
57                     refund_id = new.refund_id,
58                     refund_qty = new.refund_qty,
59                     refund_status = new.refund_status,
60                     shop_sku_id = new.shop_sku_id
61                     WHERE o_id = new.o_id AND sku_id = new.sku_id;
62                 END IF;
63             END
64         ');
65 
66 
67         DB::unprepared('
68             CREATE EVENT IF NOT EXISTS `clear_item_temp`
69             ON SCHEDULE
70             EVERY 1 DAY
71             DO TRUNCATE `item_temp`
72         ');
73     }
74 
75     /**
76      * Reverse the migrations.
77      *
78      * @return void
79      */
80     public function down()
81     {
82         Schema::drop('item_temp');
83         DB::unprepared('DROP TRIGGER IF EXISTS `sync_to_item_table`');
84         DB::unprepared('DROP EVENT IF EXISTS `clear_item_temp`');
85 
86     }
87 }

 

转载于:https://www.cnblogs.com/xiezero/p/5953604.html