README
数据库数据迁移工具。重构数据库后,将旧数据迁移至新库时使用。
This tool would be useful if you want to carry the history data after rebuild database.
目录
安装
安装:composer require cherrylu/database-transportor
然后在config\app.php
的providers
中添加CherryLu\DatabaseTransportor\DatabaseTransportorServiceProvider::class
命令
创建迁移文件命令:php artisan make:transportor 文件名(如UserTransportor)
该命令将会在database\seeders\transportors
路径下创建对应文件,在初始使用时也会同时创建Transportor.php
文件
执行迁移命令:php artisan transport [--class=]
未指定迁移文件时,将会迁移Transportor
中handle
方法传入的类
使用
1.基础迁移
1.1一对一迁移
旧表 old_users |
|
新表 new_users |
|
=> |
|
$maps = [
// new_users_map 默认的新表名称
"new_users_map" => [
// 指定的新表名称,若没有设定target_table的值,会认为该数组的键名'new_users_map'为新表的表名
"target_table" => "new_users",
// 旧表名称,即数据来源表的表名
"original_table" => "old_users",
// 新旧表字段映射关系 '新表字段' => '旧表字段'
"columns" => [
"id" => "id",
"username" => "name",
],
]
];
// database.php 中设的 connections 设定
$old_database = "pgsql";
$new_database = "mysql";
$transportor = new \cherrylu\transportor\DBT($maps, $new_database, $old_database);
$transportor->setChunk(5000);// 设定每次迁移的数据量 若不设置默认为2000
$transportor->doTransport();// 执行迁移
1.2带默认值的迁移
旧表 old_users |
|
新表 new_users |
|
=> |
id | username | created_at |
---|
1 | 张三 | now() | 2 | 李四 | now() |
|
$maps = [
"new_users" => [
"original_table" => "old_users",
"columns" => [
"id" => "id",
"username" => "name",
"created_at" => ["default" => \Carbon\Carbon::now()]
],
]
];
当旧表字段查询结果为NULL
,或处理后结果为NULL
时将填入 default
设定的值
1.3迁移前的预处理
旧表 old_users |
|
新表 new_users |
|
=> |
|
$maps = [
"new_users" => [
"original_table" => "old_users",
"columns" => [
"id" => "id",
"username" => [
"original" => "name",
"function" => function ($data) {
return $data->name . "-" .$data->id;
}
]
],
]
];
1.4带查询条件的迁移
旧表 old_users |
|
新表 new_users |
|
=> |
|
$maps = [
"new_users" => [
"original_table" => "old_users",
"extra_conditions" => [
["name", "<>", "王五"],
// or 定义为字符串时,即执行原生的查询
" `name` <> '王五' ",
],
"columns" => [
"id" => "id",
"username" => "name",
],
]
];
该工具类所有查询操作符如下:
case "="
case ">"
case "<"
case "<>"
case "!="
case "like"
case "notlike"
case "notin"
case "in"
case "between"
case "notbetween"
2.引用迁移
2.1 单引用迁移 - refer
新表 new_roles:
旧表 old_users |
|
新表 new_users |
id | name | role_name |
---|
1 | 张三 | 管理员 | 2 | 李四 | 用户 | 3 | 王五 | 黑户 |
|
=> |
id | name | role_id |
---|
1 | 张三 | 1 | 2 | 李四 | 2 | 3 | 王五 | 0 |
|
$maps = [
"new_users" => [
"original_table" => "old_users",
"columns" => [
"id" => "id",
"username" => "name",
"temp_role_name" => [
"original" => "role_name",
"delete_after_transport" => true,
"rebuild" => true,
],
"role_id" => [
"refer" => [ // 单引用
"search_source" => "target", // 如果数据源为旧表时,用original即可
"search_table" => "new_roles",
"search_column" => "role_name",
"according_column" => "temp_role_name",
"wanted_column" => "id",
// 未定义此项时,直接迁移 id 的原值。$data是固定格式,为 "wanted_column" 定义的对应引用字段,此处为 id
"pre_format" => function ($data) {
// 查询前去除前后的无关字符
return trim($data->role_name);
},
]
],
"default" => 0,
],
]
];
2.2 单引用迁移-引用多字段 - refer-mulitfield
旧表 records:
id | created_at | amount |
---|
132 | 2020-01-12 | 3000 |
322 | 2020-01-15 | 3100 |
旧表 old_pay |
|
新表 new_pay |
id | total | create_date |
---|
1 | 3000 | 2020-01-12 | 2 | 3100 | 2020-01-15 |
|
=> |
|
$maps = [
"new_users" => [
"original_table" => "old_users",
"columns" => [
"id" => "id",
"temp_total" => [
"original" => "total",
"delete_after_transport" => true,
"rebuild" => true,
],
"temp_create_date" => [
"original" => "create_date",
"delete_after_transport" => true,
"rebuild" => true,
],
"role_id" => [
"refer" => [ // 单引用
"according_column" => ["temp_total", "temp_create_date"],
"search_source" => "original",
"search_table" => "records",
"search_column" => ["amount", "created_at"],
"wanted_column" => "bill_id",
]
],
"default" => 0,
],
]
];
2.3 多引用迁移 - refers
旧表 accounts:
id |
user_id |
fee_type_id |
amount |
1 |
1 |
1 |
100 |
2 |
1 |
2 |
200 |
旧表 old_users |
|
新表 new_users |
|
=> |
|
$maps = [
"new_users" => [
"original_table" => "old_users",
"columns" => [
"id" => "id",
"username" => "name",
"amount" => [
"refers" => [
"according_column" => "id",
"search_source" => "original",
"search_table" => "accounts",
"search_column" => "user_id",
"processor" => function ($data) {
$amount = 0;
foreach ( $data as $datum ) {
$amount += $datum->amount;
}
return $amount;
}
]
],
]
]
];
3.多对多迁移
新表 types
id |
type_name |
1 |
类型_1 |
2 |
类型_2 |
3 |
类型_3 |
旧表 old_materials |
|
|
|
=> |
新表 new_materials新表 material_types |
此处只给出中间表material_types
的$maps,new_materials
请参照上文
$maps = [
"material_types" => [
"original_table" => null,
"columns" => [
"material_id" => null,
"type_id" => null,
],
"middle" => [
"one" => [
"refer_table" => "new_materials",
"wanted_column" => "id",
"fill_column" => "material_id",
"according_column" => "type_temp", // 定义 new_materials 的 maps 时,将原来的 type_id 暂存为 type_temp
"pre_format" => function ($data) { return explode(',', trim($data)); }
],
"many" => [
"fill_column" => "type_id",
"refer_table" => "types",
"wanted_column" => "id",
"search_column" => "id",
"search_method" => "in",
],
],
]
];