MySQL-尝试添加新列时出现重复输入错误 [英] MySQL - Duplicate entry error when trying to add new column

问题描述

我有一个MySQL数据库，其中的表使用innodb引擎具有200万行.我想添加另一列，但仍然出现以下错误:

I have a MySQL database with a table that has 2 million rows using innodb engine. I want to add another column, but I keep getting the following error:

Error 1062: Duplicate entry '' for key 'PRIMARY' SQL Statement: ALTER TABLE `mydb`.`table`  ADD COLUMN `country` VARCHAR(35) NULL DEFAULT NULL AFTER `email` 

如何添加列而不出现此错误?

How can I add the column without getting this error?

编辑:表定义

id int(11) NOT NULL AUTO_INCREMENT,   
user_id varchar(45) NOT NULL,   
first_name varchar(150) DEFAULT NULL,   
last_name varchar(150) DEFAULT NULL,   
gender varchar(10) DEFAULT NULL,   
email varchar(100) DEFAULT NULL,   
created_at bigint(20) DEFAULT NULL,   
updated_at bigint(20) DEFAULT NULL,  
PRIMARY KEY (`id`,`user_id`),   
UNIQUE KEY `user_id_UNIQUE` (`user_id`),   
KEY `first_name` (`first_name`),   
KEY `last_name` (`last_name`)

编辑#2:显示索引输出

Table       Non_unique  Key_name        Seq_in_index  Column_name     Collation  Cardinality Index_type
table       0           PRIMARY         1             id              A          3516446     BTREE      
table       0           PRIMARY         2             user_id         A          3516446     BTREE
table       0           user_id_UNIQUE  1             user_id         A          3516446     BTREE
table       1           first_name      1             first_name      A          390716      BTREE  
table       1           last_name       1             last_name       A          439555      BTREE

推荐答案

它的解决方案将在写时锁定表，但通常适合解决表不是很大的问题

it solution will lock table on write, but often suitable for solving the problem if table is not very big

LOCK TABLES my_table WRITE;

ALTER TABLE my_table 
ADD COLUMN `ts` DATETIME NULL AFTER `id`;

UNLOCK TABLES;

这篇关于MySQL-尝试添加新列时出现重复输入错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！