目录
Mysql select into outfile NULL值导出的处理方法
1简介
基于mysql select into outfile的方式导出文本文件的处理方法,这里主要针对里面NULL的处理。
2 准备
2.1 环境说明
1 mysql版本10.3.13-MariaDB,同理其它mysql版本效果类似。
-- 查看mysql版本
SELECT VERSION() AS mysql_Version;
mysql_Version |
10.3.13-MariaDB |
-- mysql 字符集
show variables like '%char%';
Variable_name | Value |
character_set_client | utf8 |
character_set_connection | utf8 |
character_set_database | utf8 |
character_set_filesystem | binary |
character_set_results | |
character_set_server | utf8 |
character_set_system | utf8 |
character_sets_dir | E:\Program Files\MariaDB 10.3\share\charsets\ |
3 安装
无.
4 配置
无
5 使用
5.1 验证实验
1) mysql里创建一个demo表定义id和长度为6的name字段,插入有NULL值的数据验证
CREATE TABLE demo(id int,name varchar(6),PRIMARY KEY (id)) DEFAULT CHARSET=utf8;
注:
- 如果character_set_database已经是utf8,那么这里建表时可以不加DEFAULT CHARSET=utf8.
- 修改character_set_database参数可以在my.ini或者my.cnf里添加或者修改如下内容:
[client]
default-character-set=utf8
INSERT INTO demo VALUES(10,'中国');
INSERT INTO demo VALUES(11,'中国安徽合肥');
INSERT INTO demo VALUES(13,'abcdef');
INSERT INTO demo VALUES(14,NULL);
-- 查看数据
SELECT * FROM demo;
id | name |
10 | 中国 |
11 | 中国安徽合肥 |
13 | abcdef |
14 |
2) 通过mysql select outfile的方式导出数据
SELECT id,name into outfile "D:\\temp\\demo.txt" fields terminated by "\t" lines terminated by '\r\n'
from demo;
id | name |
10 | 中国 |
11 | 中国安徽合肥 |
13 | abcdef |
14 | \N |
3) 通过mysql select outfile 配合ESCAPED BY ''导出数据
SELECT id,name into outfile "D:\\temp\\demo1.txt" fields terminated by "\t" ESCAPED BY '' lines terminated by '\r\n'
from demo;
id | name |
10 | 中国 |
11 | 中国安徽合肥 |
13 | abcdef |
14 | NULL |
注: 这里的NULL是字符串,如果导入其它数据库需要加相关参数对NULL进行转义。
3) 通过mysql select outfile 配合IFNULL导出数据
SELECT id,IFNULL(name,'') name into outfile "D:\\temp\\demo2.txt" fields terminated by "\t" lines terminated by '\r\n'
from demo;
id | name |
10 | 中国 |
11 | 中国安徽合肥 |
13 | abcdef |
14 |
综上所述建议采用第三种方式:将字段里可能为空的字段加IFNULL的转换后再导入其它数据库,如果只是mysql数据库间的转换则不需要这么转换。
5.2 问题分析
无
6 延展
--拼接IFNULL 结合SELECT outfile 的示例。
-- 这里对COLUMN_NAME里定义为DESC、DEFAULT等SQL关键字的情况,需要加`COLUMN_NAME`转义
SELECT TABLE_NAME,
concat('SELECT ',
group_concat(fin order by IS_NULLABLE separator ',')
,' into outfile "D:/temp/demo5.txt" fields terminated by "\\t" lines terminated by \'\\r\\n\' from ',TABLE_NAME
,';') concat_str
FROM
(
SELECT TABLE_NAME,COLUMN_NAME,IS_NULLABLE,
CASE WHEN IS_NULLABLE='NO' THEN
group_concat(COLUMN_NAME order by ORDINAL_POSITION separator ',')
WHEN IS_NULLABLE='YES' THEN
group_concat(CONCAT('IFNULL(',COLUMN_NAME,',\'\')') order by ORDINAL_POSITION separator ',')
END fin
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA='test'
AND TABLE_NAME='demo'
GROUP BY TABLE_NAME,IS_NULLABLE
)A
GROUP BY TABLE_NAME
concat_str |
SELECT id,IFNULL(name,'') into outfile "D:/temp/demo5.txt" fields terminated by "\t" lines terminated by '\r\n' from demo; |
7 总结
暂无.有问题可以咨询如下: