Mysql comma delimited column conversion summary

方法一

Stored Procedure implementations:

--Modify Terminator to prevent the default semicolon from running directly on the MySQL command line

Delimiter $$

--Create a function to calculate the number of strings after splitting

Drop function if exists calc_length $$

Create function calc_length (str varchar (), SPLITSTR varchar (5)) returns INT (11)

Begin

return Length (str)-length (replace (str, SPLITSTR, ')) +1;

End $$

--Create a function to simulate split split string

Drop function if exists split_string $$

Create definer= ' root ' @ ' localhost ' function split_string (str varchar), SPLITSTR varchar (5), Strindex int) returns varchar (255)

Begin

DECLARE result varchar (255) default ';

Set result =reverse (Substring_index (Reverse (Substring_index (str, SPLITSTR, strindex)), SPLITSTR, 1);

return result;

End $$

--Create a stored procedure

drop procedure if exists proce_split $$

CREATE PROCEDURE Proce_split ()

Begin

DECLARE CNT int default 0;

declare i int default 0;

DECLARE str varchar (+) default ';

Select name into str from table1 where id = 2;

Set cnt = calc_length (str, ', ');

drop table if exists table2;

Create table2 (id int not NULL, name varchar (255) is not NULL) default Charset=utf8;

While I < CNT

Do

Set i = i+1;

INSERT into table (ID, name) values (i, split_string (str, ', ', i));

End while;

End $$

Method Two:

Cartesian Product Realization:

Select A.id,substring_index (Substring_index (A.loginname, ', ', b.help_topic_id+1), ', ',-1)

From

Account A

Join

Mysql.help_topic b

On b.help_topic_id < (length (a.loginname)-Length (replace (A.loginname, ', ', '))) +1)

Order BY a.ID;

Mysql comma delimited column conversion summary