将带有空值的 csv 文件中的值插入到 mysql 表中会导致错误

发布于 2025-01-10 12:02:33 字数 1258 浏览 3 评论 0原文

我有一个如下所示的 CSV 文件：

"Jared","D","James","111111100","1966-10-10","123 Peachtree, Atlanta, GA","M","85000.00","null","6"
"Brad","C","Knight","111111103","1968-02-13","176 Main St., Atlanta, GA","M","44000.00","111111100","6"

我正在尝试使用以下命令输入表：

load data infile '/var/lib/mysql-files/employee.dat' 
into table employee 
fields terminated by ',' enclosed by '"';

这会导致错误：

错误 1452 (23000)：无法添加或更新子行：外键约束失败 (`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`superssn`) REFERENCES `employee` (`ssn`) ）

表员工的创建方式与


DROP TABLE employee;
CREATE TABLE employee (
  fname    varchar(15) not null, 
  minit    varchar(1),
  lname    varchar(15) not null,
  ssn      char(9),
  bdate    date,
  address  varchar(50),
  sex      char,
  salary   decimal(10,2),
  superssn char(9),
  dno      integer(4),
  primary key (ssn),
  foreign key (superssn) references employee(ssn),
  foreign key (dno) references department(number)
);

相同的加载数据 infile 命令适用于具有相同格式的其他一些数据文件

I我认为很可能是引号中的 null 值导致了问题，并且 MySQL 正在尝试将 null 作为字符串插入，从而导致外键冲突。

我可以采取什么措施来解决此问题？

原文

I have a CSV file that looks like this:

"Jared","D","James","111111100","1966-10-10","123 Peachtree, Atlanta, GA","M","85000.00","null","6"
"Brad","C","Knight","111111103","1968-02-13","176 Main St., Atlanta, GA","M","44000.00","111111100","6"

I am trying to make entry into a table with this command:

load data infile '/var/lib/mysql-files/employee.dat' 
into table employee 
fields terminated by ',' enclosed by '"';

This is resulting in an error:

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`superssn`) REFERENCES `employee` (`ssn`))

Table employee is created like


DROP TABLE employee;
CREATE TABLE employee (
  fname    varchar(15) not null, 
  minit    varchar(1),
  lname    varchar(15) not null,
  ssn      char(9),
  bdate    date,
  address  varchar(50),
  sex      char,
  salary   decimal(10,2),
  superssn char(9),
  dno      integer(4),
  primary key (ssn),
  foreign key (superssn) references employee(ssn),
  foreign key (dno) references department(number)
);

The same load data infile command worked for some other data files with the same format

I think it's most likely the null value in quotes that is causing the issue and MySQL is trying to insert the null as a string resulting in a foreign key violation.

What could I do to resolve this issue?

分享到QQ

分享到微博