MySql字符串拆分多条数据,序号自增长

根据特定字符将一条数据拆分成多条数据

原始数据

处理结果

SQL语句

SELECT
	a.id,
	a. NAME,
	substring_index(
		substring_index(
			a.shareholder,
			',',
			b.help_topic_id + 1
		),
		',' ,- 1
	) AS shareholder
FROM
	company a
JOIN mysql.help_topic b ON b.help_topic_id < (
	length(a.shareholder) - length(
		REPLACE (a.shareholder, ',', '')
	) + 1
)
-- 注此处((length(a.shareholder) - length(REPLACE (a.target_column, target_char_len, '')))/target_char_len + 1) 
-- 计算数组元素个数 = 数组长度差/目标切割字符长度 + 1

附录

• 基本表建立语句

• CREATE TABLE `company` (
    `id` int(20) DEFAULT NULL,
    `name` varchar(100) DEFAULT NULL,
    `shareholder` varchar(100) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  INSERT INTO `company` VALUES ('1', '阿里巴巴', '马云');
  INSERT INTO `company` VALUES ('2', '淘宝', '马云,孙正义');

   

• 序号自增长

• SELECT
  	(@i:=@i+1) id,
  	a. NAME,
  	substring_index(
  		substring_index(
  			a.shareholder,
  			',',
  			b.help_topic_id + 1
  		),
  		',' ,- 1
  	) AS shareholder
  FROM
  	company a
  JOIN mysql.help_topic b ON b.help_topic_id < (
  	length(a.shareholder) - length(
  		REPLACE (a.shareholder, ',', '')
  	) + 1
  ),
  (SELECT @i:=0) as i