mysql 动态拆分列

原创

mob64ca12f0cf8f 2024-07-31 03:57:36 ©著作权

©著作权归作者所有：来自51CTO博客作者mob64ca12f0cf8f的原创作品，请联系作者获取转载授权，否则将追究法律责任

MySQL 动态拆分列的实现方法

在数据库设计和数据存储的情况下，许多时候我们需要对 Excel 等数据格式中的数据进行拆分，进而将其存储到 MySQL 数据库中。特别是当某一列的数据在量上或者内容上变得复杂时，动态拆分列显得尤为重要。本文将详细介绍如何在 MySQL 中实现动态拆分列，指导小白开发者一步步来完成这一过程。

过程概览

在进行动态拆分列的过程中，我们通常需要按以下几个步骤操作：

步骤	描述
1	创建测试表
2	插入测试数据
3	使用 SQL 语句拆分列数据
4	查看拆分结果

步骤 1: 创建测试表

首先，我们需要在数据库中创建一个表来存储待拆分的数据。在该表中，我们可以只用一个列来存储初始数据。

CREATE TABLE travel_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    travel_date VARCHAR(255),
    travel_info TEXT
);
-- 创建一个叫 travel_data 的表，包含 id（主键）和两个字段（travel_date 和 travel_info）

步骤 2: 插入测试数据

接下来，我们需要插入一些数据到刚创建的表中，以便进行拆分测试。

INSERT INTO travel_data (travel_date, travel_info) VALUES
('2023-01-01', '北京,上海,广州'),
('2023-01-02', '成都,重庆');
-- 向 travel_data 表中插入旅行日期以及旅行城市信息，以“,”分割

步骤 3: 使用 SQL 语句拆分列数据

这里，我们需要使用 MySQL 查询，利用 FIND_IN_SET 函数和一些字符串处理函数，把城市信息拆分到单独的行中。

创建一个存储过程来进行拆分操作。

DELIMITER //
CREATE PROCEDURE SplitColumns()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE travel_date VARCHAR(255);
  DECLARE travel_info TEXT;
  DECLARE cur CURSOR FOR SELECT travel_date, travel_info FROM travel_data;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  OPEN cur;
  
  read_loop: LOOP
    FETCH cur INTO travel_date, travel_info;
    IF done THEN
      LEAVE read_loop;
    END IF;

    SET @sql = CONCAT('SELECT "', travel_date, '" AS travel_date, SUBSTRING_INDEX(SUBSTRING_INDEX(travel_info, ",", numbers.n), ",", -1) AS city
                       FROM (SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) numbers
                       WHERE CHAR_LENGTH(travel_info)
                         -CHAR_LENGTH(REPLACE(travel_info, ",", "")) >= numbers.n - 1;');
  
    PREPARE stmt FROM @sql;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END LOOP;

  CLOSE cur;
END //
DELIMITER ;
-- 创建一个存储过程 SplitColumns，利用游标和字符串拆分逻辑来处理数据

步骤 4: 查看拆分结果

一旦存储过程创建完成并执行，我们就可以查看结果。

CALL SplitColumns();
-- 调用 SplitColumns 存储过程，查看拆分后的结果

结果展示

通过以上步骤，我们将一个包含城市列表的列成功拆分为多行，方便进行进一步的数据分析和处理。

journey
    title MySQL 动态拆分列步骤
    section 创建测试表
      创建表: 5: 成功
    section 插入数据
      插入数据: 5: 成功
    section 拆分列数据
      执行存储过程: 5: 成功
    section 查看结果
      查看拆分结果: 5: 成功