mysql将一个值拆分成多行_SQL将值拆分为多行

delimiter $$DROP PROCEDURE IF EXISTS split_value_into_multiple_rows $$CREATE PROCEDURE split_value_into_multiple_rows(tablename VARCHAR(20),

id_column VARCHAR(20), value_column VARCHAR(20), delim CHAR(1))

BEGIN

DECLARE id INT DEFAULT 0;

DECLARE value VARCHAR(255);

DECLARE occurrences INT DEFAULT 0;

DECLARE i INT DEFAULT 0;

DECLARE splitted_value VARCHAR(255);

DECLARE done INT DEFAULT 0;

DECLARE cur CURSOR FOR SELECT tmp_table1.id, tmp_table1.value FROM

tmp_table1 WHERE tmp_table1.value IS NOT NULL AND tmp_table1.value != '';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET @expr = CONCAT('CREATE TEMPORARY TABLE tmp_table1 (id INT NOT NULL, value VARCHAR(255)) ENGINE=Memory SELECT ',

id_column,' id, ', value_column,' value FROM ',tablename);

PREPARE stmt FROM @expr;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;

DROP TEMPORARY TABLE IF EXISTS tmp_table2;

CREATE TEMPORARY TABLE tmp_table2 (id INT NOT NULL, value VARCHAR(255) NOT NULL) ENGINE=Memory;

OPEN cur;

read_loop: LOOP        FETCH cur INTO id, value;

IF done THEN

LEAVE read_loop;

END IF;

SET occurrences = (SELECT CHAR_LENGTH(value) -

CHAR_LENGTH(REPLACE(value, delim, '')) + 1);

SET i=1;

WHILE i <= occurrences DO          SET splitted_value = (SELECT TRIM(SUBSTRING_INDEX(

SUBSTRING_INDEX(value, delim, i), delim, -1)));

INSERT INTO tmp_table2 VALUES (id, splitted_value);

SET i = i + 1;

END WHILE;

END LOOP;

SELECT * FROM tmp_table2;

CLOSE cur;

DROP TEMPORARY TABLE tmp_table1;

END; $$delimiter ;

使用示例(标准化)：CALL split_value_into_multiple_rows('my_contacts', 'contact_id', 'interests', ',');CREATE TABLE interests (

interest_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

interest VARCHAR(30) NOT NULL) SELECT DISTINCT value interest FROM tmp_table2;CREATE TABLE contact_interest (

contact_id INT NOT NULL,

interest_id INT NOT NULL,

CONSTRAINT fk_contact_interest_my_contacts_contact_id FOREIGN KEY (contact_id) REFERENCES my_contacts (contact_id),

CONSTRAINT fk_contact_interest_interests_interest_id FOREIGN KEY (interest_id) REFERENCES interests (interest_id))

SELECT my_contacts.contact_id, interests.interest_id    FROM my_contacts, tmp_table2, interests

WHERE my_contacts.contact_id = tmp_table2.id AND interests.interest = tmp_table2.value;