我在mysql中尝试了这段代码，但它是行操作，如何插入按列排列的数据。

Question

MySQL存储过程可用于拆分字符串，以下详细信息可供您使用MySQL存储过程参考学习。

现有的字符串，如苹果、香蕉、橘子、梨、葡萄，它应该跟着逗号(，)分为：

苹果香蕉橙梨葡萄在()方法中可以查询。

1 .具体职能：

功能: func_split_TotalLength

DELIMITER $ $
 DROP   function IF EXISTS `func_split_TotalLength` $ $
 CREATE DEFINER = `root` @ `%` FUNCTION `func_split_TotalLength`
 (F_string varchar (1000), f_delimiter varchar (5)) RETURNS   int (11)
  BEGIN  
 return 1 + (length (f_string) - length (replace (f_string, f_delimiter,'')));
 END $ $
 DELIMITER;

功能: func_split

    DELIMITER $ $
    DROP   function IF EXISTS `func_split` $ $
    CREATE DEFINER = `root` @ `%` FUNCTION `func_split`
    (F_string varchar (1000), f_delimiter varchar (5), f_order int) RETURNS   varchar        (255) CHARSET utf8
    BEGIN  
    declare result varchar (255) default   '';
     set result = reverse (substring_index (reverse (substring_index (f_string, f_delimiter, f_order)), f_delimiter, 1));
     return result;
    END $ $
   DELIMITER;

存储过程: SplitString

  DELIMITER $ $
  DROP   PROCEDURE IF EXISTS `splitString` $ $
  CREATE   Procedure the `SplitString`
  (IN f_string varchar (1000), IN f_delimiter varchar (5))
  BEGIN  
  declare cnt int   default 0;
  declare i int   default 0;
  set cnt = func_split_TotalLength (f_string, f_delimiter);
  DROP   TABLE IF EXISTS `tmp_split`;
  create   temporary   table `tmp_split` (`status` varchar (128) not   null) DEFAULT CHARSET = utf8;
 while i <cnt
 do
 set i = i + 1;
 insert   into tmp_split (`status`) values ??(func_split (f_string, f_delimiter, i));
end while;
 END $ $
 DELIMITER;

2、测试将是成功的分割。

 call splitString ("apple, banana, orange, pears, grape", ",");
 select * from tmp_split;
 The results are splitting success:

mysql>调用splitString (“苹果、香蕉、橘子、梨、葡萄")；从tmp_split中选择*；查询确定，一行受影响，但我需要按列顺序插入数据.，请帮助我

Answer 1

我会用另一种方法来解决这个问题，用表格的形式得到结果：

没有存储过程的更简单的方法

假设您有一个包含两个列的表示例: id和col1，如下所示：

创建表示例( id INT NOT NULL主键，col1 VARCHAR(1000) )；

而这些价值观

id | col
-----------------------------
 1 | abcd,efgh,ijkl,ghjy,sdfg
 2 | some other text

并且您希望将逗号分隔的值从col获取到一个列中，在id = 1时只有一个值。然后我将使用以下sql：

SELECT
    id, 
    SUBSTRING_INDEX(SUBSTRING_INDEX(e.col, ',', t.n), ',', -1) value
FROM example e CROSS JOIN (
    SELECT 
        1 + a.N + b.N * 10 + c.N * 100 AS n
    FROM
        (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
       ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
    ORDER BY n
) t
WHERE 
    e.id = 1
AND
    t.n <= (1 + LENGTH(e.col) - LENGTH(REPLACE(e.col, ',', '')));

它与存储过程中的过程相同。您可以在联接或其他操作中轻松地使用此结果。

参见此演示

第一部分假设需要存储过程。

存储过程

• 不需要其他功能
• 没有显式的临时表
• 作品多达1000件，易于扩展。

代码：

DROP procedure splitString;
DELIMITER $ $
CREATE procedure splitString(IN f_string VARCHAR(1000), IN f_delimiter VARCHAR(5))
BEGIN
    SELECT SUBSTRING_INDEX(
               SUBSTRING_INDEX(h.haystack, f_delimiter, t.n
           ), f_delimiter, -1) as value 
    FROM (SELECT f_string as haystack) h
    CROSS JOIN (
        SELECT 
            1 + a.N + b.N * 10 + c.N * 100 AS n
        FROM
            (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
           ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
           ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
        ORDER BY n
    ) t
    WHERE t.n <= 
    (
        1 
        + (
            CHAR_LENGTH(f_string) 
            - 
            CHAR_LENGTH(REPLACE(f_string, f_delimiter, ''))
          ) 
          / CHAR_LENGTH(f_delimiter)
    );
END;
$ $
DELIMITER ;

解释

1.生成从1到1000的数字列表

最内在的选择

SELECT 
    1 + a.N + b.N * 10 + c.N * 100 AS n
FROM
    (SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b
   ,(SELECT 0 AS N UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c
ORDER BY n

不超过创建一个数字从1到1000的列表。如果你需要更多，你应该看看它是怎么做的。用这些union all添加一行，并调用这个d，将+ d.N * 1000加到和中。

2.执行拆分，每行只获取一个元素

我们使用这个列表以(更易读的语句)的形式对func_split进行拆分。

SELECT SUBSTRING_INDEX(
           SUBSTRING_INDEX(h.haystack, f_delimiter, 1
       ), f_delimiter, -1) as value 
FROM (SELECT f_string as haystack) h

SELECT SUBSTRING_INDEX(
           SUBSTRING_INDEX(h.haystack, f_delimiter, 2
       ), f_delimiter, -1) as value 
FROM (SELECT f_string as haystack) h

SELECT SUBSTRING_INDEX(
           SUBSTRING_INDEX(h.haystack, f_delimiter, 3
       ), f_delimiter, -1) as value 
FROM (SELECT f_string as haystack) h

提取第一，第二，第三.子字符串，用f_delimiter分隔。

获取元素的数量

我们的WHERE条款几乎是不言而喻的：

-- because we begin by 1 we've got to include the upper limit
WHERE t.n <= 
(
    1  -- we have one comma less than parts
    + (
        -- count how many characters (not bytes) we lose by the replace operation
        CHAR_LENGTH(f_string) 
        - 
        CHAR_LENGTH(REPLACE(f_string, f_delimiter, ''))
      ) 
      -- and divide this by the count of characters in the delimiter string
      / CHAR_LENGTH(f_delimiter)
);

Note

LENGTH()也可以，而不是CHAR_LENGTH()，因为除法的结果是相同的，但是我喜欢这样做:-)

**检查职能**

您可以用

CALL splitString('apple, bananas, grape, ananas, pears', ', ');

它回来了

value
-----
apple
bananas
grape
ananas
pears