我的表是一个树型的分类表,结构是有四个字段(自增ID,类名,父ID,路径)。虽然从逻辑上说路径是一个非独立字段,可在查询时通过父ID递归或循环得出,不需要单独存储,但为了提高查询效率,可将他求出做为一个独立字段存储。我现在需要的就是这个求路径的MYSQL函数。
我先给出我在MSSQL SERVER下的实现。
CREATE TABLE [dbo].[Categories](
[ID] [int] IDENTITY(0,1) NOT NULL,
[CategoryName] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NOT NULL,
[PID] [int] NULL CONSTRAINT [DF_Categories_PID] DEFAULT ((0)),
[Path] [nvarchar](1000) COLLATE Chinese_PRC_CI_AS NULL,
CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [mytb]
GO
ALTER TABLE [dbo].[Categories] WITH CHECK ADD CONSTRAINT [FK_Categories_Categories] FOREIGN KEY([PID])
REFERENCES [dbo].[Categories] ([ID])
GO
Insert into Categories(CategoryName) values('Root')
insert into Categories(CategoryName,pid) values('Animals',0)
insert into Categories(CategoryName,pid) values('Cat',1)
insert into Categories(CategoryName,pid) values('Dog',1)
insert into Categories(CategoryName,pid) values('Plants',0)
insert into Categories(CategoryName,pid) values('Vegetables',4)
insert into Categories(CategoryName,pid) values('Tomato',5)
go
CREATE FUNCTION [dbo].[getCategoryPath]
(
-- Add the parameters for the function here
@id int
)
RETURNS nvarchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @path nvarchar(1000)
-- Add the T-SQL statements to compute the return value here
set @path=''
while @id>0
begin
SELECT @id=pid,@path=categoryname+' > '+@path from Categories where id=@id
end
select @path=categoryname+' > '+@path from Categories where id=0
return @path
END
go
update categories set path=dbo.getCategoryPath(id)
select * from categories
-----------以下是表结果----------
-id------类名-------父ID----路径----------------
0 Root 0 Root >
1 Animals 0 Root > Animals >
2 Cat 1 Root > Animals > Cat >
3 Dog 1 Root > Animals > Dog >
4 Plants 0 Root > Plants >
5 Vegetables 4 Root > Plants > Vegetables >
6 Tomato 5 Root > Plants > Vegetables > Tomato >