mysql - 根据现有表中的值为表生成另一列

Question

假设下表:

CREATE TABLE `item`(
`item_id` int(11) NOT NULL AUTO_INCREMENT,
`item_serial_number` varchar(255) DEFAULT NULL,
`item_type` varchar(255) DEFAULT NULL,
PRIMARY KEY (`item_id`)
)

和以下插入:

INSERT INTO `item` (`item_id`, `item_serial_number`, `item_type`) VALUES
(1, '11232141', 'laptop'),
(2, '22343252', 'printer'),
(3, '34234244', 'printer'),
(4, '78678678', 'charger');

如果我想添加另一列，我应该查看/查看哪些 SQL 命令，即 item_type_id，它将查找每个 item_type varchar 字段值并生成如下值这个:

------------------------
|ID| SERIAL   | TYPE   |
------------------------
|1 | 11232141 | laptop |
|2 | 22343252 | printer|
|3 | 34234244 | printer|
|4 | 78678678 | charger|
------------------------

->
----------------------------------
|ID| SERIAL   | TYPE   | TYPE_ID |
----------------------------------
|1 | 11232141 | laptop |   1     |
|2 | 22343252 | printer|   2     |
|3 | 34234244 | printer|   2     |
|4 | 78678678 | charger|   3     |
----------------------------------

Answer 1

如果你想正确地做到这一点，创建一个types表:

create table types (
    type_id int auto_increment primary key,
    type varchar(255)
);

insert into types (type)
    select distinct type
    from item;

然后，修复当前表:

alter table item add type_id int;

alter table item add constraint fk_types_type_id
    foreign key (type_id) references types (type_id);

然后设置值:

update item i join
       types t
       on i.type = t.type
    set i.type_id = t.type_id;

最后删除旧列:

alter table item drop column type;