MySQL charge les valeurs NULL à partir de données CSV

Question

J'ai un fichier qui peut contenir de 3 à 4 colonnes de valeurs numériques qui sont séparées par une virgule. Les champs vides sont définis à l'exception de ceux qui se trouvent à la fin de la ligne :

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

La table suivante a été créée dans MySQL :

+-------+--------+------+-----+---------+-------+
| Field | Type   | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| one   | int(1) | YES  |     | NULL    |       | 
| two   | int(1) | YES  |     | NULL    |       | 
| three | int(1) | YES  |     | NULL    |       | 
| four  | int(1) | YES  |     | NULL    |       | 
| five  | int(1) | YES  |     | NULL    |       | 
+-------+--------+------+-----+---------+-------+

J'essaie de charger les données à l'aide de la commande MySQL LOAD :

load data infile '/tmp/testdata.txt' into table moo fields terminated by "," lines terminated by "\n";

Le tableau qui en résulte :

+------+------+-------+------+------+
| one  | two  | three | four | five |
+------+------+-------+------+------+
|    1 |    2 |     3 |    4 |    5 | 
|    1 |    2 |     3 |    0 |    5 | 
|    1 |    2 |     3 | NULL | NULL | 
+------+------+-------+------+------+

Le problème réside dans le fait que lorsqu'un champ est vide dans les données brutes et n'est pas défini, MySQL, pour une raison quelconque, n'utilise pas la valeur par défaut de la colonne (qui est NULL) et utilise zéro. NULL est utilisé correctement lorsque le champ est totalement absent.

Malheureusement, je dois être en mesure de faire la distinction entre NULL et 0 à ce stade ; toute aide serait donc la bienvenue.

Merci S.

modifier

La sortie de SHOW WARNINGS :

+---------+------+--------------------------------------------------------+
| Level   | Code | Message                                                |
+---------+------+--------------------------------------------------------+
| Warning | 1366 | Incorrect integer value: '' for column 'four' at row 2 | 
| Warning | 1261 | Row 3 doesn't contain data for all columns             | 
| Warning | 1261 | Row 3 doesn't contain data for all columns             | 
+---------+------+--------------------------------------------------------+

Answer 1

Cela fera ce que vous voulez. Elle lit le quatrième champ dans une variable locale, puis définit la valeur réelle du champ à NULL, si la variable locale finit par contenir une chaîne vide :

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,'')
;

S'il est possible qu'ils soient tous vides, alors vous les lisez tous dans des variables et vous avez plusieurs instructions SET, comme ceci :

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,'')
;

Answer 2

Manuel MySQL dit :

Lors de la lecture de données avec LOAD DATA INFILE, les colonnes vides ou manquantes sont mises à jour avec ''. Si vous voulez une valeur NULL dans une colonne, vous devez utiliser \N dans le fichier de données. Le mot littéral "NULL" peut également être utilisé dans certaines circonstances.

Vous devez donc remplacer les blancs par \N comme ça :

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

Answer 3

Le comportement est différent selon la configuration de la base de données. Dans le mode strict, cela entraînerait une erreur ou un avertissement. La requête suivante peut être utilisée pour identifier la configuration de la base de données.

mysql> show variables like 'sql_mode';

Answer 4

Prétraiter votre CSV d'entrée pour remplacer les entrées vides par \N.

Tentative de regex : s/,,/, \n ,/g et s/,$/, \N /g

Bonne chance.

Answer 5

Afficher les variables

Show variables like "`secure_file_priv`";

Note : gardez votre fichier csv dans l'emplacement donné par la commande ci-dessus.

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

Remarque : ici, le date La colonne ' a quelques valeurs vides dans le fichier csv.

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);