将数据分解成mysql中的列

问题描述

我有一列，我想创建一个包含多列的查询.

I have a column and I want to create a query to have multiple columns.

例如，我有 test 列.

这一列的数据是这样的:

The data of this column looks like this:

record1 => **option1|value1;option2|value2**
record2 => **option3|value3**
record3 => **option3|value3;option1|value1;option2|value2;option4|value4**

选项和值的数量未定义.

The number of options and values are undefined.

我想让这一个测试列分成这样的选项数:

I want to have this one test column break into number of option like this :

option1   option2   option3  option4
'value1'  'value2'  'value3'  
                    'value3'
'value1'  'value2'  'value3' 'value4'

我的桌子是这样的:

    id  name    options
    1   name    age|12;color|red;active|yes;car|no
    2   name    age|15;member|yes

谢谢！

推荐答案

你可以写一个 自定义 MySQL 函数称为用户定义函数，可以接收变量名称和要搜索的值，然后根据需要返回行或 ID 等.

You can write a custom MySQL function called user defined function that can receive the variable name and the value to search for and then return the row or ID etc. as you need.

在该函数中，您需要遍历只读游标中的所有行并使用 SUBSTRING_INDEX() 分别获取值并检查匹配.

In that function, you will need to loop through all the rows in a read only cursor and use SUBSTRING_INDEX() to get the values separately and check for the match.

如果您需要进一步的帮助，请告诉我.

If you need further assistance then let me know.

有关字符串函数的更多信息，请查看 MySQL 手册.

来自上述链接的用户评论摘录，可能有助于您编写函数:这会将 IP 地址(a.b.c.d")拆分为 4 个各自的八位字节:

An extract from a user comment from the above link that might help you write the function:This will split an IP address ("a.b.c.d") into 4 respective octets:

SELECT
`ip` , 
SUBSTRING_INDEX( `ip` , '.', 1 ) AS a,
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', 2 ),'.',-1) AS b, 
SUBSTRING_INDEX(SUBSTRING_INDEX( `ip` , '.', -2 ),'.',1) AS c,
SUBSTRING_INDEX( `ip` , '.', -1 ) AS d
FROM log_table

这篇关于将数据分解成mysql中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！