数据库迁移工具robmorgan/phinx最全使用文档及教程

数据库迁移工具phinx，本篇涵盖了网上最全的使用教程，看完教程能够包含到数据库迁移中经常使用的全部功能，希望对你有所帮助。喜欢的话请关注我们网站micuer.com，米醋儿点康母

robmorgan/phinx
官方文档：https://book.cakephp.org
 1.安装

```
 composer require "robmorgan/phinx
```

2.执行

```
php vendor/bin/phinx
```

```
直接运行 `php vendor/bin/phinx init` 可生成配置文件
```

另外一种方法是直接使用php文件做配置文件

使用`phinx.php`进行配置

```php
<?php
use  MillionMile\GetEnv\Env;

return
[
    'paths' => [
        'migrations' => '%%PHINX_CONFIG_DIR%%/db/migrations',
        'seeds' => '%%PHINX_CONFIG_DIR%%/db/seeds'
    ],
    'environments' => [
        'default_migration_table' => 'byd_migration',
        'default_environment' => 'development',
        'production' => [
            'adapter' => 'mysql',
            'host' =>  Env::get('DATABASE.HOST'),
            'name' => Env::get('DATABASE.DATABASE'),
            'user' => Env::get('DATABASE.USERNAME'),
            'pass' => Env::get('DATABASE.PASSWORD'),
            'port' => Env::get('DATABASE.PORT'),
            'charset' => 'UTF8'
        ],
        'development' => [
            'adapter' => 'mysql',
            'host' =>  Env::get('DATABASE.HOST'),
            'name' => Env::get('DATABASE.DATABASE'),
            'user' => Env::get('DATABASE.USERNAME'),
            'pass' => Env::get('DATABASE.PASSWORD'),
            'port' => Env::get('DATABASE.PORT'),
            'charset' => 'UTF8'
        ],
        'testing' => [
            'adapter' => 'mysql',
            'host' =>  Env::get('DATABASE.HOST'),
            'name' => Env::get('DATABASE.DATABASE'),
            'user' => Env::get('DATABASE.USERNAME'),
            'pass' => Env::get('DATABASE.PASSWORD'),
            'port' => Env::get('DATABASE.PORT'),
            'charset' => 'UTF8'
        ]
    ],
    'version_order' => 'creation'
];
```

执行 `php vendor/bin/phinx status`查看连接状态

执行 `php vendor/bin/phinx create Migration`

现在生成了`created /db/migrations/20180310020523_migration.php`

编辑这个文件,添加数据库创建内容

```php
public function change()
{
    $user = $this->table('user');
    $user->addColumn('open_id', 'string', ['limit'=>64]);
    $user->addColumn('register_time', 'timestamp', ['default' => 'CURRENT_TIMESTAMP']);
    $user->addColumn('favorite_music', 'integer', ['default'=> 0, 'comment'=>'喜欢的音乐']);
    $user->addColumn('favorite_vedio', 'integer', ['default'=> 0, 'comment'=>'喜欢的视频数']);
    $user->addColumn('favorite_article', 'integer', ['default'=> 0, 'comment'=>'喜欢的文章数']);
    $user->addColumn('baby_birthday', 'date', ['null'=>true, 'comment'=>'宝宝生日']);
    $user->addColumn('email', 'string', ['limit' => 100])
    $user->addColumn('baby_sex', 'boolean', ['null'=>true, 'comment'=>'宝宝性别']);
    $user->addColumn('last_login', 'datetime', ['null'=>true, 'comment'=>'最后登陆日期']);
    $user->addIndex(['username', 'email'], ['unique' => true])
    $user->save();
}
```

默认会添加一个自增id,作为主键

执行 `php vendor/bin/phinx migrate`

| Option     | Description                                                  |
| ---------- | ------------------------------------------------------------ |
| comment    | set a text comment on the table 注释                         |
| row_format | set the table row format                                     |
| engine     | define table engine *(defaults to ``InnoDB``)*               |
| collation  | define table collation *(defaults to ``utf8_general_ci``)*   |
| signed     | whether the primary key is `signed`  *(defaults to ``true``)* |
| limit      | set the maximum length for the primary key 设置最小最大值    |

