MySQL表自动增量列

发布于 12-09 11:44 字数 164 浏览 0 评论 0原文

我创建了迷你cms。每个页面都有唯一的 id。用户可以添加、编辑、删除页面。

假设我们有 10 个页面,user1 删除了他的第 5-8 页。现在我们有 id 为 1,2,3,4,9,10 的页面。下次可以用id为5-8的行填充吗?如果是的话如何搜索并填写?顺便说一句,我的“id”字段是自动递增的

I've created mini-cms. Every page has unique id. Users can add, edit, delete pages.

Lets say we have 10 pages, and user1 deletes his pages 5-8. So now we have pages with id's 1,2,3,4,9,10. Is it possible to fill rows with id's 5-8 next time? If yes how to search for them and fill? BTW, my 'id' field is autoincremented

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

┊风居住的梦幻卍2024-12-16 11:44:18

首先,您需要一个充分的理由来说明为什么要实现已删除 ID 的重用。
主键用于唯一标识一条记录。时期。它不是为了人们个人的显示偏好或其他什么,它的存在是为了一个实际的、可靠的、逻辑的原因 - 因此它被称为主键

坦白说,这有什么意义呢? id 是 1 还是 201321941 对于计算机来说都是 0 的区别。如果您认为删除某些内容后出现间隙是“丑陋的”,那么您需要更改逻辑并停止使用 auto_increments,或者至少遵守它们。

您需要知道的第一件事是您永远永远不想篡改auto_increment
为什么?

首先,auto_increment 始终用于 MySQL 表的主键,因此它是一个代理主键,这意味着 MySQL 决定如何物理生成它。如果您将人为因素添加到处理敏感内容的算法中(例如始终为特定表正确生成下一个整数,考虑事务和未考虑的内容),那么您会遇到灾难。

其次,对于某些引擎(例如InnoDB),主键决定了数据的物理存储顺序。这意味着什么?假设你的表中有 100 万个条目,InnoDB 存储数据的方式是将最新记录追加到数据文件中,因为每个下一个 id 都比前一个大。然后,假设您删除了数字 500,000,并且想要重新使用它。这意味着从物理上来说,InnoDB 必须在第 500,000 行插入记录(简化解释)并重新排序 B 树以符合更改,这比仅仅追加数据要昂贵得多。

还有其他原因,我现在真的不想提及,但如果您想要某种顺序顺序或显示标识符 - 那么创建另一个名为“sequence_id”或类似字段的字段,并创建一个能够正确处理它的触发器(递增或递减)。
否则,不要做可能伤害你的事情。

First off, you need a solid reason why you want to achieve the reuse of deleted ID's.
Primary key is there to uniquely identify a record. Period. It's not for people's personal preferences of display or what not, it's there for an actual, solid, logical reason - hence it's called the primary key.

Quite frankly, what's the point? Whether the id is 1 or 201321941 makes 0 difference to the computer. If it you think it's "ugly" for having gaps after you delete something, then you need to change your logic and stop using auto_increments, or at least conform to them.

First thing you need to know is that you never, ever want to tamper with auto_increment.
Why?

First off, auto_increment is always used for primary key with MySQL tables, and therefore it's a surrogate primary key which means MySQL determines how to generate it physically. If you add human factor to an algorithm that deals with sensitive stuff (such as always, correctly generate next integer for a specific table, taking transactions and what not into account) - you get a disaster.

Second, for some engines such as InnoDB, primary key determines physical storage order of the data. What does that mean? Say you have 1 million entries in your table, and the way the InnoDB stores data is to append the latest record to the data file since every next id is larger than previous. Then, you, say, delete number 500,000 and you want to reuse it. That means that physically, InnoDB has to insert the record at the line 500,000 (simplified explanation) and reorder the b-tree to conform to the change which is much more expensive than just to append data.

There are also other reasons that I really don't want to mention now, but if you want some sort of sequential order or display of your identifiers - then create another field called sequence_id or something similar and create a trigger that will properly handle it (increment it or decrement it).
Otherwise, don't do something that might hurt you.

时间你老了2024-12-16 11:44:18

你不应该这样做。

