MySQL charge les valeurs NULL des données CSV

J’ai un fichier pouvant contenir de 3 à 4 colonnes de valeurs numériques séparées par des virgules. Les champs vides sont définis avec l’exception lorsqu’ils se trouvent à la fin de la ligne:

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

Le tableau suivant a été créé dans MySQL:

 + ------- + -------- + ------ + ----- + --------- + ------- +
 |  Champ |  Type |  Null |  Clé |  Par défaut |  Extra |
 + ------- + -------- + ------ + ----- + --------- + ------- +
 |  un |  int (1) |  OUI |  |  NULL |  | 
 |  deux  int (1) |  OUI |  |  NULL |  | 
 |  trois |  int (1) |  OUI |  |  NULL |  | 
 |  quatre |  int (1) |  OUI |  |  NULL |  | 
 |  cinq |  int (1) |  OUI |  |  NULL |  | 
 + ------- + -------- + ------ + ----- + --------- + ------- +

J’essaie de charger les données en utilisant la commande MySQL LOAD:

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

Le tableau résultant:

 + ------ + ------ + ------- + ------ + ------ +
 |  un |  deux |  trois |  quatre |  cinq |
 + ------ + ------ + ------- + ------ + ------ +
 |  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 des colonnes (qui est NULL) et utilise zéro. NULL est utilisé correctement lorsque le champ est manquant.

Malheureusement, je dois être capable de distinguer NULL et 0 à ce stade, donc toute aide serait appréciée.

Merci S.

modifier

La sortie de SHOW WARNINGS:

 + --------- + ------ + -------------------------------- ------------------------ +
 |  Niveau |  Code |  Message |
 + --------- + ------ + -------------------------------- ------------------------ +
 |  Avertissement |  1366 |  Valeur entière incorrecte: '' pour la colonne 'four' à la ligne 2 | 
 |  Avertissement |  1261 |  La ligne 3 ne contient pas de données pour toutes les colonnes | 
 |  Attention |  1261 |  La ligne 3 ne contient pas de données pour toutes les colonnes | 
 + --------- + ------ + -------------------------------- ------------------------ +

Cela fera ce que vous voulez. Il lit le quasortingème champ dans une variable locale, puis définit la valeur du champ réel sur NULL si la variable locale contient 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,'') ; 

Si elles sont toutes éventuellement vides, vous les lirez toutes en variables et aurez 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,'') ; 

Le 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 espaces par \ N comme ceci:

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

Le comportement est différent selon la configuration de la firebase database. En mode ssortingct, cela provoquerait une erreur sinon un avertissement. La requête suivante peut être utilisée pour identifier la configuration de la firebase database.

 mysql> show variables like 'sql_mode'; 

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

Tentative d’une regex: s / ,, /, \ n, / g et s /, $ /, \ N / g

Bonne chance.