mysql中删除后自动递增

hrirmatl  于 2021-06-20  发布在  Mysql
关注(0)|答案(17)|浏览(420)

我有一个mysql表,它有一个主键字段,可以自动递增。在阅读了这里的其他帖子后,我注意到人们有着同样的问题和不同的答案。有些人建议不要使用此功能,有些人则表示无法“修复”。
我有:

table: course
fields: courseID, courseName

示例:表中的记录数:18。如果我删除记录16、17和18,我希望输入的下一个记录的courseid是16,但是它将是19,因为最后输入的courseid是18。
我的sql知识并不惊人,但是是否有任何方法可以通过查询(或phpmyadmin接口中的设置)刷新或更新此计数?
此表将与数据库中的其他表相关。
考虑到所有的建议,我决定忽略这个“问题”。我将简单地删除和添加记录,同时让自动增量做它的工作。我猜数字是什么其实并不重要,因为它只是作为一个唯一的标识符使用,没有(如上所述)商业意义。
对于那些我可能对我原来的帖子感到困惑的人:我不想用这个字段来知道我有多少记录。我只是想让数据库看起来整洁一点,保持一致性。

bbuxkriu

bbuxkriu1#

这里有一个函数可以解决您的问题

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 <= 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();
    }
}
vwkv1x7d

vwkv1x7d2#

您可以使用mysql客户机软件/脚本来指定删除所需记录后主键的起始位置。

v8wbuo2f

v8wbuo2f3#

我可以想到很多可能需要这样做的场景,特别是在迁移或开发过程中。例如,我刚才必须通过交叉连接两个现有表来创建一个新表(作为复杂设置过程的一部分),然后我需要在事件之后添加一个主键。可以删除现有的主键列,然后执行此操作。

ALTER TABLE my_table ADD `ID` INT NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (`ID`);

对于一个活动系统,这不是一个好主意,尤其是如果有其他表的外键指向它。

ngynwnxp

ngynwnxp4#

您可以这样选择ID:

set @rank = 0;
select id, @rank:=@rank+1 from tbl order by id

结果是一个ID列表,以及它们在序列中的位置。
您还可以重置ID,如下所示:

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;

您也可以打印出第一个未使用的id,如下所示:

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;

每次插入后,可以重置自动增量:

alter table tbl auto_increment = 16

或在执行插入时显式设置id值:

insert into tbl values (16, 'something');

通常这是没有必要的,你有 count(*) 以及在结果集中创建排名数字的能力。一个典型的排名可能是:

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

按消费金额排序的客户。

olhwl3o2

olhwl3o25#

ALTER TABLE foo AUTO_INCREMENT=1

如果您删除了最近的条目,那么应该将其设置为使用下一个最低的可用条目。如中所示,只要没有19,删除16-18就会将自动增量重置为使用16。
编辑:我错过了关于phpmyadmin的一点。你也可以把它放在那里。转到“表”屏幕,然后单击“操作”选项卡。有一个 AUTOINCREMENT 字段,您可以手动设置为所需的任何值。

gpfsuwkq

gpfsuwkq6#

您不应该依赖auto\u increment id来告诉您表中有多少条记录。你应该使用 SELECT COUNT(*) FROM course . id用于唯一标识课程,并且可以用作其他表中的引用,因此不应重复id,也不应试图重置自动增量字段。

ct3nt3jp

ct3nt3jp7#

我来这里是想找到标题问题的答案 "MySQL - Auto Increment after delete" 但我只能在问题中找到答案
如何从mysql表中删除某一行?
如何在mysql中重置自动增量?
使用类似于:

DELETE FROM table;
ALTER TABLE table AUTO_INCREMENT = 1;

请注意,darindimitrov的回答解释得非常好 AUTO_INCREMENT 这就是用法。在做一些你可能会后悔的事情之前先看看那里。
附言:问题本身更重要 "Why you need to recycle key values?" 多尔夫的回答涵盖了这一点。

yshpjwxd

