MYSQL中将搜索结果导出至文件

最新推荐文章于 2022-11-03 11:01:07 发布

合理摸鱼健康长寿

最新推荐文章于 2022-11-03 11:01:07 发布

阅读量488

点赞数

分类专栏： mysql 文章标签： mysql excel csv 分组导出

本文链接：https://blog.csdn.net/orck_chen/article/details/83147708

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

1.修改mysql导出数据本地所在位置

a.找到mysql的安装目录

b.打开my.ini文件

c.找到secure_file_priv="E:/MYSQL"

d.修改导出路径

2.导出单文件

示例表：

/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50714
Source Host           : localhost:3306
Source Database       : cpptest

Target Server Type    : MYSQL
Target Server Version : 50714
File Encoding         : 65001

Date: 2018-10-18 15:14:49
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for users
-- ----------------------------
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`type` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

-- ----------------------------
-- Records of users
-- ----------------------------
INSERT INTO `users` VALUES ('1', 'admin', '1');
INSERT INTO `users` VALUES ('2', 'ppc', '2');
INSERT INTO `users` VALUES ('3', 'aaa', '1');
INSERT INTO `users` VALUES ('4', 'bbb', '2');

sql语句导出表数据（带标题行）

a.导出csv

SELECT * FROM ( select '编号','姓名','类型' 
		union select id,username,type from users WHERE type=1) a 
INTO OUTFILE 'E:/MYSQL/orders1.csv'
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n';

b.导出Excel

SELECT * FROM ( select '编号','姓名','类型' 
		union select id,username,type from users WHERE type=1) a 
INTO OUTFILE 'E:/MYSQL/orders1.xls';

3.根据分组导出文件

示例：导出Excel（存储过程使用游标完成循环）

create procedure cursor_test()
BEGIN  
    DECLARE df1 smallint(6);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT type FROM users GROUP BY type;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
    OPEN cur;
    read_loop : LOOP
        FETCH cur INTO df1;
        IF done THEN
            LEAVE read_loop;
        END IF;

        SET @sql_cpp = CONCAT("SELECT * FROM ( select '编号','姓名','类型' 
				union select id,username,type from users WHERE type=",df1,") temp 
        INTO OUTFILE 'E:/MYSQL/orders",df1,".xls'");

        PREPARE sqlcpp FROM @sql_cpp;
        EXECUTE  sqlcpp;
        COMMIT;
        END LOOP read_loop;
	CLOSE cur;
END
--------------------------------
调用存储过程：
call `cursor_test`();