因此,越来越多的网页都执行了URL重写,以达到URL友好,搜索引擎友好!所以构建一个用户友好的主键ID利于url重写时进行更好的分析!
以下是我提供的一个sql存储过程,可以按年月日生成用户友好的主键ID。欢迎指点!
/**/
/*
生成一个按年月日连续的ID
(基于特定表的,只需要设置[Application.TableKey].TableID
及最后生成@NextID时把TableID的值嵌进去即可)
如果表名在表[Application.TableKey]不存在,则自动创建一条记录
生成的ID形如:
200605151(2006年5月15日的第一条记录)
2006051510(2006年5月15日的第十条记录)
200605161(2006年5月16日的第一条记录)
2006061610(2006年6月16日的第十条记录)
*/
IF EXISTS ( SELECT * FROM sysobjects WHERE type = ' P ' AND name = ' Application.spGetNextID ' )
BEGIN
DROP Procedure [ Application.spGetNextID ]
END
GO
CREATE Procedure [ Application.spGetNextID ]
@TableName nvarchar ( 50 ),
@NextID bigint = null output
AS
set nocount on
declare @Year int
declare @Month int
declare @Day int
declare @SerialNumber int
select @Year = year ( getdate ()), @Month = month ( getDate ()), @Day = day ( getdate ()), @SerialNumber = 0
if not exists ( select 1 from [ Application.TableKey ] where TableName = @TableName )
begin
INSERT INTO [ Application.TableKey ]
(TableName, Year , Month , Day , SerialNumber)
VALUES ( @TableName , @Year , @Month , @Day , @SerialNumber )
-- update [Application.TableKey] set TableID=@@Identity where TableName=@TableName
end
else
begin
select @SerialNumber = isnull (SerialNumber, 0 ) from [ Application.TableKey ]
where TableName = @TableName and year = @Year and month = @Month and Day = @Day
end
select @SerialNumber = @SerialNumber + 1
update [ Application.TableKey ] set
year = @Year , month = @Month , day = @Day ,SerialNumber = @SerialNumber
where TableName = @TableName
select @NextID = ( @Year * POWER ( 10 , 4 ) + @Month * POWER ( 10 , 2 ) + @Day ) * power ( 10 , len ( @SerialNumber )) + @SerialNumber
生成一个按年月日连续的ID
(基于特定表的,只需要设置[Application.TableKey].TableID
及最后生成@NextID时把TableID的值嵌进去即可)
如果表名在表[Application.TableKey]不存在,则自动创建一条记录
生成的ID形如:
200605151(2006年5月15日的第一条记录)
2006051510(2006年5月15日的第十条记录)
200605161(2006年5月16日的第一条记录)
2006061610(2006年6月16日的第十条记录)
*/
IF EXISTS ( SELECT * FROM sysobjects WHERE type = ' P ' AND name = ' Application.spGetNextID ' )
BEGIN
DROP Procedure [ Application.spGetNextID ]
END
GO
CREATE Procedure [ Application.spGetNextID ]
@TableName nvarchar ( 50 ),
@NextID bigint = null output
AS
set nocount on
declare @Year int
declare @Month int
declare @Day int
declare @SerialNumber int
select @Year = year ( getdate ()), @Month = month ( getDate ()), @Day = day ( getdate ()), @SerialNumber = 0
if not exists ( select 1 from [ Application.TableKey ] where TableName = @TableName )
begin
INSERT INTO [ Application.TableKey ]
(TableName, Year , Month , Day , SerialNumber)
VALUES ( @TableName , @Year , @Month , @Day , @SerialNumber )
-- update [Application.TableKey] set TableID=@@Identity where TableName=@TableName
end
else
begin
select @SerialNumber = isnull (SerialNumber, 0 ) from [ Application.TableKey ]
where TableName = @TableName and year = @Year and month = @Month and Day = @Day
end
select @SerialNumber = @SerialNumber + 1
update [ Application.TableKey ] set
year = @Year , month = @Month , day = @Day ,SerialNumber = @SerialNumber
where TableName = @TableName
select @NextID = ( @Year * POWER ( 10 , 4 ) + @Month * POWER ( 10 , 2 ) + @Day ) * power ( 10 , len ( @SerialNumber )) + @SerialNumber
使用的表
/**/
/****** 对象: Table [dbo].[Application.TableKey] 脚本日期: 05/16/2006 22:57:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo ] . [ Application.TableKey ] (
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ TableName ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ TableID ] [ int ] NULL ,
[ Year ] [ int ] NULL ,
[ Month ] [ int ] NULL ,
[ Day ] [ int ] NULL ,
[ SerialNumber ] [ int ] NULL
) ON [ PRIMARY ]
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [ dbo ] . [ Application.TableKey ] (
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ TableName ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ TableID ] [ int ] NULL ,
[ Year ] [ int ] NULL ,
[ Month ] [ int ] NULL ,
[ Day ] [ int ] NULL ,
[ SerialNumber ] [ int ] NULL
) ON [ PRIMARY ]