## 初始化数据

执行 `php vendor/bin/phinx seed:create CategorySeeder`
 系统自动创建 created ./db/seeds/CategorySeeder.php
 修改 CategorySeeder.php

执行 `php vendor/bin/phinx seed:run` 将会进行所有Seed
 如果想运行指定的Seed需要用`- s`参数指定
 `php vendor/bin/phinx seed:run -s CategorySeeder`

## 更新表结构

当需要更新表结构的时候,需要再创建一个migrate
执行`php vendor/bin/phinx create ChangeArtist`
再将需要更新的内容写到`change`函数

```php
案例1
public function change()
{
    $resource = $this->table('resource');
    $resource->addColumn('artist', 'string', ['limit'=>128, 'default'=>'']);
    $resource->update();
}
```

```php
案例2
public function change()
{
    // create the table
    $table = $this->table('user_logins');
    $table->addColumn('user_id', 'integer')
        ->addColumn('created', 'datetime')
        ->create();
}
```

最后执行`php vendor/bin/phinx migrate`
之前的已经执行过的migrate不会执行, 只会执行更新的部分

## 可选的格式

- binary
- boolean
- char
- date
- datetime
- decimal
- float
- double
- smallinteger
- integer
- biginteger
- string
- text
- time
- timestamp
- uuid

For any column type:

| Option  | Description                                                  |
| ------- | ------------------------------------------------------------ |
| limit   | set maximum length for strings, also hints column types in adapters (see note below) |
| length  | alias for `limit`                                            |
| default | set default value or action                                  |
| null    | allow `NULL` values, defaults to false (should not be used with primary keys!) (see note below) |
| after   | specify the column that a new column should be placed after, or use `\Phinx\Db\Adapter\MysqlAdapter::FIRST` to place the column at the start of the table *(only applies to MySQL)* |
| comment | set a text comment on the column                             |

For `decimal` columns:

| Option    | Description                                                  |
| --------- | ------------------------------------------------------------ |
| precision | combine with `scale` set to set decimal accuracy             |
| scale     | combine with `precision` to set decimal accuracy             |
| signed    | enable or disable the `unsigned` option *(only applies to MySQL)* |

For `enum` and `set` columns:

| Option | Description                                         |
| ------ | --------------------------------------------------- |
| values | Can be a comma separated list or an array of values |

For `integer` and `biginteger` columns:

| Option   | Description                                                  |
| -------- | ------------------------------------------------------------ |
| identity | enable or disable automatic incrementing                     |
| signed   | enable or disable the `unsigned` option *(only applies to MySQL)* |

For `timestamp` columns:

| Option   | Description                                                  |
| -------- | ------------------------------------------------------------ |
| default  | set default value (use with `CURRENT_TIMESTAMP`)             |
| update   | set an action to be triggered when the row is updated (use with `CURRENT_TIMESTAMP`) *(only applies to MySQL)* |
| timezone | enable or disable the `with time zone` option for `time` and `timestamp` columns *(only applies to Postgres)* |

## 执行SQL语句

执行SQL可以使用execute()和query()，execute()返回的是影响的行，query()返回的是pdo链接

```php
<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        // execute()
        $count = $this->execute('DELETE FROM users'); // returns the number of affected rows

// query()
        $stmt = $this->query('SELECT * FROM users'); // returns PDOStatement
        $rows = $stmt->fetchAll(); // returns the result as an array
    }

/**
     * Migrate Down.
     */
    public function down()
    {

}
}
```

## 添加数据