yshpjwxd8#

我有一个很简单但很棘手的方法。
删除行时,可以将id保存到另一个临时表中。之后,当您将新数据插入到主表中时,您可以从临时表中搜索和选择id。所以在这里用支票。如果临时表没有id,则在主表中计算最大id,并将新id设置为: new_ID = old_max_ID+1 .
注意:这里不能使用自动递增功能。

xhv8bpkk

xhv8bpkk9#

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");
hwazgwia

hwazgwia10#

您可以考虑在delete之后做一个触发器,这样就可以更新autoincrement的值和所有看起来不像您希望看到的行的id值。
因此,您可以使用同一个表,并且自动增量将被自动修复每当您删除一行时,触发器将修复它。

vmpqdwk3

vmpqdwk311#

数据库中的主自动增量键用于唯一标识给定的行,不应赋予任何业务含义。因此,保持主键不变,并添加另一个名为example的列 courseOrder . 然后,当您从数据库中删除一条记录时,您可能需要发送一条额外的update语句以减少 courseOrder 列中包含 courseOrder 大于当前删除的值。
顺便说一句,您永远不应该修改关系数据库中主键的值,因为可能有其他表将其作为外键引用,修改它可能会违反引用约束。

omtl5h9j

omtl5h9j12#

这绝对不值得推荐。如果您有一个包含多个表的大型数据库,则可能在表2中保存了一个userid作为id。如果重新排列表1,那么预期的userid可能不会最终成为预期的表2 id。

30byixjq

30byixjq13#

你想做的事很危险。仔细想想。自动增量的默认行为有一个很好的理由。
考虑一下:
在与另一个表有关系的表中删除记录。由于审核原因,无法删除第二个表中的相应记录。此记录从第一个表变成孤立的。如果在第一个表中插入了一条新记录,并且使用了一个顺序主键,则该记录现在链接到孤立项。显然,这很糟糕。通过使用自动递增的pk,以前从未使用过的id总是可以得到保证。这意味着孤儿仍然是孤儿,这是正确的。

wgeznvg7

wgeznvg714#

mysql查询自动增量解决方案。当您在软件测试阶段插入了许多记录时,它工作得非常完美。现在,您希望向客户端实时启动应用程序,并希望从1开始自动递增。
为避免任何不必要的问题,为安全起见先出口 .sql 文件。
然后执行以下步骤:
步骤1)首先创建现有表的副本mysql命令创建副本:

CREATE TABLE new_Table_Name  SELECT * FROM existing_Table_Name;

使用除约束以外的所有行创建表的精确副本。
它不会将自动增量和主键之类的约束复制到 new_Table_name 步骤2)如果在测试阶段没有插入数据并且数据无效,则删除所有行。如果数据很重要,则直接转到步骤3。

DELETE from new_Table_Name;

步骤3)要添加约束,转到表的结构
3a)从更多选项添加主键约束(如果需要)。
3b)从更改选项添加自动增量约束。对于此集合,定义值为 None .
3c)删除现有的表名称和
3d)将新的\u表\u名称重命名为现有的\u表\u名称。
现在它将完美地工作。新的第一条记录将采用自动递增列中的第一个值。

s8vozzvw

s8vozzvw15#

尝试:
设置@num:=0;
更新您的表集合id=@num:=(@num+1);
更改表格 tableName 自动增量=1;
这将重置自动递增的值,然后在为其创建新值时对每一行进行计数。
示例:之前
1:这里第一个值
2:这里是第二个值
x:删除值
4:剩下的table
5:剩下的。。
因此该表将显示数组:1,2,4,5
示例:after(如果使用此命令,将获得)
1:这里第一个值
2:这里是第二个值
3:剩下的table
4:剩下的
没有被删除的值的踪迹,其余的递增值继续这个新计数。
但是
如果代码中的某个地方使用了自动递增的值。。。也许这个归因会引起问题。
如果在代码中不使用这个值,一切都应该正常。

相关问题