关于自增列delete之后insert的问题！

DiligencyMan 2008-12-15 05:18:31 
/****** Object:  Table [dbo].[ttt]    Script Date: 2008-12-15 17:11:25 ******/

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ttt]

GO



/****** Object:  Table [dbo].[ttt]    Script Date: 2008-12-15 17:11:26 ******/

CREATE TABLE [dbo].[ttt] (

	[id] [int] IDENTITY (1, 1) NOT NULL ,

	[name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

	[time] [datetime] NULL 

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[ttt] ADD 

	CONSTRAINT [PK_ttt] PRIMARY KEY  CLUSTERED 

	(

		[id]

	)  ON [PRIMARY] 

GO



insert into ttt(name,time) values('logan',getdate());

insert into ttt(name,time) values('peter',getdate());

insert into ttt(name,time) values('man',getdate());

insert into ttt(name,time) values('lida',getdate());



select * from ttt

delete from ttt where name='peter'

insert into ttt(name,time) values('peter',getdate());

select * from ttt



结果(一)如下：

1	logan	2008-12-15 17:14:02.840

2	peter	2008-12-15 17:14:02.840

3	man	2008-12-15 17:14:02.840

4	lida	2008-12-15 17:14:02.857



结果(二)如下：

1	logan	2008-12-15 17:14:02.840

3	man	2008-12-15 17:14:02.840

4	lida	2008-12-15 17:14:02.857

5	peter	2008-12-15 17:14:02.857


为什么结果(二)里面的name为peter的id值不是2而是5呢？有没有办法将自增列delete之后的id值给收回呢，这样delete之后再insert的话，name为peter的记录的id任然为2呢？
...全文
145 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
水族杰纶 2008-12-16
  • 打赏
  • 举报
 回复 
--完全沒必要連續,需要得時候生成一列新的即可

if object_id('tempdb..#')is not null drop table #

go

create table #(ID int identity,[Name] varchar(10))

insert # select 'A' union all 

             select 'B' union all

             select 'C' 

select * from #

delete # where ID=2

insert # select 'E'

select * from #

--生成新的列IDNEW

select ID=(select count(*)+1 from # where ID<t.ID),* from # t order by ID

/*

(影響 3 個資料列)



ID          Name       

----------- ---------- 

1           A

2           B

3           C



(影響 3 個資料列)





(影響 1 個資料列)





(影響 1 個資料列)



ID          Name       

----------- ---------- 

1           A

4           E

3           C



(影響 3 個資料列)



ID          ID          Name       

----------- ----------- ---------- 

1           1           A

2           3           C

3           4           E*/
oraclelogan 2008-12-16
  • 打赏
  • 举报
 回复
[Quote=引用 9 楼 fcuandy 的回复:]
既然问的人多，也就随手写一个。

利用触发器实现标识列连续。(支持批量插入）

see also:
http://blog.csdn.net/fcuandy/archive/2008/12/15/3522876.aspx



SQL codeif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ttt]
GO

/****** Object: Table [dbo].[ttt] Script Date: 2008-12-15 17:11:26 *****…
[/Quote]

海哥正解，不过触发器性能消耗太大了，问下， id自增列里面不连续这样的，是否有很多资源被浪费掉了啊！
让你望见影子的墙 2008-12-15
  • 打赏
  • 举报
 回复
[Quote=引用楼主 DiligencyMan 的帖子:]
SQL code/****** Object: Table [dbo].[ttt] Script Date: 2008-12-15 17:11:25 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[ttt]
GO

/****** Object: Table [dbo].[ttt] Script Date: 2008-12-15 17:11:26 ******/
CREATE TABLE [dbo].[ttt] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[na…
[/Quote]

原因：当你第一次插入的时候，那么肯定是连续的，那么当前的标示值为5。删除了第二个值，再插入的时候，标识列是从当前的标示值开始插入，那么新插入到一行自然就是5了，插入一行后，那么标示值的当前值为6.
也就是说标示值不会回收以前的标示值的，最后插入的一行自然不会为2了。

如果非要连续，那么只能自己写。参照FC的
fcuandy 2008-12-15
  • 打赏
  • 举报
 回复
上面的触发器有些问题。
如果删除的是最后一条记录，或最后几条记录，那么就有问题了。

需要做多点处理，触发器里先插入一条测试记录，得到最大标识值，再删除这条，然后再处理。


建议楼主不要这么搞了.

你新插的记录补了前面的id, 从数据上来说，也没得什么意义。
fcuandy 2008-12-15
  • 打赏
  • 举报
 回复
海哥说的是。

我的观点在3楼已经表达过了。

上面只说实现。
昵称被占用了 2008-12-15
  • 打赏
  • 举报
 回复
考虑过性能吗？

为这个连续号用触发器代价太大了
fcuandy 2008-12-15
  • 打赏
  • 举报
 回复
既然问的人多，也就随手写一个。

利用触发器实现标识列连续。(支持批量插入）

see also:
http://blog.csdn.net/fcuandy/archive/2008/12/15/3522876.aspx


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ttt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[ttt]

GO



/****** Object:  Table [dbo].[ttt]    Script Date: 2008-12-15 17:11:26 ******/

CREATE TABLE [dbo].[ttt] (

    [id] [int] IDENTITY (1, 1) NOT NULL ,

    [name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,

    [time] [datetime] NULL 

) ON [PRIMARY]

GO



ALTER TABLE [dbo].[ttt] ADD 

    CONSTRAINT [PK_ttt] PRIMARY KEY  CLUSTERED 

    (

        [id]

    )  ON [PRIMARY] 

GO



insert into ttt(name,time) values('logan',getdate());

insert into ttt(name,time) values('peter',getdate());

insert into ttt(name,time) values('man',getdate());

insert into ttt(name,time) values('lida',getdate());

insert into ttt(name,time) values('fcuandy',getdate());



select * from ttt

/*

1	logan	2008-12-15 17:36:37.780

2	peter	2008-12-15 17:36:37.780

3	man	2008-12-15 17:36:37.780

4	lida	2008-12-15 17:36:37.780

5	fcuandy	2008-12-15 17:36:37.793

*/





GO

CREATE TRIGGER tr ON ttt

INSTEAD OF INSERT

AS

	SET IDENTITY_INSERT ttt ON

	DECLARE @n INT

	SELECT @n=MAX(id) FROM ttt

	;WITH fc AS

	(

		SELECT n=1

		UNION ALL

		SELECT n=n+1 FROM fc WHERE n<@n

	),fc1 AS

	(

	SELECT n FROM fc a

	LEFT JOIN ttt b

		ON a.n = b.id

		WHERE b.id IS NULL

	)

	INSERT ttt(id,name,time) SELECT n,name,time

		FROM

		(SELECT ROW_NUMBER() OVER (ORDER BY GETDATE()) idx,n FROM fc1) a

	INNER JOIN

		(

		SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted

		) b

		ON a.idx=b.idx

	DECLARE @r INT

	SELECT @r=@@ROWCOUNT

	

	SET IDENTITY_INSERT ttt OFF

		INSERT ttt(name,time) SELECT name,time FROM 

			(

				SELECT idx=ROW_NUMBER() OVER (ORDER BY GETDATE()),name,time FROM inserted

			) x

			WHERE idx>@r

	



GO

DELETE FROM ttt WHERE name = 'peter' OR name='lida'

GO

INSERT ttt SELECT 'xxx',getdate()

INSERT ttt SELECT 'yyy',GETDATE()

GO

SELECT * FROM ttt

/*

1	logan	2008-12-15 17:37:20.967

2	xxx	2008-12-15 17:37:21.013

3	man	2008-12-15 17:37:20.967

4	yyy	2008-12-15 17:37:21.030

5	fcuandy	2008-12-15 17:37:20.967

*/



DELETE FROM ttt WHERE name ='xxx' OR name='yyy'



INSERT ttt SELECT 'roy_88',GETDATE() UNION ALL SELECT 'limpire',GETDATE() UNION ALL SELECT '熊',GETDATE()



SELECT * FROM ttt



/*

1	logan	2008-12-15 17:38:29.450

2	roy_88	2008-12-15 17:38:29.530

3	man	2008-12-15 17:38:29.467

4	limpire	2008-12-15 17:38:29.530

5	fcuandy	2008-12-15 17:38:29.467

6	熊	2008-12-15 17:38:29.530

*/
baoshan 2008-12-15
  • 打赏
  • 举报
 回复
SET IDENTITY_INSERT [ database_name . [ schema_name ] . ] table { ON | OFF }


如果插入值大于表的当前标识值，则 SQL Server 自动将新插入值作为当前标识值使用。

SET IDENTITY_INSERT 的设置是在执行或运行时设置的，而不是在分析时设置的。

AndrewITBOY 2008-12-15
  • 打赏
  • 举报
 回复
好吧 我是来接分的
dawugui 2008-12-15
  • 打赏
  • 举报
 回复
如果要连续,或...,参考如下:

表jh03有下列数据：

name　score

aa　　99

bb　　56

cc　　56

dd　　77

ee　　78

ff　　76

gg　　78

ff　　50



1. 名次生成方式1,Score重复时合并名次

SELECT *　,　Place=(SELECT COUNT(DISTINCT Score) FROM jh03 WHERE Score >= a.Score)

FROM jh03 a

ORDER BY Place

结果

Name       Score        Place 

---------------- ----------------- ----------- 

aa         99.00        1

ee         78.00        2

gg         78.00        2

dd         77.00        3

ff         76.00        4

bb         56.00        5

cc         56.00        5

ff         50.00        6



2. 名次生成方式2 , Score重复时保留名次空缺

SELECT * , Place=(SELECT COUNT(Score) FROM jh03 WHERE Score > a.Score) + 1

FROM jh03 a

ORDER BY Place

结果

Name       Score        Place 

--------------- ----------------- ----------- 

aa         99.00        1

ee         78.00        2

gg         78.00        2

dd         77.00        4

ff         76.00        5

bb         56.00        6

cc         56.00        6

ff         50.00        8
dawugui 2008-12-15
  • 打赏
  • 举报
 回复
为什么结果(二)里面的name为peter的id值不是2而是5呢？有没有办法将自增列delete之后的id值给收回呢，这样delete之后再insert的话，name为peter的记录的id任然为2呢？

------------

没必要,反正都是唯一的,不需要强求连续.

claro 2008-12-15
  • 打赏
  • 举报
 回复
帮顶
fcuandy 2008-12-15
  • 打赏
  • 举报
 回复
另外，大多数应用下，标识都不是连续的， 所以不必强求。

只是希望得到连续的续号，可以在select语句中产生，或者在前加输出时加序号。
new_Stone 2008-12-15
  • 打赏
  • 举报
 回复
没办法,数据库设计就这样了.
fcuandy 2008-12-15
  • 打赏
  • 举报
 回复 
SET IDENTITY_INSERT 表名 ON
mysql的插入问题 怎么获得自动增长的ID
一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 还是MYSQL的,一张表有还是有ID自增主键,用JDBC insert一条语句之内,怎么在JAVA...
关于MySQL自增ID的一些小问题总结
下面这几个小问题都是基于 InnoDB 存储引擎的。 1. ID最大的记录删除后，新插入的记录ID是什么 例如当前表中有ID为1,2,3三条记录，把3删除，新插入记录的ID从哪儿开始？ 答案： 从4开始。 实验 创建表 tb0，ID自增...
MySQL入门进阶及面试题分析
mysqlSQL语句(create,delete,insert,update,select)，   mysql查询语句聚合,mysql表连接，mysql字段类型，mysql表约束以及表结构操作，   mysql唯一约束非空约束主键约束，mysql外键约束和默认约束，...
Mybatis源码阅读（三）：结果集映射3.3 —— 主键生成策略
这里需要提的是在insert语句中，关于主键自增的问题。 KeyGenerator 在我们实际的开发中，自增主键还是使用比较多的。而mybatis在处理insert语句时，并不会将自增主键给返回，而是返回插入的条数。当我们有业务需求...
oracle 触发器 学习笔记
2、 自动生成派生列，比如自增字段 3、 强制数据一致性 4、 提供审计和日志记录 5、 防止无效的事务处理 6、 启用复杂的业务逻辑 开始 create trigger biufer_employees_department_id before insert or update of ...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章