利用SQL将查询到的string值分离，以及python实现导出csv文件方法

最新推荐文章于 2022-06-25 18:20:56 发布

Donald Su

最新推荐文章于 2022-06-25 18:20:56 发布

阅读量849

点赞数

分类专栏： SQL Python

本文链接：https://blog.csdn.net/donaldsy/article/details/119618450

版权

Python 同时被 2 个专栏收录

35 篇文章 1 订阅

订阅专栏

SQL

3 篇文章 0 订阅

订阅专栏

文章目录

1、问题描述

最近一小伙伴需要做一个SQL查询操作：

获取到两列A、B，B是一个string，string以,分隔，需要将B拆封，显示 A，B1；A，B2…
- 类似于下面的操作:

ps：根据自己对SQL的认识，不使用SQL 函数的情况下很难做到，如果是将查询结果导出，再利用python脚本这种分离非常容易实现。

2、解决方案

查了一些相关资料，上图的这种操作，MySQL中几个拆分字符串的函数，分别为：

SUBSTRING_INDEX(str,delim,count)
- 例如：SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); 输出： ‘www.mysql’
SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)
- SUBSTR是SUBSTRING的简写，都可使用
- 例如：SELECT SUBSTRING('Quadratically',5,6);，输出： ‘ratica’

但以上两个函数能拆分，但都不能满足当前需要求，同时MySQL中并没有 split函数，上面的查询操作属于一些比较高级的操作，所以需要使用：存储过程或函数解决，函数的写法可参见 2-1.

2-1、定义split函数

MySQL中并没有 split函数，在数据库中定义SPLIT_STR 函数即可，如下所示。

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

定义完成后，通过命令：SELECT SPLIT_STR(string, delimiter, position) 使用，但该函数只是实现了split，但并为分离所有的，一定程度上仍然不满足。
定义函数中可能会遇到【ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, xxx】错误，通过set global log_bin_trust_function_creators=1;设置即可解决，可参考：【Mysql自定义函数报错解决方法】
参考：【stackoverflow】How to split the name string in mysql?

这里只是一个说明，函数就是利器在手，什么操作都能实现，可根据需求实现函数，又是一个值得深挖的操作，在此不再深入介绍。

2-2、借助python实现

这里更方便的是，两步操作

将查询结果导出（csv文件）
使用python脚本进行解析

Python脚本为：

#!/usr/bin/env python
# coding: utf-8

import csv
file_path = "./data.csv"
save_file_path = "./convert_data.csv"

output = open(save_file_path, "w", newline='')
writer = csv.DictWriter(output, ['iterm','iterm_val'])
writer.writeheader()

with open(file_path, newline='') as csvfile:
    reader = csv.DictReader(csvfile)
    for row in reader:
        iterm = row['iterm']
        valules = row["iterm_values"]
        for val in valules.split(','):
            writer.writerow({'iterm':iterm, 'iterm_val': val})
            # print({'iterm':iterm, 'iterm_val': val})

# 关闭已经打开的文件
output.close()
csvfile.close()

2-3、利用MySQL自带函数

在整理该问题的反向操作时，换个角度搜索了下，没想到果真找到了该问题的答案，不用创建新表，在哪都可操作，脚本如下，可以根据自己的表实际情况进行操作，已经测试，以下脚本可以实现效果。

select
  key_value.iterm,
  SUBSTRING_INDEX(SUBSTRING_INDEX(key_value.iterm_values, ',', numbers.n), ',', -1) iterm_values
from
  (select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5) numbers INNER JOIN key_value
  on CHAR_LENGTH(key_value.iterm_values)
     -CHAR_LENGTH(REPLACE(key_value.iterm_values, ',', ''))>=numbers.n-1
order by
  iterm, n;

以上操作来源于【stackoverflow】SQL split values to multiple rows
- 该回答中第一个是创建了numbers这个数据表，可以参考，这里更推荐第二种方法（类似上面的脚本）

3、反向操作（多行内容合并）

如果将以上进行反向操作，如下图所示：
在这里插入图片描述
该问题，在MySQL中可以使用 GROUP_CONCAT函数，使用方法如下：

SELECT person_id,
   GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;

该问题可参考：

【stackoverflow】Can I concatenate multiple MySQL rows into one field?