MySQL - 如何从字符串中获取子字符串表

问题描述：

我在数据库中获取了表EVENT_ID - ATTENDANTS varchar（300）包含以重复属性和唯一用户名连接的字符串形式的数据。属性和名称之间的分隔符是“：”，“属性：用户”的每个集合都以“;”结尾。某些属性名称中有空格。每行中的配对数量是未知的。MySQL - 如何从字符串中获取子字符串表

“attributeC：USER_NAME1; attributeA：USER_NAME3; attributeA：user_name4; attributeA：user_name10; attributeB：user_name42;”

然后，我就表对USER_ID - USER_NAME和目标表的字段USER_ID - EVENT_ID - ATTRIBUTE。

提取用户和属性并将它们插入到目标表中的最佳方法是什么？

我会喜欢这样做的SQL（MySQL的），在使用功能等，使他们暂时的情况下，脚本将只运行一次。

如果这将是复杂得多，我愿意使用PHP和OOP的mysqli功能。（这将在设定的其他$ mysqli->查询（运行）命令）

我发现，MySQL functions SUBSTRING（），查找（），长度（）能来得心应手。

我能够通过PHP来做到这一点有些费力的方式基于SELECT

并使阵列（USER_ID - USER_NAME）
使SELECT（EVENT_ID - 服务员）和每一行调用爆炸（）， GET属性，EVENT_ID，USER_ID（从上面阵列，代替SQL查询在每个字符串中的每个用户名的每一个事件）
paralelly插入数据到目标表用的mysqli制备语句

但我想（希望？）有一些更好的办法。你怎么看？

答

经过一番研究，我解决了这个问题，它并不像看起来那样愚蠢。

代码第一：

select OUTPUT.ID, OUTPUT.name, OUTPUT.attribute from 
(
select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 1), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 1), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 2), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 2), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 3), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 3), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 4), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';',4), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 5), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 5), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 6), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 6), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 7), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 7), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 8), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 8), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 9), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 9), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 10), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 10), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 11), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 11), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 12), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 12), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 13), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 13), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 14), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 14), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 15), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 15), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 16), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 16), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 17), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 17), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 18), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 18), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 19), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 19), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 20), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 20), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 21), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 21), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 22), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 22), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 23), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 23), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 24), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 24), ';', -1), ':', -1) as name 
from AKCE 

union 

select 
    AKCE.ID, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 25), ';', -1), ':', 1) as attribute, 
    substring_index(substring_index(substring_index(AKCE.PRIHLASENI, ';', 25), ';', -1), ':', -1) as name 
from AKCE 

order by ID 
) AS OUTPUT WHERE OUTPUT.name!='' AND OUTPUT.attribute!=''

正如你所看到的，它是漫长的，充满了冗余代码等

当您使用内侧的位置比deimiters的数量更大SUBSTRING_INDEX（），它会返回空字段。由于工会，每个原始表格行（AKCE.ID）仅返回一次这些空白字段。

这些空行过滤用WHERE ...！=“”

这当然isn't以正确的方式，但在150行，最大23双它快（仅适用不错NAD比基本选择慢10倍）

答

你可以在MySQL中很痛苦地做到这一点。下面是获得前三这样的字符串的例子：

select t.col, n.n, 
     substring_index(substring_index(t.col, ';', n.n), ';', -1) as pair, 
     substring_index(substring_index(substring_index(t.col, ';', n.n), ';', -1), ':', 1) as name, 
     substring_index(substring_index(substring_index(t.col, ';', n.n), ';', -1), ':', -1) as value 
from t join 
    (select 1 as n union all select 2 union all select 3 
    ) n 
    on length(t.col) - length(replace(t.col, ';', '') + 1 <= n.n;

你只需要额外的数字添加到n子查询，以获得更多的对。

我想： t.col = my_table.ATTENDANTS， n =重复次数，究竟是什么nn？我不得不补充，对每个EVENT行的配对数量是未知的，最有可能不同。 – Faramos

@Faramos。。。 'on'子句应该处理这个问题。你只需要足够大的'n'值来获得每一对。 –

MySQL - 如何从字符串中获取子字符串表

相关推荐