migrate数据库迁移

一、migrate命令

由于Yii migrate 生成的迁移文件默认是存放在 app/migrations 目录下面，如果想要生成的迁移文件到自己指定的目录(例如 dir/migrations),加上如下参数：

yii migrate/create test_table -p=@dir/migrations

如果需要指定到特定的数据库，加上如下参数：

yii migrate/create test_table --db=dbName

 

查看migrate帮助： ./yii help migrate

生成一个迁移文件的命令：./yii migrate/create  test_table -p=@dir/migrations  --db=dbName

执行该迁移文件可使用  ./yii migrate/to  m180227_023510_test_table -p=@dir/migrations --db=dbName

查看 dbName 的最近 n 条历史记录：./yii migrate/history n

还原 dbName 最近的 n 条数据库迁移: ./yii migrate/down n --db=dbName

还原上一次执行的迁移文件可使用  ./yii migrate/down  -p=@dir/migrations 

执行全部 migrations ：./yii migrate/auto

执行某个特定目录下面的migrations :./yii migrate -p=@dir/migrations/test  --db=dbName

二、migration文件写法

1、migrate 的标准格式：

<?php
/*
 * @purpose : migrate的标准格式
 * @author :  daicr
 * @time   :  2018-05-7
 * */
use yiidbMigration;

class m180505_051217_table_name_init extends Migration
{
    public $db = 'db1';
    public $tableName = 'table_name';

    public function init()
    {
        //$this->tableName = 'db2.table_name_2';  // 如果要修改另一个库的表加上这句
        parent::init();
    }

    public function up()
    {
        $tableOptions = null;
        if($this->db->driverName='mysql'){
            $tableOptions = 'CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE=InnoDB';
        }

        $this->createTable($this->tableName,[
            'id'=>$this->primaryKey(11)->unsigned()->notNull()->comment('主键ID'),
            'name'=>$this->string(32)->notNull()->defaultValue('')->comment('姓名'),
            'remarks'=>$this->text()->comment('备注');
            
            ...
            
            'created_at'=>$this->integer(11)->notNull()->defaultValue(0)->comment('创建时间'),
            'updated_at'=>$this->integer(11)->notNull()->defaultValue(0)->comment('更新时间'),
            'is_delete'=>$this->smallInteger(1)->notNull()->defaultValue(0)->comment('是否删除，1：是，0：否'),
        ],$tableOptions.' COMMENT "用户表"');
    }

    public function down()
    {
        $this->dropTable($this->tableName);
        return true;
    }

}

2、在迁移文件中执行原生 sql :

public function safeUp()
{
    $this->execute(<<<EOF
    INSERT INTO tableName VALUES ('1', 'value1', 'value2', 'value3');
    INSERT INTO tableName VALUES ('2', 'value1', 'value2', 'value3');
EOF
);
}

3、添加和删除字段：

    public function up()
    {
        $this->addColumn($this->tableName,'test_field', $this->integer(11)->notNull()->defaultValue(0)->comment('测试字段'));
    }

    public function down()
    {
        $this->dropColumn($this->tableName, 'im_customer_id');

        return true;
    }

4、批量迁移多张表

<?php

use yiidbMigration;

/**
 * @purpose: xxx模块儿表迁移文件
 * Class m190528_031851_organize_tables_init
 */
class m190528_031851_fusionpbx_tables_init extends Migration
{
    public $db = 'dbConf';

    public $tableName = 'conf_field';

    public function init()
    {
        $this->db = 'dbConf';

        parent::init();
    }

    /**
     * {@inheritdoc}
     */
    public function safeUp()
    {
        $map = call_user_func([$this,'map']);

        foreach($map as $key => $val){
            $data = $this->migrateSchema($key,call_user_func([$this,$val]));
            $this->batchInsert($this->tableName, array_keys($data[0]), $data);
        }
    }

    /**
     * {@inheritdoc}
     */
    public function safeDown()
    {
        $map = call_user_func([$this,'map']);
        foreach ($map as $key => $val){
            $arrField = call_user_func([$this,$val]);
            foreach ($arrField as $v){
                $this->delete($this->tableName,['table_name'=>$key]);
            }
        }

        return true;
    }

    /**
    * @purpose: 定义表和表字段方法的对应关系
    * @return array
    */
    public function map()
    {
        return [
            //用户表
            'user' => 'defineUserTable',
            //部门表
            'dept' => 'defineDeptTable',
        ];
    }

