MySQL行转列

更新：2022-05-23 13:18:30编辑：Story归类：数据库人气：244

1.1. 前言

公司的多个系统间的通信是通过接口来实践的。由于历史的遗留的原因，从其他系统传来的订单和商品的信息是一个字符串，在我们这边 "商品ID" 存储的方式是使用逗号(',')隔开的。最近要在之前保存的业务上增加需求。但是不好对该记录的商品进行关联。因此，为了让改动降到最低，我们有规划了另外一个表用来来存放商品信息，只存放 "商品ID" 和主表的ID。

1.2. 表的基本结构

改造前表结构：

CREATE TABLE orders(
  orders_id INT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  good_ids VARCHAR(200) NOT NULL COMMENT '商品ID字符串',
  PRIMARY KEY(orders_id)
);
INSERT INTO orders VALUES(NULL, '1,2,3,4,5');
INSERT INTO orders VALUES(NULL, '11,12,13,14,15');
INSERT INTO orders VALUES(NULL, '21,22,23,24,25');

CREATE TABLE orders (

orders_id INT NOT NULL AUTO_INCREMENT COMMENT '订单ID' ,

good_ids VARCHAR ( 200 ) NOT NULL COMMENT '商品ID字符串' ,

PRIMARY KEY ( orders_id )

) ;

INSERT INTO orders VALUES ( NULL , '1,2,3,4,5' ) ;

INSERT INTO orders VALUES ( NULL , '11,12,13,14,15' ) ;

INSERT INTO orders VALUES ( NULL , '21,22,23,24,25' ) ;

改造后表结构：

-- orders 表不变
CREATE TABLE orders(
  orders_id INT NOT NULL AUTO_INCREMENT COMMENT '订单ID',
  good_ids VARCHAR(200) NOT NULL COMMENT '商品ID字符串',
  PRIMARY KEY(orders_id)
);
 
-- 新增订单商品表
CREATE TABLE order_goods(
  order_goods_id INT NOT NULL AUTO_INCREMENT COMMENT '订单商品ID',
  orders_id INT NOT NULL COMMENT '订单ID',
  goods_id INT NOT NULL COMMENT '商品ID',
  PRIMARY KEY(order_goods_id)
);

-- orders 表不变

CREATE TABLE orders (

orders_id INT NOT NULL AUTO_INCREMENT COMMENT '订单ID' ,

good_ids VARCHAR ( 200 ) NOT NULL COMMENT '商品ID字符串' ,

PRIMARY KEY ( orders_id )

) ;

-- 新增订单商品表

CREATE TABLE order_goods (

order_goods_id INT NOT NULL AUTO_INCREMENT COMMENT '订单商品ID' ,

orders_id INT NOT NULL COMMENT '订单ID' ,

goods_id INT NOT NULL COMMENT '商品ID' ,

PRIMARY KEY ( order_goods_id )

) ;

1.3. 实现行转列

这边我们需要借助一个有着ID连续的表(mysql.help_topic)来做关联，以至于能够划分出商品ID。

SELECT o.orders_id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(o.good_ids, ',', h.help_topic_id), ',', -1) 
FROM orders AS o
  JOIN mysql.help_topic AS h 
    ON h.help_topic_id <= (LENGTH(o.good_ids) - LENGTH(REPLACE(o.good_ids, ',', ''))+1)
WHERE help_topic_id > 0
ORDER BY o.orders_id;

SELECT o . orders_id ,

SUBSTRING_INDEX ( SUBSTRING_INDEX ( o . good_ids , ',' , h . help_topic_id ) , ',' , - 1 )

FROM orders AS o

JOIN mysql . help_topic AS h

ON h . help_topic_id <= ( LENGTH ( o . good_ids ) - LENGTH ( REPLACE ( o . good_ids , ',' , '' ) ) + 1 )

WHERE help_topic_id > 0

ORDER BY o . orders_id ;

1.4. 分解SQL进行解释

这边我们的目的是获得商品ID字符串中第二个商品ID。

使用逗号(',') 分割 good_ids 查看前两个元素(good_id)：

SELECT o.orders_id,
  SUBSTRING_INDEX(o.good_ids, ',', 2)
FROM orders AS o;
+-----------+-------------------------------------+
| orders_id | SUBSTRING_INDEX(o.good_ids, ',', 2) |
+-----------+-------------------------------------+
|         1 | 1,2                                 |
|         2 | 11,12                               |
|         3 | 21,22                               |
+-----------+-------------------------------------+

SELECT o . orders_id ,

SUBSTRING_INDEX ( o . good_ids , ',' , 2 )

FROM orders AS o ;

+ -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +

| orders_id | SUBSTRING_INDEX ( o . good_ids , ',' , 2 ) |

+ -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +

| 1 | 1 , 2 |

| 2 | 11 , 12 |

| 3 | 21 , 22 |

+ -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - +

通过上面获得的前两个元素的字符串，再次进行逗号(',') 分割，并获得最后一个元素。

SELECT o.orders_id,
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(o.good_ids, ',', 2),
    ',',
    -1
  ) AS good_id
FROM orders AS o;
+-----------+---------+
| orders_id | good_id |
+-----------+---------+
|         1 | 2       |
|         2 | 12      |
|         3 | 22      |
+-----------+---------+

SELECT o . orders_id ,

SUBSTRING_INDEX (

SUBSTRING_INDEX ( o . good_ids , ',' , 2 ) ,

',' ,

- 1

) AS good_id

FROM orders AS o ;

+ -- -- -- -- -- - + -- -- -- -- - +

| orders_id | good_id |

+ -- -- -- -- -- - + -- -- -- -- - +

| 1 | 2 |

| 2 | 12 |

| 3 | 22 |

+ -- -- -- -- -- - + -- -- -- -- - +

最终的SQL只是将指定的第二个元素，变成动态改变的。而动态的改变的数字就是通过关联mysql.help_topic来实现的

小提示：这里使用mysql.help_topic的原因是它有从0到629连续不断的help_topic_id。当然你也可以自己创建一个表并且插入连续不断的数据来作为关联表。

SELECT o.orders_id,
  SUBSTRING_INDEX(SUBSTRING_INDEX(o.good_ids, ',', h.help_topic_id), ',', -1) AS good_id
FROM orders AS o
  JOIN mysql.help_topic AS h 
    ON h.help_topic_id <= (LENGTH(o.good_ids) - LENGTH(REPLACE(o.good_ids, ',', ''))+1)
WHERE help_topic_id > 0
ORDER BY o.orders_id;
+-----------+---------+
| orders_id | good_id |
+-----------+---------+
|         1 | 1       |
|         1 | 2       |
|         1 | 3       |
|         1 | 4       |
|         1 | 5       |
|         2 | 15      |
|         2 | 11      |
|         2 | 12      |
|         2 | 13      |
|         2 | 14      |
|         3 | 22      |
|         3 | 23      |
|         3 | 24      |
|         3 | 25      |
|         3 | 21      |
+-----------+---------+