This article describes the method of yii2.0 database migration. Share it with everyone for your reference, the details are as follows:

Create a migration

Use the following command to create a new migration:

yii migrate/create <name>

The role of the required parameter name is to give a brief description of the new migration. For example, if this migration is used to add fields to the same table in multiple databases (assuming each database has a news table), then you can use the name addColumn_news (the name is customized) and run the following command:

yii migrate/create addColumn_news

Note: Because the name parameter will be used to generate part of the migrated class name, this parameter should only contain letters, numbers, and underscores.

The above command will create a new PHP class file named m150101_185401_addColumn_news.php in the @app/migrations directory. This file contains the following code, which is used to declare a migration class m150101_185401_addColumn_news, with a code skeleton:

<?php
use yii\db\Schema;
use yii\db\Migration;
class m150101_185401_addColumn_news extends Migration
{
//createDbs 該方法是獲取數(shù)據(jù)庫對象返回
private function createDbs(){
  $dbs = [];
  $dbs_info =\Yii::$app->params[&#39;db&#39;];
  foreach($dbs_info as $k=>$v){
    $dbs[$k] = \Yii::createObject($v);
  }
  return $dbs;
}
//up() 該方法是往不同的數(shù)據(jù)庫的news表添加 name,nickname,age,sex,site_id等字段
public function up()
{
  $dbs = $this->createDbs();
  foreach($dbs as $v){   //《------遍歷講字段同時添加到不同的數(shù)據(jù)庫中
    $this->db=$v;
    $this->addColumn(&#39;{{%news}}&#39;,&#39;name&#39;,&#39;varchar(20)&#39;);
    $this->addColumn(&#39;{{%news}}&#39;,&#39;nickname&#39;,&#39;varchar(20)&#39;);
    $this->addColumn(&#39;{{%news}}&#39;,&#39;age&#39;,&#39;int(3)&#39;);
    $this->addColumn(&#39;{{%news}}&#39;,&#39;sex&#39;,&#39;int(1)&#39;);
    $this->addColumn(&#39;{{%news}}&#39;,&#39;site_id&#39;,&#39;int(5)&#39;);
  }
}
//down()  該方法與up()方法相反,是刪除字段的意思
public function down()
{
  $dbs = $this->createDbs();
  foreach($dbs as $v){
    $this->db=$v;
    $this->dropColumn(&#39;{{%news}}&#39;,&#39;name&#39;,&#39;varchar(20)&#39;);
    $this->dropColumn(&#39;{{%news}}&#39;,&#39;nickname&#39;,&#39;varchar(20)&#39;);
    $this->dropColumn(&#39;{{%news}}&#39;,&#39;age&#39;,&#39;int(3)&#39;);
    $this->dropColumn(&#39;{{%news}}&#39;,&#39;sex&#39;,&#39;int(1)&#39;);
    $this->dropColumn(&#39;{{%news}}&#39;,&#39;site_id&#39;,&#39;int(5)&#39;);
  }
}
}

Each database migration will be defined as a PHP class inherited from yiidbMigration. The name of the class is automatically generated in the format of m_, where

refers to the UTC time when the create migration command is executed.

is the same as the name parameter value when you execute the command.

In the migration class, you should write the code to change the database structure in the up() method. You may also need to write code in the down() method to revert the changes made by the up() method. When you upgrade the database via migration, the up() method will be called, and vice versa, down() will be called. The following code shows how to create a news table through the migration class:

use yii\db\Schema;
use yii\db\Migration;
class m150101_185401_create_news_table extends \yii\db\Migration
{
  public function up()
  {
    $this->createTable('news', [
      'id' => Schema::TYPE_PK,
      'title' => Schema::TYPE_STRING . ' NOT NULL',
      'content' => Schema::TYPE_TEXT,
    ]);
  }
  public function down()
  {
    $this->dropTable('news');
  }
}

Note: Not all migrations are reversible. For example, if the up() method deletes a row of data in the table, this data cannot be restored through the down() method. Sometimes, you might just be too lazy to execute the down() method because it's not that versatile for reverting database migrations. In this case, you should return false in the down() method to indicate that the migration is irreversible.

Methods to access the database

The migration base class yiidbMigration provides a complete set of methods to access and operate the database. You may find that the naming of these methods is similar to the DAO methods provided by the yiidbCommand class. For example, the yiidbMigration::createTable() method can create a new table, which has the same function as yiidbCommand::createTable().

The advantage of using the methods provided by yiidbMigration is that you no longer need to explicitly create yiidbCommand instances, and some useful information will be displayed when executing each method to tell us whether the database operations have been completed and what they are. How long it took to complete these operations, etc.

The following is a list of all these database access methods:

yiidbMigration::execute(): Execute a SQL statement
yiidbMigration::insert(): Insert a single row of data
yiidbMigration::batchInsert(): Insert multiple rows of data
yiidbMigration ::update(): Update data
yiidbMigration::delete(): Delete data
yiidbMigration::createTable(): Create table
yiidbMigration::renameTable(): Rename table name
yiidbMigration::dropTable(): Delete one Table
yiidbMigration::truncateTable(): Clear all data in the table
yiidbMigration::addColumn(): Add a field
yiidbMigration::renameColumn(): Rename a field name
yiidbMigration::dropColumn(): Delete a field
yiidbMigration::alterColumn(): Modify a field
yiidbMigration::addPrimaryKey(): Add a primary key
yiidbMigration::dropPrimaryKey(): Delete a primary key
yiidbMigration::addForeignKey(): Add a foreign key
yiidbMigration::dropForeignKey( ): Delete a foreign key
yiidbMigration::createIndex(): Create an index
yiidbMigration::dropIndex(): Delete an index

Submit migration

In order to upgrade the database to the latest structure, you should use the following command to submit All new migrations:

yii migrate

This command will list all uncommitted migrations so far. If you determine that you need to submit these migrations, it will run the up() or safeUp() method in each new migration class one after another in the order of the timestamp in the class name. If any of the migrations fails to commit, this command will exit and stop the remaining migrations that have not yet been executed.

For each successfully submitted migration, this command will insert a record containing the application's successfully submitted migration in a database table called migration. This record will help the migration tool determine which migrations have been submitted and which have not been submitted.

Tips: The migration tool will automatically create a migration table in the database specified in the yiiconsolecontrollersMigrateController::db option of the command. By default, it is specified by the db application component.

有時，你可能只需要提交一個或者少數(shù)的幾個遷移，你可以使用該命令指定需要執(zhí)行的條數(shù)，而不是執(zhí)行所有的可用遷移。例如，如下命令將會嘗試提交前三個可用的遷移：

yii migrate 3

你也可以指定一個特定的遷移，按照如下格式使用 migrate/to 命令來指定數(shù)據(jù)庫應(yīng)該提交哪一個遷移：

yii migrate/to 150101_185401           # using timestamp to specify the migration 使用時間戳來指定遷移
yii migrate/to "2015-01-01 18:54:01"       # using a string that can be parsed by strtotime() 使用一個可以被 strtotime() 解析的字符串
yii migrate/to m150101_185401_create_news_table  # using full name 使用全名
yii migrate/to 1392853618             # using UNIX timestamp 使用 UNIX 時間戳

如果在指定要提交的遷移前面還有未提交的遷移，那么在執(zhí)行這個被指定的遷移之前，這些還未提交的遷移會先被提交。

如果被指定提交的遷移在之前已經(jīng)被提交過，那么在其之后的那些遷移將會被還原。

還原遷移

你可以使用如下命令來還原其中一個或多個意見被提交過的遷移：

yii migrate/down   # revert the most recently applied migration 還原最近一次提交的遷移
yii migrate/down 3  # revert the most 3 recently applied migrations 還原最近三次提交的遷移

注意：并不是所有的遷移都能被還原。嘗試還原這類遷移將可能導致報錯甚至是終止所有的還原進程。

重做遷移

重做遷移的意思是先還原指定的遷移，然后再次提交。如下所示：

yii migrate/redo    # redo the last applied migration 重做最近一次提交的遷移
yii migrate/redo 3   # redo the last 3 applied migrations 重做最近三次提交的遷移

注意：如果一個遷移是不能被還原的，那么你將無法對它進行重做。

列出遷移

你可以使用如下命令列出那些提交了的或者是還未提交的遷移：

yii migrate/history   # 顯示最近10次提交的遷移
yii migrate/history 5  # 顯示最近5次提交的遷移
yii migrate/history all # 顯示所有已經(jīng)提交過的遷移
yii migrate/new     # 顯示前10個還未提交的遷移
yii migrate/new 5    # 顯示前5個還未提交的遷移
yii migrate/new all   # 顯示所有還未提交的遷移

修改遷移歷史

有時候你也許需要簡單的標記一下你的數(shù)據(jù)庫已經(jīng)升級到一個特定的遷移，而不是實際提交或者是還原遷移。這個經(jīng)常會發(fā)生在你手動的改變數(shù)據(jù)庫的一個特定狀態(tài)，而又不想相應(yīng)的遷移被重復提交。那么你可以使用如下命令來達到目的：

yii migrate/mark 150101_185401           # 使用時間戳來指定遷移
yii migrate/mark "2015-01-01 18:54:01"       # 使用一個可以被 strtotime() 解析的字符串
yii migrate/mark m150101_185401_create_news_table  # 使用全名
yii migrate/mark 1392853618             # 使用 UNIX 時間戳

該命令將會添加或者刪除 migration 表當中的某幾行數(shù)據(jù)來表明數(shù)據(jù)庫已經(jīng)提交到了指定的某個遷移上。執(zhí)行這條命令期間不會有任何的遷移會被提交或還原。

自定義遷移

有很多方法可以自定義遷移命令。

使用命令行選項

遷移命令附帶了幾個命令行選項，可以用來自定義它的行為：

interactive: boolean (默認值為 true)，指定是否以交互模式來運行遷移。當被設(shè)置為 true 時，在命令執(zhí)行某些操作前，會提示用戶。如果你希望在后臺執(zhí)行該命令，那么你應(yīng)該把它設(shè)置成 false。

migrationPath: string (默認值為 @app/migrations)，指定存放所有遷移類文件的目錄。該選項可以是一個目錄的路徑，也可以是 路徑別名。需要注意的是指定的目錄必選存在，否則將會觸發(fā)一個錯誤。

migrationTable: string (默認值為 migration)，指定用于存儲遷移歷史信息的數(shù)據(jù)庫表名稱。如果這張表不存在，那么遷移命令將自動創(chuàng)建這張表。當然你也可以使用這樣的字段結(jié)構(gòu)： version
varchar(255) primary key, apply_time integer 來手動創(chuàng)建這張表。

db: string (默認值為 db)，指定數(shù)據(jù)庫 application component 的 ID。它指的是將會被該命令遷移的數(shù)據(jù)庫。

templateFile: string (defaults to @yii/views/migration.php)，指定生產(chǎn)遷移框架代碼類文件的模版文件路徑。該選項即可以使用文件路徑來指定，也可以使用路徑 別名 來指定。該模版文件是一個可以使用預(yù)定義變量 $className 來獲取遷移類名稱的 PHP 腳本。

如下例子向我們展示了如何使用這些選項：

例如，如果我們需要遷移一個 forum 模塊，而該遷移文件放在該模塊下的 migrations 目錄當中，那么我們可以使用如下命令：

# 在 forum 模塊中以非交互模式進行遷移
yii migrate --migrationPath=@app/modules/forum/migrations --interactive=0

全局配置命令

在運行遷移命令的時候每次都要重復的輸入一些同樣的參數(shù)會很煩人，這時候，你可以選擇在應(yīng)用程序配置當中進行全局配置，一勞永逸：

return [
  'controllerMap' => [
    'migrate' => [
      'class' => 'yii\console\controllers\MigrateController',
      'migrationTable' => 'backend_migration',
    ],
  ],
];

如上所示配置，在每次運行遷移命令的時候，backend_migration 表將會被用來記錄遷移歷史。你再也不需要通過 migrationTable 命令行參數(shù)來指定這張歷史紀錄表了。

遷移多個數(shù)據(jù)庫

默認情況下，遷移將會提交到由 db application component 所定義的同一個數(shù)據(jù)庫當中。如果你需要提交到不同的數(shù)據(jù)庫，你可以像下面那樣指定 db 命令行選項，

yii migrate --db=db2

上面的命令將會把遷移提交到 db2 數(shù)據(jù)庫當中。

偶爾有限時候你需要提交 一些 遷移到一個數(shù)據(jù)庫，而另外一些則提交到另一個數(shù)據(jù)庫。為了達到這個目的，你應(yīng)該在實現(xiàn)一個遷移類的時候指定需要用到的數(shù)據(jù)庫組件的 ID ， 如下所示：

use yii\db\Schema;
use yii\db\Migration;
class m150101_185401_create_news_table extends Migration
{
  public function init()
  {
    $this->db = 'db2';
    parent::init();
  }
}

即使你使用 db 命令行選項指定了另外一個不同的數(shù)據(jù)庫，上面的遷移還是會被提交到 db2 當中。需要注意的是這個時候遷移的歷史信息依然會被記錄到 db 命令行選項所指定的數(shù)據(jù)庫當中。

如果有多個遷移都使用到了同一個數(shù)據(jù)庫，那么建議你創(chuàng)建一個遷移的基類，里面包含上述的 init() 代碼。然后每個遷移類都繼承這個基類就可以了。

提示：除了在 yii\db\Migration::db 參數(shù)當中進行設(shè)置以外，你還可以通過在遷移類中創(chuàng)建新的數(shù)據(jù)庫連接來操作不同的數(shù)據(jù)庫。然后通過這些連接再使用 DAO 方法 來操作不同的數(shù)據(jù)庫。

另外一個可以讓你遷移多個數(shù)據(jù)庫的策略是把遷移存放到不同的目錄下，然后你可以通過如下命令分別對不同的數(shù)據(jù)庫進行遷移：

yii migrate --migrationPath=@app/migrations/db1 --db=db1
yii migrate --migrationPath=@app/migrations/db2 --db=db2
...

? ?

第一條命令將會把 @app/migrations/db1 目錄下的遷移提交到 db1 數(shù)據(jù)庫當中，第二條命令則會把 @app/migrations/db2 下的遷移提交到 db2 數(shù)據(jù)庫當中，以此類推。

希望本文所述對大家基于Yii框架的PHP程序設(shè)計有所幫助。

更多yii2.0數(shù)據(jù)庫遷移教程相關(guān)文章請關(guān)注PHP中文網(wǎng)！