mysql导入/导出csv（带列名）

君心似砂

已于 2022-03-23 17:01:29 修改

阅读量2.4k

点赞数

分类专栏：数据库文章标签：数据库

于 2022-03-21 14:45:24 首次发布

本文链接：https://blog.csdn.net/weixin_43116971/article/details/123634869

版权

数据库专栏收录该内容

6 篇文章 1 订阅

订阅专栏

本文详细介绍了如何使用MySQL进行CSV文件的导入和导出。首先检查并设置`local_infile`权限，然后创建数据库和表，通过`LOAD DATA LOCAL INFILE`命令导入CSV数据。对于导出，重点关注了`secure_file_priv`配置，以及如何正确导出包含列名的CSV文件，避免列名排序问题。提供的代码示例展示了完整的操作流程。

摘要由CSDN通过智能技术生成

mysql导入csv

首先需要看权限

#首先看权限
SHOW GLOBAL VARIABLES LIKE 'local_infile'
#更改权限
SET GLOBAL local_infile = TRUE;

1、创建一个数据库

2、创建表

3、将csv中的数据导入表中

CREATE DATABASE mydb
USE mydb;
DROP TABLE IF EXISTS t3g1 ;
CREATE TABLE t3g1(loanAmnt   FLOAT, interestRate   FLOAT,  installment    FLOAT, homeOwnership  INT,  
  annualIncome   FLOAT, verificationStatus  INT  , issueDate   INT , isDefault   INT,
  dti       FLOAT,  ficoRangeLow   FLOAT,
  revolUtil  FLOAT, earliesCreditLine  INT)
 SELECT * FROM t3g1
 
 #数据导入
 LOAD DATA LOCAL INFILE "C://t3g1.csv" INTO TABLE t3g1
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\n';

mysql导出csv

1.首先看权限

 SHOW VARIABLES LIKE "secure_file_priv"

如果出现null，则为禁止，如果有文件夹目录，则只允许改目录下文件（测试子目录也不行），如果为空，则不限制目录；
打开my.ini配置文件，C:\mysql\mysql-8.0.28-winx64\my.ini

将里面的内容改为（如果没有，直接添加）
#Secure File Priv
[mysqld]
secure-file-priv=

2、选择想要导出的数据

SELECT *
INTO OUTFILE "C://t3g2.csv" 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
FROM a

累死了，把列名加上了！

#我用这个方法，导出的列名是按首字母排序，和数据不能对应
SET @table_name = "a";
SET @schema_name = "mydb";
SET @output_name = CONCAT("C://",@table_name,".csv");
 
SET @cols = NULL;
select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'")) INTO @cols
			from INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_NAME = @table_name AND TABLE_SCHEMA = @schema_name;
 
SET @sql = CONCAT(" SELECT * FROM (SELECT ", @cols, " UNION ALL SELECT * FROM ", @table_name, ") as r ",
				  " INTO OUTFILE '", @output_name, 
				  "' FIELDS TERMINATED BY '\\t' ");
 
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

#table_name：要导出的表名

#schema_name：数据库名

#output：导出路径，

我用上面的代码导出的csv中列名和数据不能对应。我找了很多资料，如果有和我出现一样问题的，可是试下面这个代码。

SET @table_name = "a";
SET @schema_name = "mydb";
SET @output_name = CONCAT("C://",@table_name,".csv");
 
SET @cols = NULL;
select GROUP_CONCAT(CONCAT("'",COLUMN_NAME,"'") ORDER BY ordinal_position SEPARATOR ",") INTO @cols
			from INFORMATION_SCHEMA.COLUMNS
			WHERE TABLE_NAME = @table_name AND TABLE_SCHEMA = @schema_name;
 
SET @sql = CONCAT(" SELECT * FROM (SELECT ", @cols, " UNION ALL SELECT * FROM ", @table_name, ") as r ",
				  " INTO OUTFILE '", @output_name, 
				  "' FIELDS TERMINATED BY '\\t' ");
 
 
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;