基于Navicat和MySql实现存储过程导出数据库表带字段说明（comment）的csv数据表(含源码)

devtools/2025/2/22 19:11:12/

前言

本需求是在系统开发完成后，系统运行前，有导入部分基础数据的需求，在制作数据表格时，关于字段的说明其实已经在数据库字段的注释内（comment），常用的Navicat导出数据表格其实是只有字段名而没有字段的说明的，这里想着书写一个可以直接导出数据表格带字段名和说明的存储过程，当需要的时候调用导出即可，本文代码需要在 mysql-8.0.26 和navicat 16版本下实现，其他版本可能会存在问题。

这里先定义一下数据表实体类：

@Data
@EqualsAndHashCode(callSuper = false)
@TableName(value = "value_table",keepGlobalPrefix = true)
@TableComment("数值表")
@ApiModel(value = "数值表")
public class ValueTable {@ApiModelProperty("编号")@TableId(value = "id",type = IdType.ASSIGN_ID)@Column(comment = "编号")private Long id;@ApiModelProperty(value = "名称")@Column(comment = "名称")private String name;@ApiModelProperty(value = "描述")@Column(comment = "描述")private String desc;@ApiModelProperty(value = "数值")@Column(comment = "数值")private Double value;@ApiModelProperty("创建时间")@TableField(fill = FieldFill.INSERT)@Column(type = MySqlTypeConstant.DATETIME,defaultValue = "CURRENT_TIMESTAMP",comment = "创建时间")private LocalDateTime createTime;@ApiModelProperty("更新时间")@TableField(fill = FieldFill.INSERT_UPDATE)@Column(type = MySqlTypeConstant.DATETIME,defaultValue = "CURRENT_TIMESTAMP",comment = "更新时间")private LocalDateTime updateTime;
}

DDL：

