mysql - SQL 使用参数/值对解析一个长的 varchar 字段

Question

我们的应用程序在 MySQL 5.6.21 上运行，对于每个事务，它都会将一个条目写入日志表。我感兴趣的主要字段是一个很长的 varchar 字段，它基本上包含传递给我们的应用程序的完整 URL。此数据字段中可以包含任意数量的参数/值对，由 & 符号分隔。我正在尝试想出一种方法来解析它们，以便我们可以报告这些数据。

这是一个示例记录: sessionId=LongSessionIdWeCanIgnore&serverurl=https://www.theurl.com/bla/bla&parameter1=123456789&param2=23456789&par3=987654&p4=abcdef123&parameterlongname6=ABCDEFG

所以从这个字段中，我想有一个 SQL 语句/函数将它分开如下:

Parameter                Value
---------------          ----------------------
serverUrl                https://www.theurl.com/bla/bla
parameter1               123456789
param2                   23456789
par3                     987654
p4                       abcdef123
parameterlongname6       ABCDEFG

因此，正如您所见，此 SQL 需要能够循环，并且对于它看到的每个和号，提取参数名称(从 & 到 =)，以及 = 符号之后的值(从 = 到下一个 &)。我已经为一个参数制定了“硬编码”的 SQL，但我想避免这种情况，因为我们可能有超过 300 个不同的参数，而且我不想为每个参数编写单独的代码.下面的 SQL 示例是我目前使用的硬编码 SQL 示例:

select
substr(ParameterString,(instr(ParameterString, '&parameter1=')+12), (locate('&', ParameterString, instr(ParameterString, '&parameter1=')+12)) -  (instr(ParameterString, '&parameter1=')+12))
from TestLogTable
where ParameterString like '%&parameter1=%';

同样，我不喜欢这种方法，因为它必须用数字 12 进行硬编码，因为这是“&parameter1=”的长度。

我希望这里的一些 SQL 专家可以帮助我使这个东西更加动态和自动化？？下面的示例 SQL 用于创建包含一些测试数据的测试表。

谢谢!!

create table TestLogTable (ParameterString varchar(32768));

insert into TestLogTable (ParameterString) values ('sessionId=LongSessionIdWeCanIgnore&serverurl=https://www.theurl.com/bla/bla&parameter1=123456789&param2=23456789&par3=987654&p4=abcdef123&parameterlongname6=ABCDEFG');
insert into TestLogTable (ParameterString) values ('sessionId=LongSessionIdWeCanIgnore&serverurl=https://www.theurl2.com/bla/bla&parameter5=123456789&param6=23456789&par7=987654&p8=abcdef123&parameterlongname9=ABCDEFG');
insert into TestLogTable (ParameterString) values ('sessionId=LongSessionIdWeCanIgnore&serverurl=https://www.theurl2.com/bla/bla&parameter5=123456123&param6=23456789123&par7=987654123&p8=abcdef123123&parameterlongname9=ABCDEFG123123');

Answer 1

您正在寻找的基本上与 mysql Group_conat() 函数相反——不是将一个集合连接在一起，而是在分隔符上将其分开。

如果您不知道参数的确切数量，则没有简单的一行查询可以涵盖这一点。如果你知道参数的最大数量，你可以这样写:SQL split comma separated row .

我建议您真正想做的是在您不知道确切限制或它可能会更改时编写一个过程。

这是另一个 SO 问题，涵盖了如何使用存储过程实现此目的:"Reverse GROUP_CONCAT" in MySQL?