在 Yii2 中，计划将 RBAC 相关的表，字符串类型的字段长度调整为 200 的实现

1、执行迁移命令：yii migrate –migrationPath=@yii/rbac/migrations 后，字符串类型的字段长度为 64

-- apply_server_v6.auth_item definition

CREATE TABLE `auth_item` (
  `name` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `type` smallint NOT NULL,
  `description` text COLLATE utf8mb3_unicode_ci,
  `rule_name` varchar(64) COLLATE utf8mb3_unicode_ci DEFAULT NULL,
  `data` blob,
  `created_at` int DEFAULT NULL,
  `updated_at` int DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `rule_name` (`rule_name`),
  KEY `idx-auth_item-type` (`type`),
  CONSTRAINT `auth_item_ibfk_1` FOREIGN KEY (`rule_name`) REFERENCES `auth_rule` (`name`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- apply_server_v6.auth_assignment definition

CREATE TABLE `auth_assignment` (
  `item_name` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `user_id` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `created_at` int DEFAULT NULL,
  PRIMARY KEY (`item_name`,`user_id`),
  KEY `idx-auth_assignment-user_id` (`user_id`),
  CONSTRAINT `auth_assignment_ibfk_1` FOREIGN KEY (`item_name`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- apply_server_v6.auth_item_child definition

CREATE TABLE `auth_item_child` (
  `parent` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `child` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  PRIMARY KEY (`parent`,`child`),
  KEY `child` (`child`),
  CONSTRAINT `auth_item_child_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `auth_item_child_ibfk_2` FOREIGN KEY (`child`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- apply_server_v6.auth_rule definition

CREATE TABLE `auth_rule` (
  `name` varchar(64) COLLATE utf8mb3_unicode_ci NOT NULL,
  `data` blob,
  `created_at` int DEFAULT NULL,
  `updated_at` int DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

2、本计划新建一个迁移，然后修改字段的长度。但是，执行迁移命令时，报错 SQLSTATE[HY000]: General error: 1833 Cannot change column ‘name’: used in a foreign key constraint ‘auth_item_child_ibfk_1’ of table 。如图1

图1

PS C:\wwwroot\object\src> ./yii migrate
Yii Migration Tool (based on Yii v2.0.49.3)

Total 1 new migration to be applied:
        m250409_162156_alter_columns_in_auth

Apply the above migration? (yes|no) [no]:yes
*** applying m250409_162156_alter_columns_in_auth
    > alter column name in table auth_item to string(200) NOT NULL ...Exception: SQLSTATE[HY000]: General error: 1833 Cannot change column 'name': used in a foreign key constraint 'auth_item_child_ibfk_1' of table 'apply_server_v6.auth_item_child'
The SQL being executed was: ALTER TABLE `auth_item` CHANGE `name` `name` varchar(200) NOT NULL (C:\wwwroot\object\src\vendor\yiisoft\yii2\db\Schema.php:676)
#0 C:\wwwroot\object\src\vendor\yiisoft\yii2\db\Command.php(1325): yii\db\Schema->convertException(Object(PDOException), 'ALTER TABLE `au...')
#1 C:\wwwroot\object\src\vendor\yiisoft\yii2\db\Command.php(1120): yii\db\Command->internalExecute('ALTER TABLE `au...')
#2 C:\wwwroot\object\src\vendor\yiisoft\yii2\db\Migration.php(431): yii\db\Command->execute()
#3 C:\wwwroot\object\src\console\migrations\m250409_162156_alter_columns_in_auth.php(15): yii\db\Migration->alterColumn('auth_item', 'name', Object(yii\db\mysql\ColumnSchemaBuilder))
#4 C:\wwwroot\object\src\vendor\yiisoft\yii2\db\Migration.php(114): m250409_162156_alter_columns_in_auth->safeUp()
#5 C:\wwwroot\object\src\vendor\yiisoft\yii2\console\controllers\BaseMigrateController.php(758): yii\db\Migration->up()
#6 C:\wwwroot\object\src\vendor\yiisoft\yii2\console\controllers\BaseMigrateController.php(216): yii\console\controllers\BaseMigrateController->migrateUp('m250409_162156_...')
#7 [internal function]: yii\console\controllers\BaseMigrateController->actionUp(0)
#8 C:\wwwroot\object\src\vendor\yiisoft\yii2\base\InlineAction.php(57): call_user_func_array(Array, Array)
#9 C:\wwwroot\object\src\vendor\yiisoft\yii2\base\Controller.php(178): yii\base\InlineAction->runWithParams(Array)
#10 C:\wwwroot\object\src\vendor\yiisoft\yii2\console\Controller.php(180): yii\base\Controller->runAction('', Array)
#11 C:\wwwroot\object\src\vendor\yiisoft\yii2\base\Module.php(552): yii\console\Controller->runAction('', Array)
#12 C:\wwwroot\object\src\vendor\yiisoft\yii2\console\Application.php(180): yii\base\Module->runAction('migrate', Array)
#13 C:\wwwroot\object\src\vendor\yiisoft\yii2\console\Application.php(147): yii\console\Application->runAction('migrate', Array)
#14 C:\wwwroot\object\src\vendor\yiisoft\yii2\base\Application.php(384): yii\console\Application->handleRequest(Object(yii\console\Request))
#15 C:\wwwroot\object\src\yii(23): yii\base\Application->run()
#16 {main}
*** failed to apply m250409_162156_alter_columns_in_auth (time: 0.036s)

0 from 1 migrations were applied.

Migration failed. The rest of the migrations are canceled.

3、最终决定将 迁移命令：yii migrate –migrationPath=@yii/rbac/migrations 所涉及到的 4 个迁移文件复制至 console/migrations，然后直接修改 m140506_102106_rbac_init，将 64 替换为 200。然后删除掉 auth_ 开头的表，再删除掉 migration 表中的 4 条迁移记录。重新执行迁移命令：./yii migrate

PS C:\wwwroot\object\src> ./yii migrate
Yii Migration Tool (based on Yii v2.0.49.3)

Total 4 new migrations to be applied:
        m140506_102106_rbac_init
        m170907_052038_rbac_add_index_on_auth_assignment_user_id
        m180523_151638_rbac_updates_indexes_without_prefix
        m200409_110543_rbac_update_mssql_trigger

Apply the above migrations? (yes|no) [no]:yes
*** applying m140506_102106_rbac_init
    > create table {{%auth_rule}} ... done (time: 0.023s)
    > create table {{%auth_item}} ... done (time: 0.026s)
    > create index idx-auth_item-type on {{%auth_item}} (type) ... done (time: 0.018s)
    > create table {{%auth_item_child}} ... done (time: 0.027s)
    > create table {{%auth_assignment}} ... done (time: 0.021s)
*** applied m140506_102106_rbac_init (time: 0.127s)

*** applying m170907_052038_rbac_add_index_on_auth_assignment_user_id
    > create index auth_assignment_user_id_idx on {{%auth_assignment}} (user_id) ... done (time: 0.015s)
*** applied m170907_052038_rbac_add_index_on_auth_assignment_user_id (time: 0.021s)

*** applying m180523_151638_rbac_updates_indexes_without_prefix
    > drop index auth_assignment_user_id_idx on {{%auth_assignment}} ... done (time: 0.009s)
    > create index {{%idx-auth_assignment-user_id}} on {{%auth_assignment}} (user_id) ... done (time: 0.010s)
    > drop index idx-auth_item-type on {{%auth_item}} ... done (time: 0.017s)
    > create index {{%idx-auth_item-type}} on {{%auth_item}} (type) ... done (time: 0.016s)
*** applied m180523_151638_rbac_updates_indexes_without_prefix (time: 0.057s)

*** applying m200409_110543_rbac_update_mssql_trigger
*** applied m200409_110543_rbac_update_mssql_trigger (time: 0.007s)

4 migrations were applied.

Migrated up successfully.

4、auth_ 开头的 4 张表中字符串类型的长度皆已经调整为 200，符合预期。如图2

图2