CREATE TABLE `value_table` (`id` bigint NOT NULL COMMENT '编号',`name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '名称',`desc` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '描述',`value` double DEFAULT NULL COMMENT '数值',`create_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',`update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

按这个表进行导出的编写测试：
在这里插入图片描述

实现

实现思路是 先查询表结构及字段注释：首先，需要获取表中每个字段的名称和注释。这可以通过查询 information_schema 来实现。其次是将数据表内的数据进行导出，SELECT INTO OUTFILE 命令将表内容导出为CSV文件。

标题/数据分表

起初的想法是存储过程包含两个参数一个是指定特定导出的表名称和导出csv的文件路径，采用的方法是 第一个SELECT INTO OUTFILE 导出字段名和字段注释。它会生成一个CSV 文件，其中第一行包含字段名称和字段注释。第二个 SELECT INTO OUTFILE 导出表的数据。这个方式的问题是，在第一个成功导出后，第二次导出的表会报错 ‘**.csv’ already exists。就是只有标题，没有数据。
所以将其拆分成了两个表导出，标题表和数据表，代码如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `table_to_csv`(IN p_table_name VARCHAR(255),IN p_file_path VARCHAR(255)
)
BEGINDECLARE v_field_headers TEXT;DECLARE v_title_file_path VARCHAR(512);DECLARE v_data_file_path VARCHAR(512);DECLARE v_table_exists INT DEFAULT 0;-- 增加错误处理DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINSHOW ERRORS;RESIGNAL;END;-- 验证表是否存在SELECT COUNT(*)INTO v_table_existsFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = DATABASE()AND TABLE_NAME = p_table_name;IF v_table_exists = 0 THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = '目标表不存在';END IF;-- 使用更安全的GROUP_CONCAT设置SET SESSION group_concat_max_len = 1048576; -- 1MB-- 获取带列名的注释（使用显式格式）SET v_field_headers = (SELECT GROUP_CONCAT(CONCAT('"', COLUMN_NAME, ' (', IFNULL(COLUMN_COMMENT, '无注释'), ')"')SEPARATOR ', ')FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA = DATABASE()AND TABLE_NAME = p_table_nameORDER BY ORDINAL_POSITION);-- 文件路径处理SET v_title_file_path = CONCAT(p_file_path, '_HEADER_',DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '.csv');SET v_data_file_path = CONCAT(p_file_path, '_DATA_',DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '.csv');-- 使用标准CSV格式（RFC4180）SET @export_header_sql = CONCAT('SELECT ', v_field_headers,' INTO OUTFILE ', QUOTE(v_title_file_path),' FIELDS TERMINATED BY ', QUOTE(','),' OPTIONALLY ENCLOSED BY ', QUOTE('"'),' ESCAPED BY ', QUOTE('"'),' LINES TERMINATED BY ', QUOTE('\r\n'));-- 使用参数化表名SET @export_data_sql = CONCAT('SELECT * FROM `', REPLACE(p_table_name, '`', '``'), '`',' INTO OUTFILE ', QUOTE(v_data_file_path),' FIELDS TERMINATED BY ', QUOTE(','),' OPTIONALLY ENCLOSED BY ', QUOTE('"'),' ESCAPED BY ', QUOTE('"'),' LINES TERMINATED BY ', QUOTE('\r\n'));-- 执行导出操作PREPARE stmt_header FROM @export_header_sql;EXECUTE stmt_header;DEALLOCATE PREPARE stmt_header;PREPARE stmt_data FROM @export_data_sql;EXECUTE stmt_data;DEALLOCATE PREPARE stmt_data;-- 完成标记SELECT CONCAT('导出成功！\n','标题文件: ', v_title_file_path, '\n','数据文件: ', v_data_file_path) AS result;
END

调用方式：

call table_to_csv("value_table", 'D:/**/tmp/value_table');

导出的标题：
在这里插入图片描述

导出的数据：
在这里插入图片描述

导出为完整表

上面的分离方式在初期是勉强能用的状态，但是两个分开的表对于需要结合起来看，或者需要手动合并一次，带来了一些问题，所以很多人提出了导出一个完整的表，方便直接查看和修改。所以就有了导出为一个完整的表。代码如下：

CREATE DEFINER=`root`@`localhost` PROCEDURE `export_table_to_onecsv`(IN p_table_name VARCHAR(255),IN p_file_path VARCHAR(255)
)
BEGINDECLARE v_field_headers TEXT;DECLARE v_combined_path VARCHAR(512);DECLARE v_table_exists INT DEFAULT 0;-- 错误处理DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINSHOW ERRORS;RESIGNAL;END;-- 验证表存在性SELECT COUNT(*)INTO v_table_existsFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = DATABASE()AND TABLE_NAME = p_table_name;IF v_table_exists = 0 THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT = '目标表不存在';END IF;-- 设置GROUP_CONCAT长度SET SESSION group_concat_max_len = 1048576;-- 获取带列名的注释（使用显式格式）SET v_field_headers = (SELECT GROUP_CONCAT(CONCAT('"', COLUMN_NAME, ' (', IFNULL(COLUMN_COMMENT, '无注释'), ')"')SEPARATOR ', ')FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA = DATABASE()AND TABLE_NAME = p_table_nameORDER BY ORDINAL_POSITION);-- 生成带时间戳的文件路径SET v_combined_path = CONCAT(p_file_path, '_',DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '.csv');-- 构建动态SQL（合并标题和数据）SET @export_sql = CONCAT('(',  -- 使用子查询保证顺序'SELECT ', v_field_headers,' UNION ALL ','SELECT * FROM `', REPLACE(p_table_name, '`', '``'), '`',') INTO OUTFILE ', QUOTE(v_combined_path),' FIELDS TERMINATED BY ', QUOTE(','),' OPTIONALLY ENCLOSED BY ', QUOTE('"'),' ESCAPED BY ', QUOTE('"'),' LINES TERMINATED BY ', QUOTE('\r\n'));-- 执行导出PREPARE stmt FROM @export_sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;-- 返回导出结果SELECT CONCAT('导出成功！文件路径: ', v_combined_path) AS result;
END

这个的思路是合并两个CSV文件意味着标题行（字段名和注释）应该作为第一行，接着是所有的数据行。原来的存储过程分别生成了标题和数据文件，现在需要调整逻辑，只生成一个文件。构建标题行，作为一行数据。使用UNION ALL将标题行和实际数据合并。导出合并后的结果集到一个文件。

调用存储过程：

call export_table_to_onecsv("value_table", 'D:/**/tmp/value_table');

导出表格：
在这里插入图片描述

导出库内所以表

上面的操作按理可以更简化，在导出的表数量较多的情况下，每次输入表名和路径也比较繁琐，所以再添加一个存储过程，能一键导出库里的所有表。

CREATE DEFINER=`root`@`localhost` PROCEDURE `export_all_tables_to_csv`(IN p_base_path VARCHAR(255))
BEGINDECLARE v_table_name VARCHAR(255);DECLARE v_done BOOLEAN DEFAULT FALSE;DECLARE v_field_headers TEXT;DECLARE v_export_path VARCHAR(512);DECLARE v_table_counter INT DEFAULT 0;-- 声明游标获取所有表名DECLARE cur_tables CURSOR FOR SELECT TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = DATABASE()AND TABLE_TYPE = 'BASE TABLE';-- 错误处理（继续执行后续表）DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = TRUE;DECLARE CONTINUE HANDLER FOR SQLEXCEPTIONBEGINSHOW ERRORS;SELECT CONCAT('表 ', v_table_name, ' 导出失败') AS error_msg;END;-- 打开游标OPEN cur_tables;-- 开始循环处理表table_loop: LOOPFETCH cur_tables INTO v_table_name;IF v_done THENLEAVE table_loop;END IF;-- 设置动态参数SET SESSION group_concat_max_len = 1048576;-- 获取带列名的注释（使用显式格式）SET v_field_headers = (SELECT GROUP_CONCAT(CONCAT('"', COLUMN_NAME, ' (', IFNULL(COLUMN_COMMENT, '无注释'), ')"')SEPARATOR ', ')FROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA = DATABASE()AND TABLE_NAME = v_table_nameORDER BY ORDINAL_POSITION);-- 生成文件路径（包含表名和时间戳）SET v_export_path = CONCAT(p_base_path,'/',v_table_name,'_',DATE_FORMAT(NOW(), '%Y%m%d%H%i%S'),'.csv');-- 构建动态SQLSET @export_sql = CONCAT('(','SELECT ', v_field_headers,' UNION ALL ','SELECT * FROM `', REPLACE(v_table_name, '`', '``'), '`',') INTO OUTFILE ', QUOTE(v_export_path),' FIELDS TERMINATED BY ', QUOTE(','),' OPTIONALLY ENCLOSED BY ', QUOTE('"'),' ESCAPED BY ', QUOTE('"'),' LINES TERMINATED BY ', QUOTE('\r\n'));-- 执行导出PREPARE stmt FROM @export_sql;EXECUTE stmt;DEALLOCATE PREPARE stmt;SET v_table_counter = v_table_counter + 1;END LOOP;-- 清理资源CLOSE cur_tables;-- 输出总结报告SELECT CONCAT('成功导出 ', v_table_counter, ' 张表\n','文件保存路径: ', p_base_path, '\n','文件名格式: [表名]_[时间戳].csv\n','导出时间: ', NOW()) AS final_report;
END

通过查询INFORMATION_SCHEMA.TABLES来获取当前数据库中的所有表名。使用游标来逐行读取这些表名，并对每个表执行导出到一个csv文件的操作。

调用存储过程：

call export_all_tables_to_csv('D:/**/tmp/');

注意事项

目录权限

输出的文件路径需要是服务器上可访问的路径。需要目录写权限（可通过SHOW VARIABLES LIKE ‘secure_file_priv’;查看）

在执行如上存储过程中可能会提示如下错误：
在这里插入图片描述
报错 1290 -The MySQL server is running with the --secure-file-privoption so it cannot execute this statement

或者
在这里插入图片描述

因为 MySQL 的 --secure-file-priv 选项限制了文件操作（如 SELECT INTO OUTFILE）的路径，这意味着你只能将文件导出到指定的目录。 解决参考办法：

  • 步骤1: 查找允许的文件路径

你可以查看 --secure-file-priv 选项的当前设置，了解允许导出的目录。运行以下 SQL 查询：
SHOW VARIABLES LIKE ‘secure_file_priv’;
如果返回的路径是一个目录，比如 /var/lib/mysql-files/，那么你只能将文件导出到这个目录。 这里我的是返回null

  • 步骤2: 更改 secure-file-priv 设置

如果你有 MySQL 的管理员权限，你可以通过修改 MySQL 配置文件来更改或禁用 --secure-file-priv 选项：
打开 MySQL 配置文件 my.cnf 或 my.ini（通常在 /etc/mysql/ 或 /etc/ 下）。
找到或添加如下配置：

[mysqld]
secure-file-priv=""
# 或者设置一个你想要的路径：
[mysqld]
secure-file-priv="/your/custom/path"

重启 MySQL 服务以使更改生效：

sudo systemctl restart mysql

或者使用：

sudo service mysql restart

这里我是找不到my.cnf 或 my.ini文件，直接新建了一个my.ini内容如下：

[mysqld]
# 设置数据库数据存储路径
datadir=C:/ProgramData/MySQL/MySQL Server 8.0/Data
# 启动时的配置文件路径
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0"

检查 MySQL 服务启动命令
在 Windows 上，MySQL 配置文件路径也可以通过服务启动命令来设置。如果你无法找到配置文件，可以检查 MySQL 服务的启动命令：

  • 打开“服务”管理器：按 Win + R，输入 services.msc，然后按 Enter。

  • 在服务列表中找到 MySQL（或MySQL80，取决于你的安装），右键点击并选择 属性。

  • 在启动参数（或 命令行参数）部分，查看 MySQL启动时是否指定了配置文件路径。例如，可能会看到类似：

--defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"

如果没有则将其添加上(地址改为你新建的my.ini)

如果有指定文件路径，可以根据它找到配置文件。

数据量

如果表数据量非常大，可能需要调整服务器的配置或分批导出数据。大表处理建议增加超时设置：

SET SESSION wait_timeout = 3600;
SET SESSION net_write_timeout = 3600;

或者添加分页导出功能


http://www.ppmy.cn/devtools/159122.html

相关文章

【个人开发】deepseed+Llama-factory 本地数据多卡Lora微调

【个人开发】deepseed+Llama-factory 本地数据多卡Lora微调

文章目录 1.背景2.微调方式2.1 关键环境版本信息2.2 步骤2.2.1 下载llama-factory2.2.2 准备数据集2.2.3 微调模式2.2.4 微调脚本 2.3 踩坑经验2.3.1 问题一：ValueError: Undefined dataset xxxx in dataset_info.json.2.3.2 问题二： ValueError: Target…
阅读更多...
NoSQL数据库-分类与特点

NoSQL数据库-分类与特点

当前出现了很多不同类型、面向不同应用的No SQL产品，按照所使用的数据结构的类型， 一般可以将NoSQL数据库分为以下4种类型。 1.列式存储数据库 行式数据库即传统的关系型数据库，数据按记录存储，每一条记录的所有属性存储在一行。…
阅读更多...
常见的系统架构

常见的系统架构

1. 单体架构（Monolithic Architecture） 定义 单体架构就像是把所有功能都放在一个大盒子里。所有的代码、业务逻辑和数据处理都在同一个应用程序中。 优点 简单易懂：就像一个大玩具箱，所有东西都在一起，容易管理和开发…
阅读更多...
AcWing 798. 差分矩阵

AcWing 798. 差分矩阵

题目来源： 找不到页面 - AcWing 题目内容： 输入一个 n 行 m 列的整数矩阵，再输入 q 个操作，每个操作包含五个整数 x1,y1,x2,y2,c，其中 (x1,y1) 和 (x2,y2)表示一个子矩阵的左上角坐标和右下角坐标。 每个操作都要将…
阅读更多...
C++中函数的调用

C++中函数的调用

************* C topic：call functions ************* Have some busy works recently. I have a doubts the call functions. first take add two integers function as an example. The function is written as the form. 函数类型 函数名(参数类型 参数名)…
阅读更多...
vivo手机和Windows电脑连接同一个WiFi即可投屏！

vivo手机和Windows电脑连接同一个WiFi即可投屏！

虽然现在很多人喜欢刷手机，但是对于长时间需要使用手机办公的人来说，手机屏幕还是太小了，当人一天二十四小时中要花费近十个小时摆弄手机，就会渴望手机屏幕能够大一点，至少看的时候，眼睛舒服一点。 因为嫌弃…
阅读更多...
域名劫持原理与实践

域名劫持原理与实践

了解域名及域名劫持 由于点分十进制的IP地址难于记忆，便出现了域名。由于网络传输中最终还是基于IP，所以必须通过一种机制将IP和域名一一对应起来，这便是DNS。全球总共有13台根域名服务器。 域名劫持是互联网攻击中常见的一种攻击方式&…
阅读更多...
【Linux】进程间关系与守护进程

【Linux】进程间关系与守护进程

文章目录 1. 进程组2. 会话2.1 什么是会话2.2 如何创建会话2.3 守护进程 3. 作业控制 1. 进程组 我们运行下面的命令 sleep 10000 | sleep 20000 | sleep 30000然后查看进程的信息： 可以看到，其实每一个进程除了有进程PID、PPID之外，还属于…
阅读更多...
最新文章

Copyright @ 2022~2023