mysql - ExtractValue 在 mysql 中处理和解析 XML 非常慢

Question

我们正在使用 ExtractValue(xml,xpath) 处理大量 XML，并对 XML 进行循环。例如ExtractValue(p_xml, concat(xpath,'/ROOT/TABLE[0]/')),

While (20k)
 INSERT INTO select_keys(key)
 Values (ExtractValue(p_xml, concat(xpath,'key',[counter])));
End While

当行数超过数千时，对数据表的 XML 处理变得非常慢。

CALL `MT_PROC`
(181, 0, '<ROOT></ROOT>', '<ROOT>
<TABLE><keys>f5d29464-e941-4cdf-a21b-984add68dce9</keys></TABLE>
<TABLE><keys>731d23a1-0438-4f72-91a5-83288f6e93b4</keys></TABLE>
<TABLE><keys>c68a4fb8-6306-4728-8b55-f26a151ffad4</keys></TABLE>
<TABLE><keys>3f5ec4bb-2109-4876-a419-b07b553081cc</keys></TABLE>
<TABLE><keys>4689bc20-9eec-4f6b-a454-9a30b7080cc2</keys></TABLE>
<TABLE><keys>466a7da5-7a9e-419f-b1fc-dcd57931a095</keys></TABLE>
....20K rows
</ROOT>') 

这个问题的解决方案是什么？

Answer 1

通过直接插入而不是 ExtractValue 加快 xml 处理速度。使用查询直接向表中插入数据如下。

if p_xml is not null and p_xml <> '<ROOT></ROOT>' 
        then
            /*set CountMAX  = ExtractValue(p_xml, 'count(/ROOT/TABLE)');
            set Counter=1;

                while(Counter<=CountMAX) DO
                set xpath=concat('/ROOT/TABLE[',Counter,']/');
                    -- inserting values into the temp table
                    INSERT INTO select_keys(report_key)   Values (
                    ExtractValue(p_xml, concat(xpath,'report_key'))
                     );
                set Counter = Counter + 1;
               END WHILE;
               */               
    set p_xml = replace(replace(p_xml,'<TABLE><report_key>','('''),'</report_key></TABLE>','''),');            
    set p_xml = replace(replace(p_xml,'<ROOT>',''),',</ROOT>','');

    set @stmt_str = concat('INSERT INTO select_keys(report_key) values ' ,p_xml);
    prepare stmt from @stmt_str;
    execute stmt;
    deallocate prepare stmt;
END If;