虽然您的过程在第二个块中查找备用匹配字符串时有一些禁用的内容，但我不确定您要获取什么，所以我将该块大部分注解掉。
而且你实际上对这个东西没有任何用处，所以我把它去掉了。如果您需要它回来，我会重新添加变量cursor和fetch，但也需要将插入列添加到split\u vals表中。
我的方法是使用instr（）函数来确定给定字符串或字符的第一个位置。。。在本例中，您正在查找管道角色。一旦确定了这一点，我就会不断地从字符串中删除刚刚处理的字符串的任何部分，从而不断地收缩p\u searchtext字符串，直到什么都没有留下。我认为您检查每个字符的逻辑一直失败，或者没有将这些项视为双空格，从而创建循环中描述和覆盖的双“| |”字符。
除了instr（）之外，您还可以对substring\u index（str，delim，count）执行类似的操作，以获取字符串、空字符串或找到的位置。。。希望这能帮到你。。。

CREATE DEFINER=`root`@`localhost` 
PROCEDURE `sp_update_productsearch_datasync`()
BEGIN
   -- regardless of any possible results,
   -- prepare temp table to store string split into word parts
   drop TABLE IF EXISTS split_vals;      
   create temporary TABLE split_vals(vals varchar(1000));  

   DECLARE p_searchtext longtext default ''; 
   DECLARE search_loop_finished boolean default false; 

   DECLARE srchbox_cur CURSOR FOR 
      SELECT DISTINCT searchbox 
         FROM  productsearch 
         where searchbox is not null 
         limit 10; 

   DECLARE continue HANDLER for NOT found 
      SET search_loop_finished = true; 

   open srchbox_cur;   
   srchbox_loop : loop   
      FETCH srchbox_cur INTO  p_searchtext; 
      -- exit immediately if no result
      IF search_loop_finished then 
         leave srchbox_loop; 
      END IF; 

      -- preset any SPACES into a common PIPE character
      set p_searchtext = rtrim(replace(p_searchtext,' ','|')); 

      declare str       varchar(500); 
      declare pipePos   int default 0;

      split_val_loop : loop 

         -- if no text string left to work with, exit loop
         if length(p_searchtext) = 0 THEN 
            leave split_val_loop; 
         end if; 

         -- does the p_searchtext have any more pipe characters left in it?
         set pipePos = INSTR('|', p_searchtext);

         -- in case there is a double space could create "test||strings|||more"
         -- you would never want to insert a "|" string value
         if pipepos = 1
            set p_searchtext = substr(p_searchtext, 2);
            iterate split_val_loop;
         end if;

         -- if not, do the insert for whatever is remaining and clear flag
         -- for the split_val_loop
         if pipePos = 0 then
            set str = p_searchtext;
            -- clear text since this was the end
            set p_searchtext = '';
         else
            -- there IS a pipe.  get portion UP TO, but not including the pipe
            -- do not include the pipe character in string returned
            set str = left( p_searchtext, pipePos -1);
            -- remove the entire length of the found string including the pipe
            set p_searchtext = SUBSTR( p_searchtext, pipePos +1)
         end if

         -- there HAD to be something since search text was > 0
         insert INTO split_vals VALUES (str); 
      end loop split_val_loop;

   -- I would continue ALL fetched search records and split them all first
   -- so you don't keep requerying the list as it grows for each 10 records
   end loop srchbox_loop;

   -- close cursor once out of the loop
   close srchbox_cur;

   -- uncomment below to pre-check results of split values
   -- select * from split_vals;

   -- now the next set of processing
   DECLARE alternative_search_loop_finished boolean default false; 
   DECLARE p_alternative_str VARCHAR(1000) default ''; 

   DECLARE alternative_search_cur CURSOR FOR 
      SELECT DISTINCT vals FROM  split_vals; 

   DECLARE continue HANDLER for NOT found 
      SET alternative_search_loop_finished = true; 

   OPEN alternative_search_cur; 

   alternative_search_loop : loop 
      FETCH alternative_search_cur INTO p_alternative_str; 

      IF alternative_search_loop_finished then 
         leave alternative_search_loop; 
      END IF; 

      -- select search_b 
      --    from Product_Search_Alternatives 
      --    where search_a = p_alternative_str;

      -- select p_item, p_searchtext; 

      /*
      update productsearch
      set searchbox = Concat(searchbox,' ',v_alt_txt)
      where item = v_item;        
      */
      -- select v_alt_txt;
   end loop alternative_search_loop;

   -- close cursor OUT of the loop
   close alternative_search_cur; 

-- end of procedure
end