如何仅用MySQL将logs表type字段替换为外键并创建logs_type表？

完全可以只用MySQL语句一次性完成logs_type表的创建、数据填充，以及logs表的字段替换和外键设置，具体步骤如下：

1. 创建并填充logs_type表 #

利用CREATE TABLE ... SELECT语法结合窗口函数，一次性生成类型表并插入唯一的日志类型：

CREATE TABLE logs_type (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type VARCHAR(255) UNIQUE NOT NULL
) AS
SELECT DISTINCT
    ROW_NUMBER() OVER (ORDER BY type) AS id,
    type
FROM logs;

• DISTINCT确保每种日志类型只保留一条记录
• ROW_NUMBER()按类型名称排序生成连续的数字ID

2. 替换logs表的字符串类型为数字ID #

通过关联logs_type表，把logs中原varchar类型的type字段替换为对应的数字ID：

UPDATE logs l
JOIN logs_type lt ON l.type = lt.type
SET l.type = lt.id;

MySQL会自动把数字字符串转换为数值，后续修改字段类型时不会出错。

3. 修改字段类型并添加外键约束 #

将logs表的type字段从varchar改为int，同时添加外键约束关联logs_type表：

ALTER TABLE logs
MODIFY COLUMN type INT NOT NULL,
ADD CONSTRAINT fk_logs_type FOREIGN KEY (type) REFERENCES logs_type(id);

注意事项 #

• 若logs是超大型表，一次性UPDATE可能会长时间锁表，建议按主键范围分批次更新，减少对业务的影响
• 操作前一定要备份全量数据，防止意外数据丢失

内容的提问来源于stack exchange，提问作者Corathir