MySQL 将表从 varchar 移至 int

Question

我正在移动一张旧的 Mantis table ，上面有 varchar(64) category_id 列添加到新的 Mantis 表中，该表的 int(10) category_id 列。

简化后的结构如下

bug_table(旧数据库)

+----+-------------+-------------+--------+
| id | project_id | category_id | report |
+----+-------------+-------------+--------+
| 1 | 0 | Server | crash |
| 2 | 0 | Database | error |
| 3 | 1 | Server | bug |
| 4 | 1 | Server | crash |
+----+-------------+-------------+--------+


category_table(新数据库)

+----+------------+----------+
| id | project_id | name |
+----+------------+----------+
| 0 | 1 | Server |
| 1 | 1 | Database |
| 2 | 2 | Server |
| 3 | 2 | Database |
+----+------------+----------+

我需要一个神奇的查询来替换 category_id在 bug_table 中，数值为 category_id在category_table中。值得庆幸的是，我能够通过 project_id 来匹配行和类别 name .

这是我正在处理的查询，但陷入了复杂性

UPDATE bug_table b SET b.category_id = c.id USING category_table WHERE b.category_id = c.name

Answer 1

我喜欢以与处理新查找/引用表的方式稍微不同的方式来处理此类任务。

1. 对我来说，新的类别表只有 id 和 name 列。根据示例数据，只有两行:服务器和数据库。是的，我意识到可能还有其他名称，但这些名称可以轻松添加，并且应该在继续最大化随后的 id 匹配之前添加。

2. 接下来，我将向错误表添加一个新列，该列可以称为“category_new”，其数据类型将存储新类别 ID。或者，您可以将现有的category_id列重命名为category，然后id的新列可以是column_id。

3. 完成所有这些操作后，您可以通过加入名称类别并设置匹配的 ID 来更新新列:(注意这假设非>-步骤2)中提到的替代方法

   UPDATE bug_table JOIN category_table ON bug_table.category_id = category_table.name 
   SET bug_table.category_new = category_table.id
   

4. 运行后，检查新列以验证更新后的 ID。

5. 最后，成功更新后，现在可以删除 bugs_table 中的旧类别_id 列(带有名称)，并且可以将类别_新列重命名为类别_id。

=====

请注意，如果您决定使用提到的替代列方法，查询当然会相似但略有不同。那么最后只需要一列drop即可

如果有其他表要应用相同的类别更改，则这些表的操作(基本上是步骤 2 到 5)也将类似。