Swoft2 框架精华教程：数据库 Migration

# 查看脚手架的相关命令
php bin/swoft -h
Usage:
  swoft COMMAND [arg0 arg1 arg2 ...] [--opt -v -h ...]

Options:
      --debug        Setting the application runtime debug level(0 - 4)
      --no-color     Disable color/ANSI for message output
  -h, --help         Display help message for application or command
  -V, --version      Display application version information
      --expand-cmd   Expand sub-commands for all command groups

Available Commands:
  agent      This is an agent for Apllo config center
  app        There are some help command for application[by devtool]
  dConf      There are some help command for application[by devtool]
  dclient    Provide some simple tcp, ws client for develop or testing[by devtool]
  demo       Class DemoCommand
  dinfo      There are some commands for application dev[by devtool]
  dtool      There are some commands for application dev[by devtool]
  entity     Generate entity class by database table names[by devtool]
  http       Provide some commands to manage the swoft HTTP server(alias: httpsrv)
  migrate    Manage swoft project database migration commands[by devtool](alias: mig)
  process    Provides some command for manage process pool
  rpc        Provide some commands to manage swoft RPC server
  tcp        Provide some commands to manage swoft TCP server(alias: tcpsrv)
  test       Class TestCommand
  ws         Provide some commands to manage swoft websocket server(alias: wsserver,websocket)

// 只指定时间
@Migration(time=20190913232743)
// 指定时间和可用的连接池
@Migration(time=20190630164222, pool="db.pool,db1.pool")

return [
    // 配置 migrationManager 的查找路径，此路径默认为 app/Migration
    'migrationManager'   => [
        'migrationPath' => '@base/database',
    ],
];

// 执行如下命令可以创建对应的 Migration 的基本框架.
$ php bin/swoft mig:create cms
Generate migrate /var/www/swoft-app/database/Migration/Cms.php, ensure continue ?
Please confirm (yes|no)[default:yes]: 
yes
Migration Create Success
  className Cms
  file      /var/www/swoft-app/database/Migration/Cms.php

// 执行创建指定 migration 的表结构
php bin/swoft mig:up cms
// 执行删除指定 migration 中的表结构
php bin/swoft mig:down cms
// 执行根据数据表创建实体类，最后参数为表名称
php bin/swoft entity:c user
// 更详细的语句，后台执行创建
php bin/swoft entity:create -d swoft --table tb_content --table_prefix tb_ --remove_prefix tb_

<?php declare(strict_types=1);

namespace Database\Migration;

use Swoft\Devtool\Migration\Migration as BaseMigration;
use Swoft\Devtool\Annotation\Mapping\Migration;
use Swoft\Db\Schema\Blueprint;

/**
 * Class User
 * @Migration(time=20250310101700)
 */
class User extends BaseMigration
{

    /**
     * @inheritDoc
     */
    public function up(): void
    {
        // 用户主表
        $this->schema->createIfNotExists('user', function (Blueprint $blueprint) {
            $blueprint->comment('用户主账号表');

            $blueprint->string('id', 32)->comment('用户唯一ID');
            $blueprint->string('name', 100)->comment('用户名');
            $blueprint->string('password', 64)->comment('密码');
            $blueprint->string('salt', 6)->comment('密码盐');
            $blueprint->bigInteger('create_time', false, true)->comment('创建时间');
            $blueprint->bigInteger('update_time', false, true)->comment('更新时间');

            // 普通btree 索引，前缀索引20位字符，注意这里必须用表达式对象，保证sql按用户自定义格式拼接
            $blueprint->index([Expression::new('`name`(20)')], 'idx_name');
            // 唯一索引
            $blueprint->unique('md5', 'uq_md5');
            // 普通索引，字段名可以用字符串，或者数组。
            // 前缀索引不用表达式对象，会造成整个部分作为字段名引用，进而导致字段无法找的语法问题
            $blueprint->index('create_time', 'idx_create_time');
            $blueprint->engine  = 'Innodb';
            $blueprint->charset = 'utf8mb4';
        });
    }

    public function down(): void
    {
        $this->schema->dropIfExists('user');
    }
}

-- migration 表格式
CREATE TABLE `migration` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `time` bigint NOT NULL,
  `is_rollback` tinyint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 数据
-- id	name					time			is_rollback
-- 1	Database\Migration\User	20250310101700	1
-- 其中 name 是执行的 Migration 名字记录，time 是时间
-- is_rollback=1 表示回滚过（down执行后），
-- is_rollback=2 表示已经完成（up命令执行过后）