MySQL表自动增量列
我创建了迷你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 技术交流群。
发布评论
评论(4)
狼亦尘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 次
~没有更多了~
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
首先,您需要一个充分的理由来说明为什么要实现已删除 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.