@@identity和LAST_INSERT_ID()有什么区别么

Kael Z 2013-12-03 05:53:21
RT:今天找了一些资料，说的都不太一样，有的人说@@inentity是系统全局的而LAST_INSERT_ID()是针对每个Connection的，有的说这两个是一个东西的，还有的说是当INSERT多条记录的时候这两个函数返回的结果是不一样的，求大神指点下，或者共享个相关的文章资料也可以哈
...全文
449 7 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
Kael Z 2013-12-04
  • 打赏
  • 举报
 回复
引用 6 楼 iihero 的回复:
[quote=引用 4 楼 hunterdahe 的回复:] [quote=引用 2 楼 iihero 的回复:] mysql里头也有. 楼主不妨直接查询mysql的官方文档. mysql5.5的chm帮助是这么说的: identity This variable is a synonym for the last_insert_id variable. It exists for compatibility with other database systems. You can read its value with SELECT @@identity, and set it using SET identity. 再看看: last_insert_id The value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function. 这两者是完全一样的.
哦，谢谢，今天下午我看到有人说identity是跟last_insert_id的区别是如果出现insert多条记录的时候，一个是取这个事务的最前面的哪个，一个是取这个事务的最后一个，存在这种情况么？[/quote] 汗,你说的如果是SQL Server, 还有点像,但你发的帖是在MySQL版. 所以,不存在这种情况. 再说一遍, identity 是 last_insert_id的同义词. [/quote] 嗯，就是问mysql的，呵呵，因为网上有很多都说mysql也是这么区分的，所以我有点没谱了，说法太多了，但是昨天我用自己的库测了下发现这俩基本是一个东西，identity 也不像是个全局的，因为新建个客户端查询的identity 就是0了，所以不放心就问了下，谢谢啦
iihero 2013-12-04
  • 打赏
  • 举报
 回复
引用 4 楼 hunterdahe 的回复:
[quote=引用 2 楼 iihero 的回复:] mysql里头也有. 楼主不妨直接查询mysql的官方文档. mysql5.5的chm帮助是这么说的: identity This variable is a synonym for the last_insert_id variable. It exists for compatibility with other database systems. You can read its value with SELECT @@identity, and set it using SET identity. 再看看: last_insert_id The value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function. 这两者是完全一样的.
哦，谢谢，今天下午我看到有人说identity是跟last_insert_id的区别是如果出现insert多条记录的时候，一个是取这个事务的最前面的哪个，一个是取这个事务的最后一个，存在这种情况么？[/quote] 汗,你说的如果是SQL Server, 还有点像,但你发的帖是在MySQL版. 所以,不存在这种情况. 再说一遍, identity 是 last_insert_id的同义词.
Rotel-刘志东 2013-12-03
  • 打赏
  • 举报
 回复
从本身用法是没有什么区别的。 唯一区别的@@identity是sql server ,而last_insert_id() 是my sql,identity是跟last_insert_id的区别是如果出现insert多条记录的时候，一个是取这个事务的最前面的哪个，一个是取这个事务的最后一个. last_insert_id()是取数据最后一下。而@@identity是顺序取数据的。
Kael Z 2013-12-03
  • 打赏
  • 举报
 回复
引用 2 楼 iihero 的回复:
mysql里头也有. 楼主不妨直接查询mysql的官方文档. mysql5.5的chm帮助是这么说的: identity This variable is a synonym for the last_insert_id variable. It exists for compatibility with other database systems. You can read its value with SELECT @@identity, and set it using SET identity. 再看看: last_insert_id The value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function. 这两者是完全一样的.
哦，谢谢，今天下午我看到有人说identity是跟last_insert_id的区别是如果出现insert多条记录的时候，一个是取这个事务的最前面的哪个，一个是取这个事务的最后一个，存在这种情况么？
ACMAIN_CHM 2013-12-03
  • 打赏
  • 举报
 回复
一样，没什么差别。
iihero 2013-12-03
  • 打赏
  • 举报
 回复
mysql里头也有. 楼主不妨直接查询mysql的官方文档. mysql5.5的chm帮助是这么说的: identity This variable is a synonym for the last_insert_id variable. It exists for compatibility with other database systems. You can read its value with SELECT @@identity, and set it using SET identity. 再看看: last_insert_id The value to be returned from LAST_INSERT_ID(). This is stored in the binary log when you use LAST_INSERT_ID() in a statement that updates a table. Setting this variable does not update the value returned by the mysql_insert_id() C API function. 这两者是完全一样的.
rucypli 2013-12-03
  • 打赏
  • 举报
 回复