```php
<?php

use Phinx\Migration\AbstractMigration;

class NewStatus extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('status');

// inserting only one row
        $singleRow = [
            'id'    => 1,
            'name'  => 'In Progress'
        ];

$table->insert($singleRow)->saveData();

// inserting multiple rows
        $rows = [
            [
              'id'    => 2,
              'name'  => 'Stopped'
            ],
            [
              'id'    => 3,
              'name'  => 'Queued'
            ]
        ];

$table->insert($rows)->saveData();
    }

/**
     * Migrate Down.
     */
    public function down()
    {
        $this->execute('DELETE FROM status');
    }
}
```

## 表存在则执行

```php
<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $exists = $this->hasTable('users');
        if ($exists) {
            // do something
        }
    }

/**
     * Migrate Down.
     */
    public function down()
    {

}
}
```

## 删除表

```php
<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $this->table('users')->drop()->save();
    }

/**
     * Migrate Down.
     */
    public function down()
    {
        $users = $this->table('users');
        $users->addColumn('username', 'string', ['limit' => 20])
              ->addColumn('password', 'string', ['limit' => 40])
              ->addColumn('password_salt', 'string', ['limit' => 40])
              ->addColumn('email', 'string', ['limit' => 100])
              ->addColumn('first_name', 'string', ['limit' => 30])
              ->addColumn('last_name', 'string', ['limit' => 30])
              ->addColumn('created', 'datetime')
              ->addColumn('updated', 'datetime', ['null' => true])
              ->addIndex(['username', 'email'], ['unique' => true])
              ->save();
    }
}
```

## 重命名表

```php
<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $table = $this->table('users');
        $table
            ->rename('legacy_users')
            ->update();
    }

/**
     * Migrate Down.
     */
    public function down()
    {
        $table = $this->table('legacy_users');
        $table
            ->rename('users')
            ->update();
    }
}
```

## 修改主键

```php
<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $users = $this->table('users');
        $users
            ->addColumn('username', 'string', ['limit' => 20, 'null' => false])
            ->addColumn('password', 'string', ['limit' => 40])
            ->save();

$users
            ->addColumn('new_id', 'integer', ['null' => false])
            ->changePrimaryKey(['new_id', 'username'])
            ->save();
    }

/**
     * Migrate Down.
     */
    public function down()
    {

}
}
```

## 修改表注释

```php
<?php

use Phinx\Migration\AbstractMigration;

class MyNewMigration extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $users = $this->table('users');
        $users
            ->addColumn('username', 'string', ['limit' => 20])
            ->addColumn('password', 'string', ['limit' => 40])
            ->save();

$users
            ->changeComment('This is the table with users auth information, password should be encrypted')
            ->save();
    }

/**
     * Migrate Down.
     */
    public function down()
    {

}
}
```

## 三注意事项：

1 change 方法中创建或者更新表的时候你必须使用 create() 或者 update() 方法；
   up方法中使用 save() 
2 不能在 change() 方法中使用插入数据，只能在 up() 和 down() 中使用

```
php vendor/bin/phinx migrate  -e product // -e  跟环境变量
```

## 说明

以上操作基于Linux操作系统。
在windows中使用phinx.bat文件

```tsx
D:\workspace_ued\TelegramPostBot (master -> origin)
λ vendor\bin\phinx.bat
Phinx by CakePHP - https://phinx.org. 0.10.6

Usage:
  command [options] [arguments]

Options:
  -h, --help            Display this help message
  -q, --quiet           Do not output any message
  -V, --version         Display this application version
      --ansi            Force ANSI output
      --no-ansi         Disable ANSI output
  -n, --no-interaction  Do not ask any interactive question
  -v|vv|vvv, --verbose  Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug

Available commands:
  breakpoint   Manage breakpoints
  create       Create a new migration
  help         Displays help for a command
  init         Initialize the application for Phinx
  list         Lists commands
  migrate      Migrate the database
  rollback     Rollback the last or to a specific migration
  status       Show migration status
  test         Verify the configuration file
 seed
  seed:create  Create a new database seeder
  seed:run     Run database seeders
```

注:原创不易,转载请注明出处( https://micuu.com/new/2726.html )，本站所有资源来源于网络收集，如有侵权请联系QQ245557979进行清除。