原始sql

CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR (30) NOT NULL COMMENT '用户名',
  `password` VARCHAR (60) NOT NULL COMMENT '密码',
  `email` VARCHAR (50) NOT NULL COMMENT 'email',
  `status` TINYINT (1) NOT NULL COMMENT '状态',
  `add_time` INT NOT NULL COMMENT '添加时间',
  `update_time` INT NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = UTF8 COMMENT '用户表' ;
CREATE TABLE `user_token` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `token` VARCHAR (60) NOT NULL COMMENT 'refresh token',
  `version` CHAR(10) NOT NULL COMMENT '版本',
  `status` TINYINT (1) NOT NULL DEFAULT 0 COMMENT '状态',
  `pub_time` INT NOT NULL COMMENT '发布时间',
  `expire_time` INT NOT NULL COMMENT '过期时间',
  PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = UTF8 COMMENT 'refresh token' ;
CREATE TABLE `task` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR (50) NOT NULL COMMENT '任务标题',
  `content` TEXT NOT NULL COMMENT '任务详情',
  `job_id` INT NOT NULL DEFAULT 0 COMMENT '任务ID',
  `type` TINYINT (1) NOT NULL DEFAULT 0 COMMENT '类型（正常与延迟）',
  `status` TINYINT (1) NOT NULL DEFAULT 0 COMMENT '状态',
  `add_time` INT NOT NULL COMMENT '添加时间',
  `update_time` INT NOT NULL COMMENT '修改时间',
  `expect_time` INT NOT NULL COMMENT '预期执行时间',
  `exec_time` INT NOT NULL COMMENT '实际执行时间',
  `exec_status` TINYINT (1) NOT NULL COMMENT '执行结果',
  `exec_count` TINYINT (4) NOT NULL COMMENT '执行次数',
  `is_del` TINYINT (1) NOT NULL DEFAULT 0 COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = UTF8 COMMENT '用户任务表' ;

封装的 Migration

新建base目录，并新建 base/Migration.php 文件。

<?php
namespace app\base;
/**
 * 封装公共的 Migration
 * @author vogin
 */
class Migration extends \yii\db\Migration
{
    /**
     * 创建引擎innodb的表单
     *
     * @param string $table
     *            表名
     * @param array $columns
     *            字段
     * @param string $tableComment
     *            表名注释
     */
    public function createInnoDBTable ($table, $columns, $tableComment = '')
    {
        // 表单的内容
        $tableOptions = 'ENGINE=InnoDB DEFAULT CHARSET=utf8';
        $this->createTable($table, $columns, $tableOptions);
        // 添加表注释
        if($tableComment)
        {
            $this->addCommentOnTable($table, $tableComment);
        }
    }
}

创建三个sql的Migration

创建与改写 user 的 migration

选择 yes， 会在根目录下创建 migrations 文件夹，并在其下新增 user 的 migration 文件（名称与时间有关系，每次创建都不一样）

yii migrate/create create_user_table

<?php
use app\base\Migration;
/**
 * Handles the creation of table `user`.
 */
class m201128_014542_create_user_table extends Migration
{
    /**
     * {@inheritdoc}
     */
    public function safeUp ()
    {
        // CREATE TABLE `user` (
        // `id` INT NOT NULL AUTO_INCREMENT,
        // `username` VARCHAR (30) NOT NULL COMMENT '用户名',
        // `password` VARCHAR (60) NOT NULL COMMENT '密码',
        // `email` VARCHAR (50) NOT NULL COMMENT 'email',
        // `status` TINYINT (1) NOT NULL DEFAULT 0 COMMENT '状态',
        // `add_time` INT NOT NULL COMMENT '添加时间',
        // `update_time` INT NOT NULL COMMENT '更新时间',
        // PRIMARY KEY (`id`)
        // ) ENGINE = INNODB DEFAULT CHARSET = UTF8 COMMENT '用户表';
        $this->createInnoDBTable('{{%user}}', [
            'id' => $this->primaryKey(),
            'username' => $this->string(30)->notNull()->comment('用户名'),
            'password' => $this->string(60)->notNull()->comment('密码'),
            'email' => $this->string(50)->notNull()->comment('email'),
            'status' => $this->tinyInteger(1)->notNull()->defaultValue(0)->comment('状态'),
            'add_time' => $this->integer()->notNull()->comment('添加时间'),
            'update_time' => $this->integer()->notNull()->comment('更新时间')
        ], '用户表');
    }
    /**
     * {@inheritdoc}
     */
    public function safeDown ()
    {
        $this->dropTable('{{%user}}');
    }
}