@@inentity是sqlserver的吧
使用SQL Server 获取插入记录后的ID(自动编号)
最近在开发项目的过程中遇到一个问题，就是在插入一条记录的后要立即获取所在数据库中ID，而该ID是自增的，怎么做？在sql server 2005中有几种方式可以实现。 要获取此ID,最简单的方法就是在查询之后select @@indentity –SQL语句创建数据库和表 代码如下: create database dbdemo go use dbdemo go create table tbldemo (     id int primary key identity(1,1),     name varchar(20) ) go –执行下面SQL语句就能查出来刚插入记录对应的自增列的值
获取插入记录ID(2.0)
获取新插入记录的标识号
解析sql中得到刚刚插入的数据的id
LAST_INSERT_ID:LAST_INSERT_ID 是与table无关的，如果向表a插入数据后，再向表b插入数据，LAST_INSERT_ID会改变。在多用户交替插入数据的情况下max(id)显然不能用。这就该使用LAST_INSERT_ID了，因为LAST_INSERT_ID是基于Connection的，只要每个线程都使用独立的 Connection对象，LAST_INSERT_ID函数将返回该Connection对AUTO_INCREMENT列最新的insert or update*作生成的第一个record的ID。这个值不能被其它客户端（Connection）影响，保证了你能够找
java获取新insert数据自增id的实现方法
主要介绍了java获取新insert数据自增id的实现方法,在具体生成id的时候，我们的操作顺序一般是：先在主表中插入记录，然后获得自动生成的id，以它为基础插入从表的记录,需要的朋友可以参考下
sql游标实例，更新行
USE AA if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Last]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tmp_Last] CREATE TABLE [tmp_Last] ( [FEntryID] [int] NOT NULL , [FBillNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [FNumber] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [FQty] [decimal](18, 4) NOT NULL , [FBatchNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [FSPID] [int] NOT NULL ) ON [PRIMARY] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Source]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tmp_Source] if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Batch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[tmp_Batch] Select nOutBillSeq As FEntryID , cOutBillNo As FBillNo , FNumber , nQua As FQty , FStockPlaceID As FSPID Into tmp_Source From Czq092801 Select FNumber ,FBatchNo , FQty , FStockID , FStockPlaceID As FSPID Into tmp_Batch From Czq092802 ALTER TABLE tmp_Batch ADD FDetailID int IDENTITY (1, 1) NOT NULL go Update tmp_Batch Set FSPID = 464 where FNumber = 'E.A.AZ9719410054' Update tmp_Batch Set FSPID = 464 where FNumber = 'E.A.880410054' Update tmp_Batch Set FSPID = 465 Where fnumber = 'E.A.AZ9719410054' Update tmp_Source Set FSPID = t.FSPID From tmp_Batch t Where tmp_Source.FNumber = t.FNumber --以下是把批号拆分的游标 declare @EntryID int --定义变量行号 declare @BillNo varchar(100) --定义变量单据号 declare @Number varchar(100) --定义变量物料代码 declare @qty decimal(18,6) --定义变量数量 declare @SPID int --定义变量仓位代码 declare @BatchNo varchar(100) --定义变量批号 declare @DID int declare @BQty decimal(18,6) Declare subgroup Cursor For Select FEntryID, FBillNo , FNumber , FQty , FSPID From tmp_Source Open subgroup Fetch Next From subgroup Into @EntryID , @BillNo , @Number , @qty , @SPID While @@FETCH_STATUS = 0 Begin re: set @DID = 0 set @DID = (Select min(FDetailID) From tmp_Batch Where FNumber = @Number And FSPID = @SPID And FQty <> 0 ) set @BatchNo =(Select FBatchNo From tmp_Batch Where FDetailID = @DID ) Set @BQty =(Select FQty From tmp_Batch Where FDetailID = @DID ) print @BillNo print @EntryID if @BQty >= @Qty Begin Insert Into tmp_Last values (@EntryID , @BillNo , @Number , @Qty , @BatchNo , @SPID) Update tmp_Batch Set FQty = FQty - @Qty Where FDetailID = @DID print -1 Select -1 print @qty end Else Begin Insert Into tmp_Last values (@EntryID , @BillNo , @Number , @BQty , @BatchNo , @SPID) Update tmp_Batch Set FQty = FQty - @BQty Where FDetailID = @DID set @Qty = @Qty - @BQty print 1 print @qty goto re --返回到re： End print '888888' Fetch Next From subgroup Into @EntryID , @BillNo , @Number , @qty , @SPID End Close subgroup Deallocate subgroup --释放游标 ----------------------------------------------------------------- --更新行号 ALTER TABLE tmp_Last ADD FDetailID int IDENTITY (1, 1) NOT NULL ALTER TABLE tmp_Last ADD FNewEntryID int go declare @DID int declare @BillNo varchar(100) declare @i int Declare subgroup Cursor For Select Distinct FBillNo From tmp_Last Open subgroup Fetch Next From subgroup Into @BillNo While @@FETCH_STATUS = 0 Begin set @i =1 Declare detail Cursor For Select FDetailID From tmp_Last Where FBillNo = @BillNo Open detail Fetch Next From detail Into @DID --可以认为FDetailID的值附给了@DID While @@FETCH_STATUS = 0 Begin update tmp_Last Set FNewEntryID = @i Where FDetailID = @DID set @i = @i + 1 Fetch Next From detail Into @DID End Close detail Deallocate detail Fetch Next From subgroup Into @BillNo End Close subgroup Deallocate subgroup Select * From tmp_Last ------------------------------------------------------------------- /* Select FNumber,Sum(FQty) FQty From tmp_Last Group by FNumber Select FBillNo,Sum(FQty) FQty From tmp_Last Group by FBillNo Select FNumber,Sum(nQua) nQua From Czq092801 Group by FNumber Select cOutBillNo,Sum(nQua) nQua From Czq092801 Group by cOutBillNo */
MySQL

56,990

社区成员

56,760

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章