无法删除 MariaDB 表中的字段 [英] Not able to drop field in MariaDB table

问题描述

我有表 my_transitions，我正在尝试删除名为 myStage 的字段.

I have table my_transitions and I am trying to delete field called myStage.

我正在使用这个命令:

alter table `my_transitions` drop `myStage`

我看到了这个错误:

表中不存在键列myStage"

但是当我使用此命令列出所有字段时:describe my_transitions

But when I list all fields using this command: describe my_transitions

我可以看到

id  bigint(20) unsigned NO  PRI NULL    auto_increment
myStage varchar(255)    NO  MUL NULL    
updated_at  timestamp   YES     NULL    

任何人都可以看到我是否做错了什么?

Anyone can see if I am doing something wrong?

如果我运行 show create table my_transitions;，我会得到:

If I run show create table my_transitions;, I am getting:

CREATE TABLE `my_transitions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `myStage` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `myStage1` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_stage_combination` (`myStage`,`myStage1`),
  KEY `my_transitions_myStage` (`myStage`),
  KEY `my_transitions_myStage1` (`myStage1`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

推荐答案

这是 MariaDB 中的一个特殊错误.它会影响 MariaDB 10.5.

This is a pecular bug in MariaDB. It affects MariaDB 10.5.

演示:https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=867204670347fa29e40bd5eb510c6956

解决方法是先删除列 mystage 所属的 UNIQUE KEY，然后再删除该列.

The workaround is to drop the UNIQUE KEY that column mystage is part of first, then drop the column.

alter table my_transitions drop key unique_stage_combination, drop column mystage;

P.S.:我在 MySQL 8.0 上对此进行了测试，它不需要解决方法.它确实删除了该列，但它只在一个列 mystage1 上留下了一个 UNIQUE KEY 列，这可能不是您想要的.

P.S.: I tested this on MySQL 8.0 and it does not require the workaround. It does drop the column, but it leaves the table with a UNIQUE KEY column on just one column mystage1, which might not be what you want.

这篇关于无法删除 MariaDB 表中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！