blocks|key|1813329|text|您可以按如下方式选择ids：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1813330|set+@rank+=+0;
select+id,+@rank:=@rank%2B1+from+tbl+order+by+id|code-block|syntax|javascript|1813331|结果是一个in列表，以及它们在序列中的位置。|1813332|您还可以重置ids，如下所示：|1813333|set+@rank+=+0;
update+tbl+a+join+(select+id,+@rank:=@rank%2B1+as+rank+from+tbl+order+by+id)+b
++on+a.id+=+b.id+set+a.id+=+b.rank;|1813334|您也可以打印出第一个未使用的id，如下所示：|1813335|select+min(id)+as+next_id+from+((select+a.id+from+(select+1+as+id)+a
++left+join+tbl+b+on+a.id+=+b.id+where+b.id+is+null)+union
++(select+min(a.id)+%2B+1+as+id+from+tbl+a+left+join+tbl+b+on+a.id%2B1+=+b.id
++where+b.id+is+null))+c;|1813336|每次插入后，您都可以重置auto_increment：|1813337|alter+table+tbl+auto_increment+=+16|1813338|或者在执行插入操作时显式设置id值：|1813339|insert+into+tbl+values+(16,+'something');|1813340|通常这不是必需的，您有count(*)并且能够在您的结果集中创建一个排名号。典型的排名可能是：|offset|length|style|CODE|1813341|set+@rank+=+0;
select+a.name,+a.amount,+b.rank+from+cust+a,
++(select+amount,+@rank:=@rank%2B1+as+rank+from+cust+order+by+amount+desc)+b
++where+a.amount+=+b.amount|1813342|按花费金额排名的客户。|1813343|entityMap^0|0|0|0|0|0|0|0|0|0|0|0|B|8|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|1B|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|1C|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|1D|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|1E|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|1F|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|1G|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|1H|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|1I|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|1J|8|@]|9|@]|A|$]]|$1|W|3|X|5|D|7|1K|8|@]|9|@]|A|$E|F]]|$1|Y|3|Z|5|6|7|1L|8|@$10|1M|11|1N|12|13]]|9|@]|A|$]]|$1|14|3|15|5|D|7|1O|8|@]|9|@]|A|$E|F]]|$1|16|3|17|5|6|7|1P|8|@]|9|@]|A|$]]|$1|18|3|-4|5|6|7|1Q|8|@]|9|@]|A|$]]]|19|$]]

you can select the ids like so:

<pre><code>set @rank = 0;
select id, @rank:=@rank+1 from tbl order by id
</code></pre>

the result is a list of ids, and their positions in the sequence.

you can also reset the ids like so:

<pre><code>set @rank = 0;
update tbl a join (select id, @rank:=@rank+1 as rank from tbl order by id) b
 on a.id = b.id set a.id = b.rank;
</code></pre>

you could also just print out the first unused id like so:

<pre><code>select min(id) as next_id from ((select a.id from (select 1 as id) a
 left join tbl b on a.id = b.id where b.id is null) union
 (select min(a.id) + 1 as id from tbl a left join tbl b on a.id+1 = b.id
 where b.id is null)) c;
</code></pre>

after each insert, you can reset the auto_increment:

<pre><code>alter table tbl auto_increment = 16
</code></pre>

or explicitly set the id value when doing the insert:

<pre><code>insert into tbl values (16, 'something');
</code></pre>

typically this isn't necessary, you have <code>count(*)</code> and the ability to create a ranking number in your result sets. a typical ranking might be:

<pre><code>set @rank = 0;
select a.name, a.amount, b.rank from cust a,
 (select amount, @rank:=@rank+1 as rank from cust order by amount desc) b
 where a.amount = b.amount
</code></pre>

customers ranked by amount spent.

blocks|key|2026663|text|数据库中的主自增量键用于唯一标识给定行，不应赋予任何业务意义。因此，保留主键不变，并添加另一个名为courseOrder的列。然后，当您从数据库中删除一条记录时，您可能想要发送一个额外的UPDATE语句，以便递减所有行的courseOrder列，这些行的courseOrder大于当前要删除的行。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2026664|另外，您永远不应该修改关系数据库中主键的值，因为可能有其他表将其引用为外键，而修改它可能违反引用约束。|2026665|entityMap^0|1D|B|32|B|3J|B|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@$9|K|A|L|B|C]|$9|M|A|N|B|C]|$9|O|A|P|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|Q|8|@]|D|@]|E|$]]|$1|H|3|-4|5|6|7|R|8|@]|D|@]|E|$]]]|I|$]]

Primary autoincrement keys in database are used to uniquely identify a given row and shouldn't be given any business meaning. So leave the primary key as is and add another column called for example <code>courseOrder</code>. Then when you delete a record from the database you may want to send an additional UPDATE statement in order to decrement the <code>courseOrder</code> column of all rows that have <code>courseOrder</code> greater than the one you are currently deleting.

