MySQL carga valores NULOS de datos CSV
Tengo un archivo que puede contener de 3 a 4 columnas de valores numéricos que están separados por comas. Los campos vacíos se definen con la excepción cuando están 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 de carga 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 sin procesar y no está definido, MySQL por alguna razón no usa el valor predeterminado de columnas (que es NULL) y usa cero. NULL se usa correctamente cuando el campo no está completo.
Desafortunadamente, tengo que ser capaz de distinguir entre NULL y 0 en esta etapa para que cualquier ayuda sea apreciada.
Gracias S.
Editar
La salida de MOSTRAR ADVERTENCIAS:
+---------+------+--------------------------------------------------------+ | 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 | +---------+------+--------------------------------------------------------+
4 answers
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ía todos en variables y tendría 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,'')
;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-07-29 06:53:37
El manual de MySQL dice:
Al leer datos con DATOS de CARGA Las columnas INFILES, vacías o faltantes son actualizado con ". Si quieres un NULL valor en una columna, debe usar \N en el archivo de datos. La palabra literal "NULL" también se puede utilizar bajo algunos circunstancia.
Así que necesitas reemplazar los espacios en blanco con \N de esta manera:
1,2,3,4,5
1,2,3,\N,5
1,2,3
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-04-20 13:44:29
El comportamiento es diferente dependiendo de la configuración de la base de datos. En el modo estricto esto lanzaría un error o una advertencia. La siguiente consulta se puede utilizar para identificar la configuración de la base de datos.
mysql> show variables like 'sql_mode';
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-01-22 17:10:39
Preprocese el 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.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2010-04-20 13:29:38