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

(variable1, @variable2, ..) SET variable2 = nullif(@variable2, '' ou ' ') >> vous pouvez mettre n'importe quelle condition

Answer 2

Convertit le fichier d'entrée pour inclure \N pour les données de la colonne vide en utilisant la commande sed ci-dessous dans le terminal UNix :

sed -i 's/,,/,\\N,/g' $file_name

et ensuite utiliser LOAD DATA INFILE commande pour charger dans mysql

Answer 3

J'ai juste une autre suggestion à faire : mettez à jour le tableau après qu'il ait été chargé :

UPDATE mytable SET field = NULL WHERE field = ''

Je fais cela dans une situation où il y a plusieurs types de tableaux différents qui pourraient être téléchargés. Le sql de chargement (et de mise à jour) est construit dynamiquement (en php) en fonction du type de table. Bien sûr, vous pourriez aussi faire la même chose avec la suggestion de Duncan. Ce n'est donc pas la solution la plus élégante qui soit, mais c'est une autre option.