我在使用MySQL，我没有一个很好的办法做到这一点。

I am using MySQL, I don't have a good way to do this.

我有一个表的位置字段，我需要跟踪其值从1到10000。

I have a table with a position field, which I need to keep track having values from 1 to 10,000.

比方说，我插入一条记录在中间的第5000位。所以位置5000至10,000需要更新到新的位置;旧的5000变成5001，5002变成5003 ...

Let's say I insert a record in the middle at 5000th position. So position 5000 to 10,000 need to be updated to the new position; old 5000 become 5001, 5002 becomes 5003...

有没有实现这一点没有影响到这么多条记录的好办法，当1个单位置添加？

Is there a good way to implement this without affecting so many records, when 1 single position is added?

从第一位置添加是最糟糕的。

Adding from the position 1st is the worst.

推荐答案

我会重新考虑数据库设计。如果你打算仅限于10K记录的顺序则不是太糟糕，但如果这是要增加无限制，那么你会想做些别的事情。我不知道你在做什么，但如果你想要一个简单的排序（假设你没有做很多穿越的），那么你可以有一个 prev_id 和 NEXT_ID 列，以指示兄弟关系。这里的回答你的问题，虽然：

I'd rethink the database design. If you're going to be limited to on the order of 10K records then it's not too bad, but if this is going to increase without bound then you'll want to do something else. I'm not sure what you are doing but if you want a simple ordering (assuming you're not doing a lot of traversal) then you can have a prev_id and next_id column to indicate sibling relationships. Here's the answer to your questions though:

update some_table 
set some_position = some_position + 1
where some_position > 5000 and some_position < 10000