As a side note you should never modify the value of a primary key in a relational database because there could be other tables that reference it as a foreign key and modifying it might violate referential constraints.

blocks|key|1813500|text|您可以使用mysql客户端软件/脚本指定删除所需记录后主键的起始位置。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1813501|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

You can use your mysql client software/script to specify where the primary key should start from after deleting the required records.

blocks|key|2026814|text|您不应该依赖AUTO_INCREMENT+id来告诉您表中有多少条记录。您应该使用SELECT+COUNT(*)+FROM+course。ID用于唯一标识课程，并可用作其他表中的引用，因此您不应重复ID，也不应尝试重置自动递增字段。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2026815|entityMap^0|15|R|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

You shouldn't be relying on the AUTO_INCREMENT id to tell you how many records you have in the table. You should be using <code>SELECT COUNT(*) FROM course</code>. ID's are there to uniquely identifiy the course and can be used as references in other tables, so you shouldn't repeat ids and shouldn't be seeking to reset the auto increment field.

blocks|key|2026888|text|您尝试做的事情听起来很危险，因为这不是AUTO_INCREMENT的预期用途。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2026889|如果你真的想找到最小的未使用的密钥值，那就完全不要使用AUTO_INCREMENT，手动管理你的密钥值。但是，这不是推荐的做法。|2026890|退一步，问一问“为什么需要回收键值？”无符号INT+(或BIGINT)是否没有提供足够大的密钥空间？|2026891|在您的应用程序的生命周期中，您真的会拥有超过18,446,744,073,709,551,615的唯一记录吗？|2026892|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.0/en/numeric-type-attributes.html^0|J|E|0|R|E|0|M|3|S|6|0|M|Q|M|Q|0|0^^$0|@$1|2|3|4|5|6|7|T|8|@$9|U|A|V|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]]|D|@]|E|$]]|$1|J|3|K|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@$9|17|A|18|1|19]]|E|$]]|$1|L|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]]|M|$N|$5|O|P|Q|E|$R|S]]]]

What you're trying to do sounds dangerous, as that's not the intended use of <code>AUTO_INCREMENT</code>.

If you really want to find the lowest unused key value, don't use <code>AUTO_INCREMENT</code> at all, and manage your keys manually. However, this is NOT a recommended practice.

Take a step back and ask "why you need to recycle key values?" Do unsigned <code>INT</code> (or <code>BIGINT</code>) not provide a large enough key space? 

Are you really going to have more than <a href="http://dev.mysql.com/doc/refman/5.0/en/numeric-type-attributes.html" rel="noreferrer"><code>18,446,744,073,709,551,615</code></a> unique records over the course of your application's lifetime?

blocks|key|2026961|text|我有一个非常简单但很棘手的方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2026962|删除行时，可以将ID保留到另一个临时表中。在此之后，当您将新数据插入到主表中时，您可以从临时表中搜索和挑选ID。所以在这里使用检查。如果临时表没有ID，那么在主表中计算最大ID，并将新ID设置为：new_ID+=+old_max_ID%2B1。|offset|length|style|CODE|2026963|注意:这里不能使用自动增量功能。|2026964|entityMap^0|0|2Q|L|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@$D|N|E|O|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

I got a very simple but tricky method. 

While deleting a row, you can preserve the IDs into another temporary table. After that, when you will insert new data into the main table then you can search and pick IDs from the temporary table. So use a checking here. If the temporary table has no IDs then calculate maximum ID into the main table and set the new ID as: <code>new_ID = old_max_ID+1</code>.

NB: You can not use auto-increment feature here.

blocks|key|1813720|text|你正在尝试做的事情是非常危险的。请仔细考虑这一点。自动递增的默认行为有一个非常好的原因。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1813721|请考虑以下内容：|1813722|删除一个与另一个表有关系的表中的记录。由于审计原因，第二个表中的对应记录不能删除。此记录将从第一个表中变为孤立记录。如果将新记录插入到第一个表中，并且使用了顺序主键，则此记录现在将链接到孤立记录。显然，这很糟糕。通过使用自动递增的PK，可以保证以前从未使用过的id。这意味着孤儿仍然是孤儿，这是正确的。|1813723|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

What you are trying to do is very dangerous. Think about this carefully. There is a very good reason for the default behaviour of auto increment. 

Consider this:

A record is deleted in one table that has a relationship with another table. The corresponding record in the second table cannot be deleted for auditing reasons. This record becomes orphaned from the first table. If a new record is inserted into the first table, and a sequential primary key is used, this record is now linked to the orphan. Obviously, this is bad. By using an auto incremented PK, an id that has never been used before is always guaranteed. This means that orphans remain orphans, which is correct.

