我已经做了很多(重新)搜索,然后找到了以下SO post / answer:https : //stackoverflow.com/a/5361490/6095216,它与我要找的东西非常接近。相同的代码,但有一些更有用的注释,出现在这里:http : //thenoyes.com/littlenoise/?p=136。
我需要将MySQL TEXT数据的1列拆分为多列,其中原始数据具有以下格式(N <= 7):
{"field1":"value1","field2":"value2",...,"fieldN":"valueN"}
您可能会猜到,我只需要提取值,然后将每个值放到单独的(预定义)列中即可。问题在于,不能保证所有记录的字段数量和顺序都相同。因此,使用SUBSTR / LOCATE等的解决方案不起作用,我需要使用正则表达式。另一个限制是,不能使用诸如LIB_MYSQLUDF_PREG之类的第三方库(在上面我的第一条链接的答案中建议)。
我已经修改了上述链接中的代码,以使其返回从左到右的第一个/最短匹配项;否则,返回NULL。我也对其进行了重构,使标识符对阅读器/维护者更友好:)这是我的版本:
CREATE FUNCTION REGEXP_EXTRACT_SHORTEST(string TEXT, exp TEXT)
RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE adjustStart, adjustEnd BOOLEAN DEFAULT TRUE;
DECLARE startInd INT DEFAULT 1;
DECLARE endInd, strLen INT;
DECLARE candidate TEXT;
IF string NOT REGEXP exp THEN
RETURN NULL;
END IF;
IF LEFT(exp, 1) = '^' THEN
SET adjustStart = FALSE;
ELSE
SET exp = CONCAT('^', exp);
END IF;
IF RIGHT(exp, 1) = '$' THEN
SET adjustEnd = FALSE;
ELSE
SET exp = CONCAT(exp, '$');
END IF;
SET strLen = LENGTH(string);
StartIndLoop: WHILE (startInd <= strLen) DO
IF adjustEnd THEN
SET endInd = startInd;
ELSE
SET endInd = strLen;
END IF;
EndIndLoop: WHILE (endInd <= strLen) DO
SET candidate = SUBSTRING(string FROM startInd FOR (endInd - startInd + 1));
IF candidate REGEXP exp THEN
RETURN candidate;
END IF;
IF adjustEnd THEN
SET endInd = endInd + 1;
ELSE
LEAVE EndIndLoop;
END IF;
END WHILE EndIndLoop;
IF adjustStart THEN
SET startInd = startInd + 1;
ELSE
LEAVE StartIndLoop;
END IF;
END WHILE StartIndLoop;
RETURN NULL;
END;
然后,我添加了一个辅助函数,以避免必须重复正则表达式模式,正如您从上面看到的那样,所有字段都是相同的。这是该函数(我放弃了使用回溯的尝试-在MySQL中不受支持-作为注释):
CREATE FUNCTION GET_MY_FLD_VAL(inputStr TEXT, fldName TEXT)
RETURNS TEXT DETERMINISTIC
BEGIN
DECLARE valPattern TEXT DEFAULT '"[^"]+"'; /* MySQL doesn't support lookaround :( '(?<=^.{1})"[^"]+"'*/
DECLARE fldNamePat TEXT DEFAULT CONCAT('"', fldName, '":');
DECLARE discardLen INT UNSIGNED DEFAULT LENGTH(fldNamePat) + 2;
DECLARE matchResult TEXT DEFAULT REGEXP_EXTRACT_SHORTEST(inputStr, CONCAT(fldNamePat, valPattern));
RETURN SUBSTRING(matchResult FROM discardLen FOR LENGTH(matchResult) - discardLen);
END;
当前,我要尝试做的就是使用上面的代码进行简单的SELECT查询。它正常工作,但是。是。SLOOOOOOOW ...最多只能拆分7个字段/列(并非所有记录都具有全部7个)!仅限于20条记录,大约需要3分钟-我总共大约有40,000条记录(对于数据库来说不是很多,对吧!!):)
因此,最后,我们得到了一个实际的问题:[如何]在性能上显着改善上述算法/代码(在这一点上几乎是粗暴的搜索),以便可以在实际的数据库中运行该算法/代码。合理的时间?我开始研究已知的主要模式匹配算法,但很快就迷失了方向,试图找出适合的算法,这在很大程度上是由于可用选项的数量及其各自的限制,使用条件等所致。似乎在SQL中实现其中之一只是为了看看是否有帮助,可能需要很多工作。
注意:这是我有史以来的第一篇文章(!),所以如果有不清楚的地方,请(很好地)让我知道,等等,我将尽力解决它。提前致谢。
正如上面的tadman和Matt Raines所建议的,我能够通过解析JSON来解决这个问题。作为JSON概念的新手,我只是根本不知道可以用这种方式完成它……有点尴尬,但可以吸取教训!
无论如何,我在common_schema框架中使用了get_option函数:https : //code.google.com/archive/p/common-schema/(可通过本文找到,它还演示了如何使用该函数:在MySQL中解析JSON) 。结果,我的INSERT查询运行大约需要15分钟,而使用REGEXP解决方案则需要30多个小时。谢谢,直到下一次!:)
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句