MySQL:在已填充的表中创建新的唯一字段 [英] MySQL: Creating new unique field in already populated table

问题描述

我需要在数据库表中创建一个已经填充了数据的字段.当然，仅将字段添加为空是不可能的.我只能想到创建具有新结构的新表并将现有数据复制到新表中，但是我想知道是否有n种更简单的方法.

I need to create a field in a database table, already populated with data. Of course just adding the field empty is not possible. I can only think of creating a new table with a new structure and copy the existing data to the new table, but I wonder if there is a n easier way.

提示:这是一个组合键，由同一表中的其他3个字段组成.

Hint: it is a composite key, comprised of 3 other fields in the same table.

该字段保存一个varchar值 由于某些人的要求，仅使用ADD UNIQUE就无法在填充的表中创建新的UNIQUE字段.它会在所有条目中复制新的值(是否为空).示例:

the field holds a varchar value since some of you ask, it is not possible to create a new UNIQUE field in a populated table just by using ADD UNIQUE. It duplicates the new (empty or not) value throughout all entries. Example:

ALTER TABLE'tablename'ADD'fieldname'VARCHAR(64)NOT NULL， 添加唯一( 字段名" )

ALTER TABLE 'tablename' ADD 'fieldname' VARCHAR( 64 ) NOT NULL , ADD UNIQUE ( 'fieldname' )

错误:键"fieldname"的条目"重复

error: Duplicate entry '' for key 'fieldname'

推荐答案

当然不可能仅将字段添加为空.

Of course just adding the field empty is not possible.

为什么?

我将执行以下操作:

1. 通过ALTER TABLE t ADD COLUMN new_column *type_definition*
创建字段
2. 更新新创建的字段，例如UPDATE t SET new_column=*computation_expression*
3. 通过ALTER TABLE t ADD INDEX ...添加索引(如果需要，请添加ALTER TABLE t ADD PRIMARY KEY ...作为主要索引).

1. Create field by ALTER TABLE t ADD COLUMN new_column *type_definition*
2. Update newly created field like UPDATE t SET new_column=*computation_expression*
3. Add index by ALTER TABLE t ADD INDEX ... (or ALTER TABLE t ADD PRIMARY KEY ... if you need it to be primary).

这篇关于MySQL:在已填充的表中创建新的唯一字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！