1、将查询结果中有用的数据用分隔符获取,更新到另一张表。
DELIMITER $$
DROP FUNCTION IF EXISTS `func_splitString` $$
CREATE FUNCTION `func_splitString`
( f_string VARCHAR(1000),f_delimiter VARCHAR(5),f_order INT)
RETURNS VARCHAR(255) CHARSET utf8
BEGIN
DECLARE result VARCHAR(255) DEFAULT ‘‘;
SET result = REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(f_string,f_delimiter,f_order)),f_delimiter,1));
RETURN result;
END$$
//更新表字段3min_document:cover_id int
//查询表字段3min_document_video:video string (内容类似:xxxxxxx|12)
UPDATE 3min_document d INNER JOIN 3min_document_video v ON d.id=v.id
SET d.cover_id=func_splitString(v.video,‘|‘,2);
原文:http://my.oschina.net/liucao/blog/500688