唯一标识符不是数字。它是唯一标识符。独一无二的。明白吗?
人们永远不应该接触唯一的标识符。它标识该行并且应该永远保留在该行上。

至于你的“问题”,

  1. 我怀疑有人会注意到这种数字上的不一致。特别是如果您的页面有一些层次结构。

  2. 如果你想要你的网站有漂亮的网址 - 让它们真正漂亮,用slugs阅读新闻文章等。这比你愚蠢的有序数字要好得多。

You shouldn't do that.

Unique identifier is not a number. It is unique identifier. Unique one. Understand?
One should never ever touch an unique identifier. It identifies the row and should be stuck with it forever.

As to your "problem",

  1. I doubt anyone will ever notice such inconsistence in numbers. Especially if you have some hierarchy for your pages.

  2. If you want nice urls for your site - make them real nice, with slugs reading news, articles etc. It would be much better than your silly ordered numbers.

清风不识月2024-12-16 11:44:18

如果 ID 不是自动递增的,则可以执行此操作。
在删除记录(页面)时,您应该将所有 ID 存储在另一张表中并创建已删除记录的日志。现在,当您插入任何新记录时,您可以使用当前记录表检查已删除的记录 ID;如果那里不存在该 id,那么您可以通过查询创建该 id。
因此,在某些记录结束时,您丢失的记录 ID 将被当前记录填充。

我心中唯一可以让您摆脱这个问题的选择..如果需要,您可以使用此选项。

谢谢。

You can do this if ID is not an Auto-incremented.
And at the time of deleting records (pages) you should store all the IDs in one another table and create the log of deleted records. now when you insert any new records at that time you can check the deleted records id with the current records table; if that id not exist over there then you can create that id by query..
So by the end of some records your missing record IDs will fill with current records.

The only option in my mind which can take you out of this issues..you can use this option if needed.

Thanks.

狼亦尘2024-12-16 11:44:18

你可以这样做(你必须删除自动增量):

SELECT id FROM page_table ORDER BY id ASC

然后你找到第一个未设置的id号

// $result is a one dimension array containing all ids (so you have to parse what sql query returns before)

$autoIncStartVal = 1;

$nextId = null;

// count($result) + 1 if there is no "hole" in id (so $nextId will be count($result) + 1
for($i=0;$i<count($result) + 1;$i++) { 

  if(!isset($result[$i]) OR $result[$i] != $i + $autoIncStartVal) {
    $nextId = $i+$autoIncStartVal;
    break;
  }

}

示例:

$result = array(1,2,4,5);

$autoIncStartVal = 1;
$nextId = null;

    for($i=0;$i<count($result) + 1;$i++) { 
      if(!isset($result[$i]) OR $result[$i] != $i + $autoIncStartVal) {
        $nextId = $i+$autoIncStartVal;
        break;
      }
    }

var_dump($nextId); // 3

这将在执行的精确时刻找到下一个id SQL 请求。
如果同时执行两个请求,它们将返回相同的 id,因此 $nextid 将具有相同的值 2 次

You can do (you have to remove auto-increment) :

SELECT id FROM page_table ORDER BY id ASC

Then you find the first id number that is not set

// $result is a one dimension array containing all ids (so you have to parse what sql query returns before)

$autoIncStartVal = 1;

$nextId = null;

// count($result) + 1 if there is no "hole" in id (so $nextId will be count($result) + 1
for($i=0;$i<count($result) + 1;$i++) { 

  if(!isset($result[$i]) OR $result[$i] != $i + $autoIncStartVal) {
    $nextId = $i+$autoIncStartVal;
    break;
  }

}

Example :

$result = array(1,2,4,5);

$autoIncStartVal = 1;
$nextId = null;

    for($i=0;$i<count($result) + 1;$i++) { 
      if(!isset($result[$i]) OR $result[$i] != $i + $autoIncStartVal) {
        $nextId = $i+$autoIncStartVal;
        break;
      }
    }

var_dump($nextId); // 3

This will find the next id at the precise moment of the execution of the SQL request.
If two requests are executed at the same time, They will returns the same id and therefore, $nextid will have the same value 2 times

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文