mysql将冒号分隔的串分成多条记录_MYSQL - 将冒号分隔的列值拆分为新列(MYSQL - Split colon separated column values in to new colum...

MYSQL - 将冒号分隔的列值拆分为新列(MYSQL - Split colon separated column values in to new columns)

我有一张这样的桌子，

CREATE TABLE IF NOT EXISTS `cms` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`cms` varchar(255) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `cms` (`id`, `cms`) VALUES

(1, 'cms:/o:freebsd:freebsd:2.1.7'),

(2, 'cms:/o:netbsd:netbsd:1.0');

从中我需要将cms列值拆分为四个附加列，因此必须用冒号分隔。

我需要这样的输出

需要查询

i have a table like this ,

CREATE TABLE IF NOT EXISTS `cms` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`cms` varchar(255) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `cms` (`id`, `cms`) VALUES

(1, 'cms:/o:freebsd:freebsd:2.1.7'),

(2, 'cms:/o:netbsd:netbsd:1.0');