这是执行任务的 MySQL 存储过程(已在 MySQL 5.5 上测试):
DROP PROCEDURE if exists split_sentence;
delimiter $$
CREATE PROCEDURE split_sentence(sentence varchar(255), delimiter VARCHAR(50), out result_wordcount INTEGER)
BEGIN
DECLARE last_position INTEGER;
DECLARE position INTEGER;
DROP TABLE IF EXISTS tmp_split_sentence;
CREATE TEMPORARY TABLE tmp_split_sentence (word varchar(255));
set last_position = 1;
set position = instr(sentence, delimiter);
WHILE position > 0 DO
INSERT INTO tmp_split_sentence (word) VALUES(substring(sentence, last_position, position-last_position));
set last_position = position+length(delimiter);
set position = locate(delimiter, sentence, last_position);
END WHILE;
if last_position < length(sentence) then
INSERT INTO tmp_split_sentence (word) VALUES(substring(sentence, last_position, length(sentence)-last_position+1));
end if;
SELECT count(*) into result_wordcount from tmp_split_sentence;
END$$
delimiter ;
DROP TABLE IF EXISTS tmp_sentence;
CREATE TEMPORARY TABLE tmp_sentence (word varchar(255));
call split_sentence('this is a sentence', ' ', @result_wordcount);
INSERT INTO tmp_sentence select * from tmp_split_sentence;
call split_sentence('sentence galore', ' ', @result_wordcount);
INSERT INTO tmp_sentence select * from tmp_split_sentence;
select * from tmp_sentence;
结果:
this
is
a
sentence
sentence
galore
请注意,该句子出现了两次,应该在问题中进行修正 :)。
explode
函数(http://php.net/manual/en/function.explode.php)的功能? - ajrealexplode
返回结果,这样查询将返回几行,每行包含一个单词? - ajreal