blocks|key|2026983|text|我可以想到很多场景，您可能需要这样做，特别是在迁移或开发过程中。例如，我刚才必须通过交叉连接两个现有的表来创建一个新表(作为复杂设置过程的一部分)，然后我需要在事件之后添加一个主键。您可以删除现有的主键列，然后执行此操作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2026984|ALTER+TABLE+my_table+ADD+`ID`+INT+NOT+NULL+AUTO_INCREMENT+FIRST,+ADD+PRIMARY+KEY+(`ID`);|code-block|syntax|javascript|2026985|对于活动系统，这不是一个好主意，尤其是当有其他表具有指向它的外键时。|2026986|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|K|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|L|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|M|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|J|$]]

I can think of plenty of scenarios where you might need to do this, particularly during a migration or development process. For instance, I just now had to create a new table by cross-joining two existing tables (as part of a complex set-up process), and then I needed to add a primary key after the event. You can drop the existing primary key column, and then do this.

<pre><code>ALTER TABLE my_table ADD `ID` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`ID`);
</code></pre>

For a live system, it is not a good idea, and especially if there are other tables with foreign keys pointing to it.

blocks|key|2027092|text|实际上，有一种方法可以解决这个问题。首先删除auto_incremented主键列，然后重新添加它，如下所示：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2027093|ALTER+TABLE+table_name+DROP+column_name;
ALTER+TABLE+table_name+ADD+column_name+int+not+null+auto_increment+primary+key+first;|code-block|syntax|javascript|2027094|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

There is actually a way to fix that. First you delete the auto_incremented primary key column, and then you add it again, like this:

<pre><code>ALTER TABLE table_name DROP column_name;
ALTER TABLE table_name ADD column_name int not null auto_increment primary key first;
</code></pre>

blocks|key|2027114|text|if($id+==+1){+//+deleting+first+row
++++++++++++mysqli_query($db,"UPDATE+employees++SET+id=id-1+WHERE+id>1");
++++++++}
++++++++else+if($id>1+&&+$id<$num){+//+deleting+middle+row
++++++++++++mysqli_query($db,"UPDATE+employees++SET+id=id-1+WHERE+id>$id");
++++++++}
++++++++else+if($id+==+$num){+//+deleting+last+row
++++++++++++mysqli_query($db,"ALTER+TABLE+employees+AUTO_INCREMENT+=+$num");
++++++++}
++++++++else{
++++++++++++echo+"ERROR";
++++++++}

++++++++mysqli_query($db,"ALTER+TABLE+employees+AUTO_INCREMENT+=+$num");|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2027115|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>if($id == 1){ // deleting first row
 mysqli_query($db,"UPDATE employees SET id=id-1 WHERE id&gt;1");
 }
 else if($id&gt;1 &amp;&amp; $id&lt;$num){ // deleting middle row
 mysqli_query($db,"UPDATE employees SET id=id-1 WHERE id&gt;$id");
 }
 else if($id == $num){ // deleting last row
 mysqli_query($db,"ALTER TABLE employees AUTO_INCREMENT = $num");
 }
 else{
 echo "ERROR";
 }

 mysqli_query($db,"ALTER TABLE employees AUTO_INCREMENT = $num");
</code></pre>

blocks|key|2027248|text|您可能会考虑在delete之后创建一个触发器，这样您就可以更新自动增量的值和所有看起来不像您希望看到的行的ID值。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2027249|因此，您可以使用相同的表，自动增量将自动修复，只要您删除一行，触发器就会修复它。|2027250|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 may think about making a trigger after delete so you can update the value of autoincrement and the ID value of all rows that does not look like what you wanted to see.

So you can work with the same table and the auto increment will be fixed automaticaly whenever you delete a row the trigger will fix it.

blocks|key|2027469|text|这绝对不值得推荐。如果您有一个包含多个表的大型数据库，您可能已经将一个userid作为id保存在表2中。如果您重新排列表1，那么可能预期的userid最终将不是预期的表2id。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2027470|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Its definitely not recommendable. If you have a large database with multiple tables, you may probably have saved a userid as id in table 2. if you rearrange table 1 then probably the intended userid will not end up being the intended table 2 id.

