MYSQL:具有可变发生次数的拆分字符串

Question

我在表格中有一个列，其中包含了这个格式的人的详细信息：

+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|  Team  |                                                                                                Members                                                                                                 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Team 1 | OK-10:Jason:Jones:ID No:00000000:male:my notes                                                                                                                                                         |
| Team 2 | OK-10:Mike:James:ID No:00000001:male:my notes OZ-09:John:Rick:ID No:00000002:male:my notes                                                                                                             |
| Team 3 | OK-08:Michael:Knight:ID No:00000004:male:my notes2 OK-09:Helen:Rick:ID No:00000005:female:my notes3 OZ-10:Jane:James:ID No:00000034:female:my notes23 OK-09:Mary:Jane:ID No:00000023:female:my notes46 |
+--------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

我想把它们分开放在这样的新桌子上：

+-------+-------------+-------------+----------------+------------------+---------------+---------------+--------------+
| Team  | Member_Rank | Member_Name | Member_Surname | Member_ID_Method | Member_ID_Num | Member_Gender | Member_Notes |
+-------+-------------+-------------+----------------+------------------+---------------+---------------+--------------+
| Team1 | OK-10       | Jason       | Jones          | ID No            |      00000000 | male          | my notes     |
| Team2 | OK-10       | Mike        | James          | ID No            |      00000001 | male          | my notes     |
| Team2 | OZ-09       | John        | Rick           | ID No            |      00000002 | male          | my notes     |
+-------+-------------+-------------+----------------+------------------+---------------+---------------+--------------+

分割细节：

分隔行划界器：“O&-”其中&只能是“K”或“Z”

分隔栏分隔符：':‘

一个团队可以容纳多个成员，没有上限。

这有可能吗？

Answer 1

我们可以使用SUBSTRING_INDEX来处理这个问题，但它相当丑陋：

SELECT
    Team,
    SUBSTRING_INDEX(text, ':', 1) AS Member_Rank,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(text, ':', 2), ':', -1) AS Member_Name,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(text, ':', 3), ':', -1) AS Member_Surname,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(text, ':', 4), ':', -1) AS Member_ID_Method,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(text, ':', 5), ':', -1) AS Member_ID_Num,
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(text, ':', 6), ':', -1) AS Member_Gender,
    SUBSTRING_INDEX(text, ':', -1) AS Member_Notes
FROM yourTable;

但我个人可能会在MySQL之外重新格式化/拆分您的数据，例如在应用程序语言(如Java )中，然后将其重新导入为单独的列。