创建与改写 user_token 的 migration

yii migrate/create create_user_token_table

<?php
use app\base\Migration;
/**
 * Handles the creation of table `user_token`.
 */
class m201128_022322_create_user_token_table extends Migration
{
    /**
     * {@inheritdoc}
     */
    public function safeUp ()
    {
        $this->createInnoDBTable('{{%user_token}}', [
            'id' => $this->primaryKey(),
            'token' => $this->string(60)->notNull()->comment('refresh token'),
            'version' => $this->char(10)->notNull()->comment('版本标识'),
            'status' => $this->tinyInteger(1)->notNull()->defaultValue(0)->comment('状态'),
      'pub_time' => $this->integer()->notNull()->comment('发布时间'),
            'expire_time' => $this->integer()->notNull()->comment('过期时间')
        ], '用户token表');
    }
    /**
     * {@inheritdoc}
     */
    public function safeDown ()
    {
        $this->dropTable('{{%user_token}}');
    }
}

创建与改写 task 的 migration

yii migrate/create create_task_table

<?php
use app\base\Migration;
/**
 * Handles the creation of table `task`.
 */
class m201128_022828_create_task_table extends Migration
{
    //CREATE TABLE `task` (
    //`id` INT NOT NULL AUTO_INCREMENT,
    //`title` VARCHAR (50) NOT NULL COMMENT '任务标题',
    //`content` TEXT NOT NULL COMMENT '任务详情',
    //`job_id` INT NOT NULL DEFAULT 0 COMMENT '任务ID',
    //`type` TINYINT (1) NOT NULL DEFAULT 0 COMMENT '类型（正常与延迟）',
    //`status` TINYINT (1) NOT NULL DEFAULT 0 COMMENT '状态',
    //`add_time` INT NOT NULL COMMENT '添加时间',
    //`update_time` INT NOT NULL COMMENT '修改时间',
    //`expect_time` INT NOT NULL COMMENT '预期执行时间',
    //`exec_time` INT NOT NULL COMMENT '实际执行时间',
    //`exec_status` TINYINT (1) NOT NULL COMMENT '执行结果',
    //`exec_count` TINYINT (4) NOT NULL COMMENT '执行次数',
    //`is_del` TINYINT (1) NOT NULL DEFAULT 0 COMMENT '是否删除',
    //PRIMARY KEY (`id`)
    //) ENGINE = INNODB DEFAULT CHARSET = UTF8 COMMENT '用户任务表' ;
    /**
     * {@inheritdoc}
     */
    public function safeUp ()
    {
        $this->createInnoDBTable('{{%task}}', [
            'id' => $this->primaryKey(),
            'title' => $this->string(50)->notNull()->comment('任务标题'),
            'content' => $this->text()->notNull()->comment('任务详情'),
            'job_id' => $this->integer()->notNull()->defaultValue(0)->comment('任务ID'),
            'type' => $this->tinyInteger(1)->notNull()->defaultValue(0)->comment('类型'),
            'status' => $this->tinyInteger(1)->notNull()->defaultValue(0)->comment('状态'),
            'add_time' => $this->integer()->notNull()->comment('添加时间'),
            'update_time' => $this->integer()->notNull()->comment('修改时间'),
            'expect_time' => $this->integer()->notNull()->comment('预期执行时间'),
            'exec_time' => $this->integer()->notNull()->comment('实际执行时间'),
            'exec_status' => $this->tinyInteger(1)->notNull()->defaultValue(0)->comment('执行结果'),
            'exec_count' => $this->tinyInteger(1)->notNull()->defaultValue(0)->comment('执行次数'),
            'is_del' => $this->tinyInteger(1)->notNull()->defaultValue(0)->comment('是否删除'),
        ], '用户任务表');
    }
    /**
     * {@inheritdoc}
     */
    public function safeDown ()
    {
        $this->dropTable('{{%task}}');
    }
}

执行所有migration

yii migrate

执行完成之后，就会在数据库中建立三个表 user, user_token, task。