blocks|key|2027544|text|下面是一个解决问题函数|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2027545|++++public+static+void+fixID(Connection+conn,+String+table)+{

++++try+{
++++++++Statement+myStmt+=+conn.createStatement();
++++++++ResultSet+myRs;
++++++++int+i+=+1,+id+=+1,+n+=+0;
++++++++boolean+b;
++++++++String+sql;

++++++++myRs+=+myStmt.executeQuery("select+max(id)+from+"+%2B+table);
++++++++if+(myRs.next())+{
++++++++++++n+=+myRs.getInt(1);
++++++++}
++++++++while+(i+<=+n)+{
++++++++++++b+=+false;
++++++++++++myRs+=+null;
++++++++++++while+(!b)+{
++++++++++++++++myRs+=+myStmt.executeQuery("select+id+from+"+%2B+table+%2B+"+where+id="+%2B+id);
++++++++++++++++if+(!myRs.next())+{
++++++++++++++++++++id%2B%2B;
++++++++++++++++}+else+{
++++++++++++++++++++b+=+true;
++++++++++++++++}
++++++++++++}

++++++++++++sql+=+"UPDATE+"+%2B+table+%2B+"+set+id+="+%2B+i+%2B+"+WHERE+id="+%2B+id;
++++++++++++myStmt.execute(sql);
++++++++++++i%2B%2B;
++++++++++++id%2B%2B;
++++++++}

++++}+catch+(SQLException+e)+{
++++++++e.printStackTrace();
++++}
}|code-block|syntax|javascript|2027546|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

here is a function that fix your problem

<pre><code> public static void fixID(Connection conn, String table) {

 try {
 Statement myStmt = conn.createStatement();
 ResultSet myRs;
 int i = 1, id = 1, n = 0;
 boolean b;
 String sql;

 myRs = myStmt.executeQuery("select max(id) from " + table);
 if (myRs.next()) {
 n = myRs.getInt(1);
 }
 while (i &lt;= n) {
 b = false;
 myRs = null;
 while (!b) {
 myRs = myStmt.executeQuery("select id from " + table + " where id=" + id);
 if (!myRs.next()) {
 id++;
 } else {
 b = true;
 }
 }

 sql = "UPDATE " + table + " set id =" + i + " WHERE id=" + id;
 myStmt.execute(sql);
 i++;
 id++;
 }

 } catch (SQLException e) {
 e.printStackTrace();
 }
}
</code></pre>

blocks|key|2027638|text|这是解决你问题的一个步骤。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2027639|在您的.php文件中，只需添加如下所示的查询：|offset|length|style|CODE|2027640|<?php
$servername+=+"localhost";
$username+=+"root";
$password+=+"";
$dbname+=+"";

$conn+=+new+mysqli($servername,+$username,+$password,+$dbname);

if+($conn->connect_error)+{
++++++die("Connection+failed:+"+.+$conn->connect_error);
}

//write+the+number+or+id+you+want+to+start+with+the+next+user+in+AUTO_INCREMENT++++
$sql+=+"ALTER+TABLE+`table_name`+AUTO_INCREMENT+=+number";
$conn->query($sql);
?>+|code-block|syntax|javascript|2027641|我希望你的问题能得到解决。|2027642|entityMap^0|0|3|4|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Here is a step to solve your problem.
On your <code>.php</code> file, just add this query given below:
<pre class="lang-php prettyprint-override"><code>&lt;?php
$servername = &quot;localhost&quot;;
$username = &quot;root&quot;;
$password = &quot;&quot;;
$dbname = &quot;&quot;;

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn-&gt;connect_error) {
 die(&quot;Connection failed: &quot; . $conn-&gt;connect_error);
}

//write the number or id you want to start with the next user in AUTO_INCREMENT 
$sql = &quot;ALTER TABLE `table_name` AUTO_INCREMENT = number&quot;;
$conn-&gt;query($sql);
?&gt; 
</code></pre>
I hope your problem will be solved.

I have a MySQL table with a primary key field that has AUTO_INCREMENT on.
After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state it can't be 'fixed'.

I have:

<pre><code>table: course
fields: courseID, courseName
</code></pre>

Example: number of records in the table: 18. If I delete records 16, 17 and 18 - I would expect the next record entered to have the courseID of 16, however it will be 19 because the last entered courseID was 18.

My SQL knowledge isn't amazing but is there anyway to refresh or update this count with a query (or a setting in the phpMyAdmin interface)?

This table will relate to others in a database.

<hr>

Given all the advice, I have decided to ignore this 'problem'. I will simply delete and add records whilst letting the auto increment do it's job. I guess it doesn't really matter what the number is since it's only being used as a unique identifier and doesn't have a (as mentioned above) business meaning.

For those who I may have confused with my original post: I do not wish to use this field to know how many records I have. I just wanted the database to look neat and have a bit more consistency.

Auto Increment after delete in MySQL

数据库

MySQL

我有一个主键字段为AUTO_INCREMENT on的MySQL表。在阅读了这里的其他帖子后，我注意到有些人也有同样的问题，但答案却各不相同。一些人建议不要使用这个功能，另一些人则认为它不能被“修复”。我有：table: coursefields: courseID, courseName示例:表中的记录数: 18。如...

问在MySQL中删除后自动增加
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中删除后自动增加EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在MySQL中删除后自动增加
EN