DROP TABLE IF EXISTS `index1`;
CREATE TABLE `index1` (
`id` bigint(20) unsigned NOT NULL,
`weight` int(10) NOT NULL,
`query` varchar(255) NOT NULL,
`field_id` bigint(20) DEFAULT NULL,
KEY `query` (`query`)
) ENGINE=SPHINX DEFAULT CHARSET=utf8 CONNECTION='sphinx://127.0.0.1:3312/index1'
$sql = "
SELECT *
FROM index1 as i
LEFT JOIN cms_item as c
ON (i.id=c.item_id)
WHERE 1 AND
i.id=c.item_id AND
query='$keyword'
";
delimiter //
CREATE FUNCTION getRelatedWords(inkeyword varchar(255)) RETURNS varchar(255)
BEGIN
DECLARE pid bigint(20) DEFAULT 0;
DECLARE keywords varchar(255) DEFAULT '';
SELECT synonym_id into pid from cms_synonym where content=inkeyword;
SELECT content into keywords from cms_synonym_subword where synonym_id=pid;
RETURN keywords;
END; //
delimiter ;
select get_related_words("中國");
BEGIN
-- 獲取父分類編碼
DECLARE parent_class_code varchar(255) DEFAULT '';
DECLARE child_class_code varchar(255) DEFAULT '';
DECLARE v1 INT DEFAULT 1;
IF parent_class_id > 0 THEN
SELECT class_code into parent_class_code FROM cms_resource_class WHERE class_id=parent_class_id;
END IF;
WHILE v1 <= 999 DO
SET child_class_code=concat(parent_class_code,right( convert(1000 + v1,char(4)),3));
IF exists( select class_id from cms_resource_class where class_code =child_class_code ) THEN
SET v1=v1+1;
ELSE
RETURN child_class_code;
END IF;
END WHILE;
RETURN '';
END;;
delimiter //
CREATE FUNCTION `getRelatedWords`(inkeyword varchar) RETURNS varchar(255)
BEGIN
-- DECLARE child_class_code varchar(255) DEFAULT '';
DECLARE pid bigint(20) DEFAULT 0;
SELECT synonym_id into pid from cms_synonym where content=inkeyword;
SELECT content from cms_synonym_subword where synonym_id=pid;
-- SELECT synonym_id into tmpfid from doc_forums where fid=infid;
END; //
delimiter ;
delimiter //
CREATE FUNCTION getRelatedWords(inkeyword varchar(255)) RETURNS varchar(255)
BEGIN
DECLARE pid bigint(20) DEFAULT 0;
SELECT synonym_id into pid from cms_synonym where content=inkeyword;
SELECT content from cms_synonym_subword where synonym_id=pid;
END; //
delimiter ;