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 yii\db\Migration;
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 yii\db\Migration;
/**
* @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] = \yii\helpers\ArrayHelper::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
