mysql中字符串分解成行，及各行拼接成字符串的方法

1. 将字符串分解成几行（Walking a string）

方法：首先需要一个interger表

CREATE TABLE `int_table` (
  `int_col` int(11) NOT NULL,
  PRIMARY KEY (`int_col`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

-- ...
-- INSERTS follow here
-- ...

mysql> SELECT * FROM int_table;
+---------+
| int_col |
+---------+
| 0 |
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
+---------+
10 rows in set (0.00 sec)

然后进行select

SELECT
         SUBSTRING(s, int_col+1, 1) AS c
       FROM int_table, (SELECT 'hello' AS s) sel1
       WHERE int_col < char_length(s);
+---+
| c |
+---+
| h |
| e |
| l |
| l |
| o |
+---+
5 rows in set (0.00 sec)

 

2. 将列值拼接成一个字符串（unwalking a string）

CREATE TABLE characters AS
  SELECT
    SUBSTRING(s, int_col+1, 1) AS c
  FROM int_table, (SELECT 'hello' AS s) sel1
  WHERE int_col < char_length(s);

 

mysql> SELECT GROUP_CONCAT(c separator '') AS s FROM characters;
+-------+
| s |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)