mysql 用 隔开存储_MySql存储逗号分隔的字符串为临时表

存储过程方式：

DELIMITER $

SET @branch_array='0000,0001,0002,0003,0004,0005,0006,0007';

DROP PROCEDURE IF EXISTS `split_branch_sp`;

CREATE PROCEDURE `split_branch_sp` ( IN _string VARCHAR ( 8000 ) )

BEGIN

DECLARE _index INT;

DROP TEMPORARY TABLE IF EXISTS `branch_tmp_tb`;

CREATE TEMPORARY TABLE `branch_tmp_tb`

(

branch_no VARCHAR ( 255 )

);

SET _index = locate( ',', _string );

WHILE _index > 0

DO

INSERT INTO `branch_tmp_tb` VALUES ( LEFT ( _string, _index - 1 ) );

SET _string = substr( _string FROM _index + 1 );

SET _index = locate( ',', _string );

END WHILE;

IF length( _string ) > 0

THEN

INSERT INTO `branch_tmp_tb` VALUES ( _string );

END IF;

SELECT * FROM `branch_tmp_tb`;

END $

非存储过程方式：

SET @role_ids = '2016,2017,2018,2019,2020';

DROP TEMPORARY TABLE IF EXISTS `branch_tmp_tb`;

CREATE TEMPORARY TABLE `branch_tmp_tb`

(

branch_no VARCHAR ( 255 )

);

INSERT INTO `branch_tmp_tb`

(

SELECT

SUBSTRING_INDEX( SUBSTRING_INDEX( @role_ids, ',', ht.help_topic_id + 1 ), ',', -1 ) AS roleId

FROM

mysql.help_topic ht

WHERE

ht.help_topic_id < ( LENGTH( @role_ids ) - LENGTH( REPLACE ( @role_ids, ',', '' )) + 1 )

);

SELECT * FROM `branch_tmp_tb`;

注意： MySql中临时表只能被联合查询一次。 With 用方8.0后才支持