CSV 데이터에서 MySQL로드 NULL 값

CSV 데이터에서 MySQL로드 NULL 값

---

쉼표로 구분 된 3-4 열의 숫자 값을 포함 할 수있는 파일이 있습니다. 빈 필드는 행 끝에있을 때 예외로 정의됩니다.

1,2,3,4,5
1,2,3,,5
1,2,3

다음 테이블은 MySQL에서 생성되었습니다.

+ ------- + -------- + ------ + ----- + --------- + ------- +
| 분야 | 타입 | 널 | 키 | 기본 | 추가 |
+ ------- + -------- + ------ + ----- + --------- + ------- +
| 하나 | int (1) | 예 | | NULL | |
| 두 | int (1) | 예 | | NULL | |
| 세 | int (1) | 예 | | NULL | |
| 네 | int (1) | 예 | | NULL | |
| 다섯 | int (1) | 예 | | NULL | |
+ ------- + -------- + ------ + ----- + --------- + ------- +

MySQL LOAD 명령을 사용하여 데이터를로드하려고합니다.

LOAD DATA INFILE '/tmp/testdata.txt' INTO TABLE moo FIELDS 
TERMINATED BY "," LINES TERMINATED BY "\n";

결과 테이블 :

+ ------ + ------ + ------- + ------ + ------ +
| 하나 | 두 | 세 | 네 | 다섯 |
+ ------ + ------ + ------- + ------ + ------ +
| 1 | 2 | 3 | 4 | 5 |
| 1 | 2 | 3 | 0 | 5 |
| 1 | 2 | 3 | NULL | NULL |
+ ------ + ------ + ------- + ------ + ------ +

문제는 원시 데이터에서 필드가 비어 있고 정의되지 않은 경우 어떤 이유로 MySQL은 열 기본값 (NULL)을 사용하지 않고 0을 사용한다는 사실에 있습니다. 필드가 모두 누락 된 경우 NULL이 올바르게 사용됩니다.

불행히도,이 단계에서 NULL과 0을 구별 할 수 있어야 도움을 얻을 수 있습니다.

고마워요

편집하다

SHOW WARNINGS의 출력 :

+ --------- + ------ + -------------------------------- ------------------------ +
| 레벨 | 코드 | 메시지 |
+ --------- + ------ + -------------------------------- ------------------------ +
| 경고 | 1366 | 잘못된 정수 값 : 2 행의 '4'열에 대해 ''|
| 경고 | 1261 | 행 3에 모든 열에 대한 데이터가 포함되어 있지 않습니다 |
| 경고 | 1261 | 행 3에 모든 열에 대한 데이터가 포함되어 있지 않습니다 |
+ --------- + ------ + -------------------------------- ------------------------ +

---

이것은 당신이 원하는 것을 할 것입니다. 네 번째 필드를 로컬 변수로 읽은 다음 로컬 변수가 빈 문자열을 포함하는 경우 실제 필드 값을 NULL로 설정합니다.

LOAD DATA infile '/tmp/testdata.txt'
INTO TABLE moo
fields terminated BY ","
lines terminated BY "\n"
(one, two, three, @vfour, five)
SET four = nullif(@vfour,'')
;

그것들이 모두 비어있는 경우, 변수로 모두 읽고 다음과 같이 여러 SET 문을 갖습니다.

LOAD DATA infile '/tmp/testdata.txt'
INTO TABLE moo
fields terminated BY ","
lines terminated BY "\n"
(@vone, @vtwo, @vthree, @vfour, @vfive)
SET
one = nullif(@vone,''),
two = nullif(@vtwo,''),
three = nullif(@vthree,''),
four = nullif(@vfour,'')
;

---

MySQL 매뉴얼 은 말합니다 :

LOAD DATA INFILE로 데이터를 읽을 때 비어 있거나 누락 된 열은 ''로 업데이트됩니다. 열에 NULL 값이 필요한 경우 데이터 파일에 \ N을 사용해야합니다. 상황에 따라 문자 "NULL"을 사용할 수도 있습니다.

So you need to replace the blanks with \N like this:

1,2,3,4,5
1,2,3,\N,5
1,2,3

---

The behaviour is different depending upon the database configuration. In the strict mode this would throw an error else a warning. Following query may be used for identifying the database configuration.

mysql> show variables like 'sql_mode';

---

Preprocess your input CSV to replace blank entries with \N.

Attempt at a regex: s/,,/,\n,/g and s/,$/,\N/g

Good luck.

---

show variables

Show variables like "`secure_file_priv`";

Note: keep your csv file in location given by the above command.

create table assessments (course_code varchar(5),batch_code varchar(7),id_assessment int, assessment_type varchar(10), date int , weight int);

Note: here the 'date' column has some blank values in the csv file.

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/assessments.csv' 
INTO TABLE assessments
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '' 
LINES TERMINATED BY '\n' 
IGNORE 1 ROWS 
(course_code,batch_code,id_assessment,assessment_type,@date,weight)
SET date = IF(@date = '', NULL, @date);

참고URL : https://stackoverflow.com/questions/2675323/mysql-load-null-values-from-csv-data