准备条件
CREATE TABLE `user` (
`id` int(11) NOT NULL,
`name` varchar(256) CHARACTER SET utf8 NOT NULL,
`mobile` varchar(256) CHARACTER SET utf8 NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `test`.`user` (`id`, `name`, `mobile`) VALUES ('1', '张三', '1231231233,1211211211,1321,12312');
INSERT INTO `test`.`user` (`id`, `name`, `mobile`) VALUES ('2', '李斯', '232321,1232132');
将这样的展示方式转换
转换成这样的
select a.name ,substring_index(substring_index(a.mobile,',',b.help_topic_id+1),',',-1) as mobile
from
user a
join
mysql.help_topic b
on b.help_topic_id < (length(a.mobile) - length(replace(a.mobile,',',''))+1)
order by a.name;