Oracle:max(id)+1和sequence.nextval之间的区别 [英] Oracle: difference between max(id)+1 and sequence.nextval

问题描述

我正在使用Oracle

I am using Oracle

当我们使用max(id)+1和sequance.nexval创建ID时，有何不同之处?何时使用?

What is difference when we create ID using max(id)+1 and using sequance.nexval,where to use and when?

赞:

insert into student (id,name) values (select max(id)+1 from student, 'abc');

和

insert into student (id,name) values (SQ_STUDENT.nextval, 'abc');

SQ_STUDENT.nextval有时会出现重复记录的错误...

SQ_STUDENT.nextval sometime gives error that duplicate record...

请帮助我解决这个疑问

推荐答案

使用select max(id) + 1方法，同时插入两个会话将在表中看到相同的当前最大ID，并且都插入相同的新ID值.安全使用此方法的唯一方法是在开始事务之前锁定表，这很痛苦并且会序列化事务. (正如Stijn所指出的，如果删除最高记录，则可以重用值).基本上，永远不要使用这种方法. (这样做有时可能有令人信服的理由，但我不确定我是否见过).

With the select max(id) + 1 approach, two sessions inserting simultaneously will see the same current max ID from the table, and both insert the same new ID value. The only way to use this safely is to lock the table before starting the transaction, which is painful and serialises the transactions. (And as Stijn points out, values can be reused if the highest record is deleted). Basically, never use this approach. (There may very occasionally be a compelling reason to do so, but I'm not sure I've ever seen one).

序列可确保两个会话获得不同的值 ，不需要序列化.它将执行得更好，更安全，更容易编写代码并且更易于维护.

The sequence guarantees that the two sessions will get different values, and no serialisation is needed. It will perform better and be safer, easier to code and easier to maintain.

使用序列可以得到重复错误的唯一方法是，表中是否已经存在ID高于序列值的记录，或者是否仍在不使用序列的情况下插入记录.因此，如果您有一个具有手动输入的ID(例如1到10)的表，并且创建了一个默认起始值​​为1的序列，则使用该序列的第一个插入将尝试插入ID 1-该ID已经存在.尝试10次后，该序列将为您提供11，这将起作用.如果随后使用max-ID方法执行下一个将使用12的插入操作，但是该序列仍然在11上，并且下次您调用nextval时也会给您12.

The only way you can get duplicate errors using the sequence is if records already exist in the table with IDs above the sequence value, or if something is still inserting records without using the sequence. So if you had an existing table with manually entered IDs, say 1 to 10, and you created a sequence with a default start-with value of 1, the first insert using the sequence would try to insert an ID of 1 - which already exists. After trying that 10 times the sequence would give you 11, which would work. If you then used the max-ID approach to do the next insert that would use 12, but the sequence would still be on 11 and would also give you 12 next time you called nextval.

序列和表格无关.如果将手动生成的ID值插入表中，则序列不会自动更新，因此这两种方法不会混合使用. (此外，如文档中所述，相同的序列可用于为多个表生成ID.)

The sequence and table are not related. The sequence is not automatically updated if a manually-generated ID value is inserted into the table, so the two approaches don't mix. (Among other things, the same sequence can be used to generate IDs for multiple tables, as mentioned in the docs).

如果您正在从手动方法更改为序列方法，则需要确保创建的序列的起始值高于表中所有现有的ID，并且所有执行插入操作的值

If you're changing from a manual approach to a sequence approach, you need to make sure the sequence is created with a start-with value that is higher than all existing IDs in the table, and that everything that does an insert uses the sequence only in the future.

这篇关于Oracle:max(id)+1和sequence.nextval之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！