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 等应用程序语言，然后将其作为单独的列重新导入。