used - MySQL carga valores NULL de datos CSV

load data local infile the used command is not allowed with this mysql version

Question

Tengo un archivo que puede contener de 3 a 4 columnas de valores numéricos separados por comas. Los campos vacíos se definen con la excepción cuando se encuentran al final de la fila:

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

La siguiente tabla fue creada en 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 | | +-------+--------+------+-----+---------+-------+

Estoy tratando de cargar los datos usando el comando LOAD de MySQL:

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

La tabla resultante:

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

El problema radica en el hecho de que cuando un campo está vacío en los datos brutos y no está definido, MySQL por alguna razón no usa el valor predeterminado de las columnas (que es NULO) y usa cero. NULL se usa correctamente cuando el campo falta por completo.

Desafortunadamente, tengo que ser capaz de distinguir entre NULL y 0 en esta etapa por lo que cualquier ayuda sería apreciada.

Gracias S.

editar

La salida 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

El comportamiento es diferente dependiendo de la configuración de la base de datos. En el modo estricto esto arrojaría un error si no una advertencia. La siguiente consulta se puede usar para identificar la configuración de la base de datos.

mysql> show variables like ''sql_mode'';

Answer 2

Esto hará lo que quieras. Lee el cuarto campo en una variable local, y luego establece el valor real del campo en NULL, si la variable local termina conteniendo una cadena vacía:

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 todos están posiblemente vacíos, entonces los leerá todos en variables y tendrá varias sentencias SET, como esta:

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 3

Preprocesa tu CSV de entrada para reemplazar las entradas en blanco con / N.

Intento de una expresión regular: s / ,, /, / n, / g y s /, $ /, / N / g

Buena suerte.

Answer 4

El manual de MySQL dice:

Al leer datos con LOAD DATA INFILE, las columnas vacías o faltantes se actualizan con ''''. Si desea un valor NULO en una columna, debe usar / N en el archivo de datos. La palabra literal "NULO" también se puede usar bajo ciertas circunstancias.

Entonces necesita reemplazar los espacios en blanco con / N así:

1,2,3,4,5
1,2,3,/N,5
1,2,3