    /**
     * @purpose: 合并默认值和私有值
     * @param string $table 表明
     * @param array $data 各个字段的私有值
     * @return array $data 合并后的数组
     */
    public function migrateSchema($table,$data)
    {
        $default = [
            'company_id' => COMPANY_ID,     //企业编号
            'branch_id' => BRANCH_ID,         //网点编号
            'belong_module' => 'organize',    //所属模块, ticket:工单 crm:客户资料
            'table_name' => $table,            //字段所属表名
            'table_type' => '',                //所属类型(如工单的业务类型)
            'field_name' => '',                //字段名
            'field_label' => '',            //字段标签
            'field_type' => 'cf',            //字段类型  fixed:固定字段 cf:自定义字段
            'extra_fields' => '',            //固定字段输出时需额外获取的字段
            'input_type' => 'text',            //输入控件类型
            'input_options' => '',            //输入控件的选项值，以json格式组织
            'default_value' => '',            //字段默认值
            'max_length' => 100,            //最大长度
            'is_enabled' => 1,                //是否启用
            'is_required' => 0,                //是否必填
            'is_uniqued' => 0,                //是否唯一
            'sequence' => 0,                //排序
            'visible_set' => '',            //可见设置，以逗号分隔的集合(new,edit,detail,search,list,export)
            'visible_set_default'=>'',        //默认显示值是否可修改
            'is_edit_readonly'=>0,            //是否编辑页只读
            'is_add_readonly'=>0,            //是否添加页只读
            'created_by' => 'sys',            //创建人工号
            'created_by_name' => '',        //创建人姓名
            'created_at' => time(),            //创建时间
            'updated_by' => 'sys',            //更新人
            'updated_by_name' => '',        //更新人姓名
            'updated_at' => time(),            //更新时间
        ];
        foreach($data as $key => $val){
            $data[$key] = yiihelpersArrayHelper::merge($default,$val);
        }
        return $data;
    }

    /**
     * @purpose: 定义用户字段
     * @return array
     */
    public function defineUserTable()
    {
        return [
            [
                'belong_module' => 'fusionpbx',
                'table_name' => 'user',
                'field_name' => 'id',
                'field_label' => '主键ID',
                'field_type' => 'fixed',
                'input_type' => 'number',
                'extra_fields' => '',
                'max_length' => 100,
                'is_enabled' => 1,
                'is_required' => 1,
                'is_uniqued' => 1,
                'sequence' => 1,
                'visible_set' => '',
                'visible_set_default' => '{"new": {"disabled": true},"edit": {"disabled": true},"detail": {"disabled": true},"is_enabled": {"disabled": true},"is_uniqued":{"disabled": true}, "is_required": {"disabled": true}}',
                'is_edit_readonly' => 0,
                'is_add_readonly' => 0,
            ],
            [
                'belong_module' => 'fusionpbx',
                'table_name' => 'user',
                'field_name' => 'user_name',
                'field_label' => '姓名',
                'field_type' => 'fixed',
                'input_type' => 'text',
                'extra_fields' => '',
                'max_length' => 100,
                'is_enabled' => 1,
                'is_required' => 1,
                'is_uniqued' => 0,
                'sequence' => 2,
                'visible_set' => 'new,edit,detail,search,list,export',
                'visible_set_default' => '{"new": {"disabled": true},"edit": {"disabled": true},"detail": {"disabled": true},"is_enabled": {"disabled": true},"is_uniqued":{"disabled": true}, "is_required": {"disabled": true}}',
                'is_edit_readonly' => 0,
                'is_add_readonly' => 0,
            ],
            //可以继续添加更多字段
        ];
    }

    /**
     * @purpose: 定义部门字段
     * @return array
     */
    public function defineDeptTable()
    {
            return [
            [
                'belong_module' => 'fusionpbx',
                'table_name' => 'dept',
                'field_name' => 'id',
                'field_label' => '主键ID',
                'field_type' => 'fixed',
                'input_type' => 'number',
                'extra_fields' => '',
                'max_length' => 100,
                'is_enabled' => 1,
                'is_required' => 1,
                'is_uniqued' => 1,
                'sequence' => 1,
                'visible_set' => '',
                'visible_set_default' => '{"new": {"disabled": true},"edit": {"disabled": true},"detail": {"disabled": true},"is_enabled": {"disabled": true},"is_uniqued":{"disabled": true}, "is_required": {"disabled": true}}',
                'is_edit_readonly' => 0,
                'is_add_readonly' => 0,
            ],
            [
                'belong_module' => 'fusionpbx',
                'table_name' => 'dept',
                'field_name' => 'dept_name',
                'field_label' => '部门名称',
                'field_type' => 'fixed',
                'input_type' => 'text',
                'extra_fields' => '',
                'max_length' => 100,
                'is_enabled' => 1,
                'is_required' => 1,
                'is_uniqued' => 0,
                'sequence' => 2,
                'visible_set' => 'new,edit,detail,search,list,export',
                'visible_set_default' => '{"new": {"disabled": true},"edit": {"disabled": true},"detail": {"disabled": true},"is_enabled": {"disabled": true},"is_uniqued":{"disabled": true}, "is_required": {"disabled": true}}',
                'is_edit_readonly' => 0,
                'is_add_readonly' => 0,
            ],
            //可以继续添加更多字段
        ];
    }
}

本文为袋鼠学习中的总结，如有转载请注明出处:https://www.cnblogs.com/chrdai/p/8477269.html

 可先参看博友的博文：https://segmentfault.com/a/1190000005599416