mysql - 如何将数据库中的数据从字符串更改为 id - Rails，Mysql

Question

你好，我有一个简单的问题，也许不是...

历史:我想用 db(mysql) 的状态替换 yaml 文件，我在用户表列中有:状态。当我将逻辑替换为数据库时，我创建了模型状态、创建了表并配置了与用户的关系...

描述问题:当我在用户表中创建 status_id 时，我有 2 列:“status”和“status_id”。 “状态”列是字符串，有很多字符串值，例如“已确认”。如何(使用带状态的种子迁移)并填写“status_id”列。我的意思是，如果“status”列的值为“confirmed”，我希望“status_id”列的值为:1。

statuses table:
id name
1   confirmed
2   not confirmed
3   something else


Users table
id status           status_id
1  confirmed        empty
2  confirmed        empty
3  confirmed        empty
4  not confirmed    empty
5  not confirmed    empty
6  something else   empty
7  something else   empty

User belongs_to :status

Status has_many :users

---

question: Why i didnt just change name and type in "status" column on "status_id" with data type: "id"?

answer: Because i need to deploy it using capistrano to production server and i cant losing data and remove data from status column.

Answer 1

给定以下(您的)示例数据:

create table statuses (id int, name char(20));
insert into statuses (id, name) values
    (1, 'confirmed'),
    (2, 'not confirmed'),
    (3, 'something else');

create table users (id int, status char(20), status_id int);
insert into users (id, status) values
    (1,'confirmed'),
    (2,'confirmed'),
    (3,'confirmed'),
    (4,'not confirmed'),
    (5,'not confirmed'),
    (6,'something else'),
    (7,'something else');

select * from users;
+------+----------------+-----------+
| id   | status         | status_id |
+------+----------------+-----------+
|    1 | confirmed      |      NULL |
|    2 | confirmed      |      NULL |
|    3 | confirmed      |      NULL |
|    4 | not confirmed  |      NULL |
|    5 | not confirmed  |      NULL |
|    6 | something else |      NULL |
|    7 | something else |      NULL |
+------+----------------+-----------+
7 rows in set (0.00 sec)

此 update 语句使用 statuses 中的适当值更新 users 中的 status_id 列:

update users u 
   set u.status_id=(select s.id from statuses s where u.status=s.name);
Query OK, 7 rows affected (0.01 sec)
Rows matched: 7  Changed: 7  Warnings: 0

select * from users;
+------+----------------+-----------+
| id   | status         | status_id |
+------+----------------+-----------+
|    1 | confirmed      |         1 |
|    2 | confirmed      |         1 |
|    3 | confirmed      |         1 |
|    4 | not confirmed  |         2 |
|    5 | not confirmed  |         2 |
|    6 | something else |         3 |
|    7 | something else |         3 |
+------+----------------+-----------+
7 rows in set (0.00 sec)

希望这是你要的，因为我的回答既不涉及ruby，yaml，也不涉及rails。