重复使用mysql表中的行，而无需自动递增 [英] Reuse Rows in mysql table without auto incrementing

问题描述

嘿，让我解释一下我的问题.我有一个mysql表，其中存储了来自5个不同站点的数据馈送.现在，我每天更新一次提要.我有这个主键FeedId，它会自动递增.现在，我要做的是，当我更新特定站点的提要时，我从表中删除了该站点的先前数据，然后输入新的数据.这样，新数据将填充到先前删除的数据所占据的行中，如果这次有更多的提要，则在表末尾输入其余的提要.但是对于所有新数据，FeedId都会增加. 我想要的是，存储在旧位置的Feed保留了以前的ID，只有最后保存的额外ID才获得新的递增ID.请帮忙，因为我不知道该怎么办.

hey let me explain my problem. I have a mysql table in which i store data feeds from say 5 different sites. Now i update the feeds once daily. I have this primary key FeedId which auto-increments. Now what i do is when i update feeds from particular site i delete previous data for that site from my table and enter the new one. This way the new data is filled in the rows occupied by previous deleted data and if this time there are more feeds rest are entered at the end of table. But the FeedId is incremented for all the new data. What i want is that the feeds stored in old locations retain previous Id n only the extra ones being saved at the end get new incremented Ids. Please help as i cant figure out how to do that.

推荐答案

更好的解决方案是在Feed上设置唯一键(除了自动递增的键).然后使用 INSERT ON DUPLICATE KEY UPDATE

A better solution would be to set a unique key on the feed (aside from the auto-incremented key). Then use INSERT ON DUPLICATE KEY UPDATE

INSERT INTO feeds (name, url, etc, etc2, `update_count`) 
    VALUES ('name', 'url', 'etc', 'etc2', 1) 
    ON DUPLICATE KEY UPDATE
        `etc` = VALUES(`etc`),
        `etc2` = VALUES(`etc2`),
        `update_count` = `update_count` + 1;

好处是您不增加ID，而仍在一个原子查询中进行.另外，您只需更新/更改需要更改的内容. (请注意，我包括了update_count列以显示如何更新字段)...

The benefit is that you're not incrementing the ids, and you're still doing it in one atomic query. Plus, you're only updating / changing what you need to change. (Note that I included the update_count column to show how to update a field)...

这篇关于重复使用mysql表中的行，而无需自动递增的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！