无限极分类的自动定位函数,目的省略PHP去一个个查询数据。
表结构:
mysql> desc my_category;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| upid | int(10) | NO | MUL | NULL | |
| name | varchar(20) | NO | | NULL | |
| sort | int(4) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)
mysql>
测试数据:
CREATE TABLE IF NOT EXISTS `my_category` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`upid` int(10) NOT NULL,
`name` varchar(20) NOT NULL,
`sort` int(4) NOT NULL,
PRIMARY KEY (`id`),
KEY `upid` (`upid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=68 ;
--
-- 转存表中的数据 `my_category`
--
INSERT INTO `my_category` (`id`, `upid`, `name`, `sort`) VALUES
(54, 43, '纽约州', 0),
(45, 42, '广东', 0),
(46, 45, '广州', 0),
(47, 45, '佛山', 0),
(48, 45, '深圳', 0),
(49, 45, '东莞', 0),
(53, 43, '密西西比州', 0),
(51, 43, '佛罗里达州', 0),
(52, 43, '俄亥俄州', 0),
(42, 0, '中国', 0),
(43, 0, '美国', 0),
(44, 0, '日本', 0),
(55, 43, '华盛顿州', 0),
(56, 43, '加利福尼亚州', 0),
(57, 56, '旧金山市', 0),
(58, 57, '圣何塞', 0),
(59, 46, '天河区', 0),
(60, 46, '白云区', 0),
(61, 46, '越秀区', 0),
(62, 59, '体育西站', 0),
(63, 59, '岗顶站', 0),
(64, 59, '石牌桥站', 0),
(65, 64, '太古汇广场', 0),
(66, 64, '丰兴广场', 0),
(67, 64, '万菱汇广场', 0);
假如层级关系:中国》广东》广州》天河区》石牌桥站》太古汇广场。
当输入的ID是太古汇广场的id,自动返回:中国》广东》广州》天河区》石牌桥站》
假如层级关系:美国》加利福尼亚州》旧金山市》圣何塞
当输入的ID是“旧金山市”的ID,自动返回:美国》加利福利亚州》
创建MySQL函数:get_category()
CREATE FUNCTION get_category(sid int(4))
RETURNS VARCHAR(120) charset utf8
BEGIN
#Routine body goes here...
DECLARE pos varchar(120);
DECLARE upid int(10);
SET upid=(SELECT upid FROM my_category where id=sid);
SET pos=(select name FROM my_category WHERE `id`=sid);
IF upid>0 THEN
SET pos= CONCAT(get_category(upid),pos);
END IF;
RETURN pos;
END
运行结果,无法返回正确的数据。
IF THEN这里似乎有错误,mysql是否支持将select到的数据存放到declare 的对象?(default7#zbphp.com)
继续修改此自定义函数:
CREATE FUNCTION get_category(intid int(4))
RETURNS VARCHAR(120) charset utf8
BEGIN
#Routine body goes here...
DECLARE strpos varchar(120);
DECLARE intupid int(10);
SET intupid=(SELECT upid FROM my_category where id=intid);
SET strpos=(select name FROM my_category WHERE id=intid);
IF intupid>0 THEN
SET strpos= CONCAT(get_category(intupid),strpos);
END IF;
RETURN strpos;
END
运行结果,报错,提示#1424
Procedure execution failed
1424 - Recursive stored functions and triggers are not allowed.
测试使用存储过程,创建失败:
CREATE DEFINER = CURRENT_USER PROCEDURE `PR_GET_CATEGORY`(IN `sid` int,OUT `spos` varchar)
BEGIN
#Routine body goes here...
DECLARE strloc VARCHAR(120);
DECLARE intupid int(10);
SET strloc=(SELECT name FROM my_category WHERE id=sid);
SET intupid=(SELECT upid FROM my_category WHERE id=sid);
IF intupid>0 THEN
SET strloc=CONCAT(PR_GET_CATEGORY(intupid),strloc);
END IF;
SELECT strloc INTO spos;
END;;
1)mysql存储过程名字、自定义函数名字是否区分大小写?
2)语句如何写才可以实现本例的需求?
3)mysql中函数或者存储过程 传入的参数名如果跟表的字段名一样会怎么样
1)mysql存储过程名字、自定义函数名字是否区分大小写?
看设置,在LINUX上可以区分,可以不区分,
2)语句如何写才可以实现本例的需求?
直接用WHILE循环试一下,直到upid为0或NULL
3)mysql中函数或者存储过程 传入的参数名如果跟表的字段名一样会怎么样
MYSQL将以表中字段名优先解释。 where ix = ix ,则MYSQL把IX均认为是字段。
假如上级有多条怎么办?
创建失败是因为不允许递归造成的。
其实这种东西最好是用程序来解决,用数据库效率会非常的慢;
你可以不段的用left join 来实现;
我的思路是一次取出所有数据并加载到php的数组里,用php的方法去递归查询你所需要的树;
一般这种省市级而且变动相当小,最好直接用php保存成文件。
--转自