blocks|key|2545554|text|主自动增量键仅用于唯一标识记录的.别管它了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2545555|不要误用主键作为记录顺序的指示符。如果您需要记录的特定order，那么为此使用一个额外的列。例如，一个timestamp列。|offset|length|style|BOLD|CODE|2545556|如果需要特定的记录顺序，请使用默认值为timestamp的current_timestamp列。这样它就会自动插入。|2545557|ALTER+TABLE+your_table
ADD+column+inserted_timestamp+TIMESTAMP+default+current_timestamp;|code-block|syntax|javascript|2545558|SQLFiddle演示|2545559|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!2/240ec/1^0|0|R|5|1F|9|0|J|9|T|H|0|0|0|B|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|10|8|@$D|11|E|12|F|G]|$D|13|E|14|F|H]]|9|@]|A|$]]|$1|I|3|J|5|6|7|15|8|@$D|16|E|17|F|H]|$D|18|E|19|F|H]]|9|@]|A|$]]|$1|K|3|L|5|M|7|1A|8|@]|9|@]|A|$N|O]]|$1|P|3|Q|5|6|7|1B|8|@]|9|@$D|1C|E|1D|1|1E]]|A|$]]|$1|R|3|-4|5|6|7|1F|8|@]|9|@]|A|$]]]|S|$T|$5|U|V|W|A|$X|Y]]]]

A primary auto-increment key is only for uniquely identifying a record. Just leave it be.

Don't misuse the primary key as indicator of your record order. If you need specific order of your records then use an extra column for that. For instance a <code>timestamp</code> column.

If you need a specific order of your records use a <code>timestamp</code> column with a default value of <code>current_timestamp</code>. That way it will be inserted automatically.

<pre><code>ALTER TABLE your_table
ADD column inserted_timestamp TIMESTAMP default current_timestamp;
</code></pre>

<h2><a href="http://sqlfiddle.com/#!2/240ec/1" rel="nofollow">SQLFiddle demo</a></h2>

blocks|key|1468669|text|您可以更改表和删除主键，然后再创建主键。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1468670|但你为什么需要这个。如果您已将此键用作其他表中的外键。然后你就失去了所有的数据。|1468671|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

You can do by alter the table and delete primary key then again create primary key.

But why you need this. If you have use this key as foreign key in other table. Then you lost all the data.

blocks|key|311041|text|你应该保持原样。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|311042|但是，如果确实需要，可以“重新计算”主键，即索引：|311043|set+@pk:=0;

update+
++your_table
++set+pk=@pk:=@pk%2B1
++order+by+pk;+++|code-block|syntax|javascript|311044|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|L|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|M|8|@]|9|@]|A|$G|H]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

You should leave it as it is.

However, if you do really need, you can "recalculate" the primary key, the index:

<pre><code>set @pk:=0;

update 
 your_table
 set pk=@pk:=@pk+1
 order by pk; 
</code></pre>

blocks|key|312125|text|添加将被删除的列。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|312126|示例：|312127|1-已删除|312128|0-未删除|312129|并在select查询中添加删除为0的位置|312130|primary+key+column+2+column3+.....+deleted
1++++++++++++++++++++++++++++++++++++1
2++++++++++++++++++++++++++++++++++++0
3++++++++++++++++++++++++++++++++++++0
4++++++++++++++++++++++++++++++++++++1
5++++++++++++++++++++++++++++++++++++0|code-block|syntax|javascript|312131|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|T|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|V|8|@]|9|@]|A|$M|N]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

add a column that will speicfy that is deleted

example:

1 - deleted already

0 - not deleted

and add where deleted = 0 in your select query

<pre><code>primary key column 2 column3 ..... deleted
1 1
2 0
3 0
4 1
5 0
</code></pre>

blocks|key|312138|text|存储大量的记录会导致删除效率低下。相反，您可以依赖现有的SLNO索引，这对于我所想到的所有用例来说都足够了。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|312139|如果您是SELECT+whatever+ORDER+BY+SLNO+LIMIT+...+OFFSET+k，那么返回的行具有ID+k、k%2B1、k%2B2、.|offset|length|style|CODE|312140|如果您想获得一个知道它的SLNO的记录id：|312141|SELECT+COUNT(SLNO)+FROM+A+WHERE+SLNO+<=+thatnumber|code-block|syntax|javascript|312142|如果您想获得thatnumber'th记录：|312143|SELECT+*+FROM+A+ORDER+BY+SLNO+LIMIT+1+OFFSET+thatnumber|312144|entityMap^0|0|4|1C|0|0|0|6|D|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|V|8|@$D|W|E|X|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|Y|8|@]|9|@]|A|$]]|$1|J|3|K|5|L|7|Z|8|@]|9|@]|A|$M|N]]|$1|O|3|P|5|6|7|10|8|@$D|11|E|12|F|G]]|9|@]|A|$]]|$1|Q|3|R|5|L|7|13|8|@]|9|@]|A|$M|N]]|$1|S|3|-4|5|6|7|14|8|@]|9|@]|A|$]]]|T|$]]

Storing an number of a record would make deletes inefficient. Instead you can rely on existing SLNO indexes you already have, that should be enough for all use cases that come up to my mind.

If you <code>SELECT whatever ORDER BY SLNO LIMIT ... OFFSET k</code>, then returned rows have IDs k, k+1, k+2, ...

If you want to get an id of a record knowing its SLNO:

<pre><code>SELECT COUNT(SLNO) FROM A WHERE SLNO &lt;= thatnumber
</code></pre>

If you want to get <code>thatnumber'th</code> record:

<pre><code>SELECT * FROM A ORDER BY SLNO LIMIT 1 OFFSET thatnumber
</code></pre>

I have a table A which has a auto increment serial number field SLNO. When i insert values in table it will increment automatically like 1,2,3,4... etc. But when i delete a row from the table the order get break. ie, if delete row with serial number 2 then the serial number field will 1,3,4. But I want to maintain a continuous order like 1,2,3 even delete rows. Is there any way to maintain this order, like using trigger or somthing

How to maintain proper number order in mysql auto increment field when delete rows

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

我有一个表A，它有一个自动增量序列号字段SLNO。当我在表中插入值时，它会自动增加，如1，2，3，4.等等，但当我从表中删除一行时，订单就会中断。如果删除带有序列号2的行，则序列号字段将为1,3,4。但我希望保持连续的顺序，比如1、2、3甚至删除行。有什么办法来维持这个秩序吗?比如使用触发器或其他的

问删除行时如何在mysql自动增量字段中保持正确的编号顺序
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问删除行时如何在mysql自动增量字段中保持正确的编号顺序EN