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

作者：

在

Yii2 RBAC, Yii, Web框架, Web应用开发, Yii 2, Yii2 数据库迁移

Post Views: 140

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

本计划新建一个迁移，然后修改字段的长度。但是，执行迁移命令时，报错 SQLSTATE[HY000]: General error: 1833 Cannot change column 'name': used in a foreign key constraint 'auth_item_child_ibfk_1' of table — 图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

PHP / Laravel / Yii2 老项目维护与长期技术支持

如果你的 PHP / Laravel / Yii2 项目已经上线，但遇到原开发离职、Bug 长期无人修复、接口不稳定、性能下降、代码难以接手等问题，可以联系我做一次远程技术排查。

适合以下情况：
✅ 老旧 PHP 系统无人维护
✅ Laravel / Yii2 项目 Bug 修复
✅ 后台管理系统小功能迭代
✅ RESTful API 接口排查
✅ MySQL / Redis / Nginx 性能问题
✅ 长期远程兼职维护

可先从一次小问题开始：
✅ 线上报错排查
✅ 接口异常分析
✅ 慢查询与性能瓶颈定位
✅ 代码结构初步评估
✅ 部署环境与日志检查

如需咨询，请联系我，并注明：PHP 维护咨询。

联系方式：
Telegram：@shuijingwan
微信：13980074657
邮箱：shuijingwanwq@gmail.com

@yii/rbac/migrations varchar(64) auth_item_child_ibfk_1 m140506_102106_rbac_init 64 SQLSTATE[HY000] General error foreign key constraint

发表回复取消回复

这个站点使用 Akismet 来减少垃圾评论。了解你的评论数据如何被处理。

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

评论

发表回复 取消回复

更多文章

用 EdgeOne 与 Cloudflare 统一解决 WordPress 随机 Query String 缓存穿透：只优化 canonical 文章 URL

WordPress CPU 再次满载：动态参数如何穿透 CDN 与 W3TC 页面缓存

从一次 502 故障到补齐监控体系：为阿里云 ECS 恢复主机监控并配置 CPU、内存、磁盘和带宽报警

一次 WordPress 502 故障排查：18,455 个 Sogou UA 请求如何打满 1 核 ECS

发表回复取消回复