MYSQL LOAD DATA导入数据时对于“空值”处理异常。

wjl123 2011-03-13 04:49:57
问题背景如下：

目标数据库表字段定义：
CREATE TABLE `TABLE_NAME` (
`f1` varchar(25) default NULL,
`f2` varchar(20) default NULL,
`f3` double(15,0) default -1,
`f4` varchar(20) default NULL,
`f5` double(15,3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

源文件内容：【文件名：example_data1.txt】
cat;red;2.0;tree

用面命令可以成功导入数据到表中：
load data infile 'D:\\my_program\\Tek_sig\\example_data1.txt'
into table TABLE_NAME
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
(f1,f2,f3)

结果OK：
Query OK, 1 rows affected (210 ms)

“空值”导入问题：

但是将源文件内容改为：

cat;red;;tree 【即将‘2.0’数值删除，即f3字段为空了】

再次用上面的LOAD DATA 命令导入，则系统报错：

Data truncated for column 'f3' at row 1

这是为什么？我在数据库表的字段“f3”的定义中不是可以为空吗（`f3` double(15,0) default -1,）？而且DEFAULT值为-1,难道不起作用吗？我想实现的效果是，如果在源文件中的f3字段没有值，则导入数据后f3字段的内空应为“-1”。

谁能帮我分析一下，能否实现这一功能需求？

多谢！
...全文
2615 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
wjl123 2011-03-13
  • 打赏
  • 举报
 回复
哦，好的,多谢！

还有刚才您给的这个语句，我又试了一下，没有问题，是对的！不知道刚才为何报错。

load data infile 'D:\\my_program\\Tek_sig\\example_data1.txt'
into table TABLE_NAME
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
(f1,f2,@f3,f4)
set f3=if(@f3='',-1,@f3);

再有，当多个字段均可能“有值”或“为空”时，导入命令如下，在此给大家分享一下：
例如数据库表定义如下：
CREATE TABLE `table_name` (
`f1` double(10,2) default '-8.00',
`f2` varchar(20) default NULL,
`f3` double(15,0) default '-4',
`f4` varchar(20) default NULL,
`f5` double(15,3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

数据源文件：example_data1.txt (设只有两条记录)
;red;;tree 【f1为空值， f3为空值，没有f5字段】
9.2;red;;tree 【f1为非空值，f3为空值，没有f5字段】
寻入这种不规则的数据记录，MYSQL完全可以实现类以ACCESS的智能导入功能，可用以如下命令导入：
load data infile 'D:\\my_program\\Tek_sig\\example_data1.txt'
into table TABLE_NAME
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
(@f1,f2,@f3,f4)
set f1=if(@f1='',-5,@f1),f3=if(@f3='',-1,@f3);

Query OK, 2 rows affected (551 ms) 【成功导入】

数据库中表中导入的内容如下：
f1 f2 f3 f4 f5
-5 red -1 tree NULL
9.2 red -1 tree NULL
ACMAIN_CHM 2011-03-13
  • 打赏
  • 举报
 回复
[Quote]在网上查了一下，没有查到相关的关于SET的用法，好象SQL语句中也支持判别语句，这个部知识内容您看我应当参考一些什么资料？是PLSQL吗？[/Quote]MYSQL官方手册
wjl123 2011-03-13
  • 打赏
  • 举报
 回复

目标数据库表字段定义：
CREATE TABLE `TABLE_NAME` (
`f1` varchar(25) default NULL,
`f2` varchar(20) default NULL,
`f3` double(15,0) default -4,
`f4` varchar(20) default NULL,
`f5` double(15,3) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
您给的指令有些小问题，正确的是这样的：
load data infile 'D:\\my_program\\Tek_sig\\example_data1.txt'
into table TABLE_NAME
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
(f1,f2,@f3,f4)
set f3=if(f3='',-1,f3);
这次OK了，但是也没有将-1传给数据库中的字段f3，而是用的DEFAUTL值 -4 赋给了f3 。

要是多个字段均有可能“有值或无值”呢？
这部分的知识是PLSQL的内容吗？在网上查了一下，没有查到相关的关于SET的用法，好象SQL语句中也支持判别语句，这个部知识内容您看我应当参考一些什么资料？是PLSQL吗？
多谢！




ACMAIN_CHM 2011-03-13
  • 打赏
  • 举报
 回复
试一下你自己的语句。
wjl123 2011-03-13
  • 打赏
  • 举报
 回复
哦。但是，在我的源始数据中，f3这个字段有些记录是有值的，有些记录是没有值的，全部混在一起的。例如：
源文件内容：【文件名：example_data1.txt】
cat;red;2.0;tree 【f3有值】
fly;pink;;oil 【f3无值】
;black;100;milk 【f1无值，f3有值】
cattle;grey;;; 【f3无值,f4无值】
........

即任意字段均可能“有值”或“无值”（空），总之没有规律，我想让它们在没有值时就用我数据库中定义的DEFAULT值，这一功能也可以用您给的这个方法导入吗？例如：

load data infile 'D:\\my_program\\Tek_sig\\example_data1.txt'
into table TABLE_NAME
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
(@f1,@f2,@f3)
set f1=if(@f1='',NULL,@f1),
f2=if(@f2='',NULL,@f2),
f3=if(@f3='',NULL,@f3);
是这样写吗？



ACMAIN_CHM 2011-03-13
  • 打赏
  • 举报
 回复
如果为了实现你想要的结果，则可以使用下面语句。

load data infile 'D:\\my_program\\Tek_sig\\example_data1.txt'
into table TABLE_NAME
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY ''
LINES TERMINATED BY '\n'
(f1,f2,@f3)
set f3=if(@f3='',-1,@f3)


ACMAIN_CHM 2011-03-13
  • 打赏
  • 举报
 回复
因为你现在给的是一个 cat;red;;tree ，其中F3对应的是一个空字符 '' ,并不是NULL。 而MYSQL中的默认值仅在你不给这个字段赋值的时候才起作用，如果你给它赋NULL值，则MYSQL仍会接收这个NULL值，而不是去使用默认值。

insert into TABLE_NAME (f1,f2) values (... 此时会使用默认值
insert into TABLE_NAME (f1,f2,f3) values ('a','b',null), 这里显然已经指定了NULL值了，就不会使用DEFAULT了。

wjl123 2011-03-13
  • 打赏
  • 举报
 回复
我不太了解这里的规矩，按您说的我去结贴了，之前您给我回复的TOPIC:
“用Mysql LOAD DATA导入数据时，是否要求源文本字段数量和表中列字段的一样多？[问题点数:100分]”，
我已经给结贴了，这个贴子中的第二个问题，没人给我回复，所以我又重发了一个问题，看看大家谁能帮我解决。

多谢大家的支持。CSDN真是一个比较好的平台，让我拥有了强大的技术支持团队！再次感谢！
MySQL load data 快速导入大批量数据
MySQL load data 语句能快速将一个文本文件的内容导入到对应的数据库表中
mysql loaddata拆分导入_mysql导入数据load data infile用法整理
有时候我们需要将大量数据批量写入数据库，直接使用程序语言和Sql写入往往很耗时间，其中有一种方案就是使用MySql Load data infile导入文件的形式导入数据，这样可大大缩短数据导入时间。假如是从MySql客户端调用，将客户端的文件导入，则需要使用 load local data infile.LOAD DATA INFILE 语句以很高的速度从一个文本文件中读取行到一个表中。文件名必...
mysql 远程load data_mysql导入数据load data infile用法整理
有时候我们需要将大量数据批量写入数据库，直接使用程序语言和Sql写入往往很耗时间，其中有一种方案就是使用MySql Load data infile导入文件的形式导入数据，这样可大大缩短数据导入时间。假如是从MySql客户端调用，将客户端的文件导入，则需要使用 load local data infile.LOAD DATA INFILE 语句以很高的速度从一个文本文件中读取行到一个表中。文件名必...
【mysql】load data 避雷针
@Hadi 初来乍到 多多点赞 文章目录@Hadi一、前言load data - 简介load data - 语法二、流程1、环境确认2、注意空值问题写在最后 一、前言 最近都会PO出一些 关于各类数据库ETL过程的经验总结 load data - 简介 MySQL load data 语句能快速将一个文本文件的内容导入到对应的数据库表中（一般文本的一行对应表的一条记录）。 数据库应用程序开发中，涉及大批量数据需要插入时，使用 load data 语句的效率比一般的 insert 语句的*高很多
故障分析 | MySQL 使用 load data 导入数据错误的一个场景
作者：刘晨网名 bisal ，具有十年以上的应用运维工作经验，目前主要从事数据库应用研发能力提升和技术管理相关的工作，Oracle ACE ，腾讯云TVP，拥有 Oracle OCM & OCP 、EXIN DevOps Master 、SCJP 等国际认证，国内首批 Oracle YEP 成员，OCMU 成员，《DevOps 最佳实践》中文译者之一，CSDN & ITPub 专家博主，公众号"bisal的个人杂货铺"，长期坚持分享技术文章，多次在线上和线下分享技术主题。本文来源：原创投稿。
MySQL

56,677

社区成员

56,708

社区内容

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章