我能否将一个MySQL表中的列移动到另一个表，并用新表中的ID替换原始列？

Question

我的项目正在增长，需要从一个表扩展到两个表。

我有文章，每篇文章都有一个作者。目前，作者是文章中的一列，但是我已经创建了一个名为“authors”的新表。我希望将所有唯一作者实例填充到新表中，并使用新作者ID替换文章表中的作者值。

如何使用SQL完成此操作？

我的文章表有30k条记录。

- Mickey Slater

Answer 1

1. Create the new table Authors.

   CREATE TABLE Authors (
     author_id INT AUTO_INCREMENT PRIMARY KEY,
     author_name VARCHAR(20)
   );
   

2. Populate Authors with the set of distinct authors from Posts.

   INSERT INTO Authors (author_name) 
   SELECT DISTINCT author_name FROM Posts;
   

3. Add an author_id column to Posts, which references Authors.

   ALTER TABLE Posts ADD COLUMN author_id INT, 
       ADD FOREIGN KEY (author_id) REFERENCES Authors(author_id);
   

4. Update Posts with the corresponding author_id values, based on an (inefficient) join based on the author_name in each table. MySQL supports multi-table UPDATE syntax, which is not standard SQL, but it's very handy for these situations.

   UPDATE Posts JOIN Authors USING (author_name)
   SET Posts.author_id = Authors.author_id;
   

5. Drop the now-redundant author_name from Posts, and optionally make author_id not nullable.

   ALTER TABLE Posts DROP COLUMN author_name, MODIFY author_id INT NOT NULL;
   

- Bill Karwin

Answer 2

我会按照以下步骤进行：

1. 创建新表格 table2
2. 向 table1 添加列 table2_id
3. 通过从 table1 中选择，包括 table1.id（但称其为 table1_id 以保留其自己的独立 id）和所需的 table1.column，填充（更新）table2
4. 使用来自 table2 的 ids（主键）更新 table1.table2_id
5. 从 table2 中删除列 table1_id

- Michael Durrant

---

我实际上不想在表2中有table1.id。我希望table2.id与由匹配作者创建的table1中的任何帖子相关联。 - Mickey Slater

它在最后被丢弃了。 - Michael Durrant