Curso de MariaDB / Importar y exportar CSV: rutas, permisos y UTF-8

Para importar CSV, ajuste separadores, comillas, finales de línea y codificación, valide primero los valores originales en una tabla intermedia y conviértalos después a los tipos de destino. LOAD DATA LOCAL INFILE lee un archivo del cliente; SELECT INTO OUTFILE escribe en el servidor, por lo que sus rutas y permisos son diferentes.

Identificar qué equipo lee o escribe el archivo

Operación Ubicación del archivo Requisitos
LOAD DATA LOCAL INFILE Equipo cliente LOCAL permitido por cliente y servidor
LOAD DATA INFILE Servidor de base de datos FILE y política de rutas del servidor
SELECT INTO OUTFILE Servidor de base de datos FILE, secure_file_priv y permisos del sistema operativo

Permita LOCAL únicamente para un servidor de confianza y tenga en cuenta qué archivos del cliente puede leer y enviar. No relaje todos los permisos ni opciones globales para ocultar un error de importación. La guía del cliente y los archivos SQL explica las rutas, y la práctica de INSERT ayuda a revisar el mapeo entre columnas y valores.

Preparar un archivo UTF-8 y una tabla de texto original

Cree un archivo nuevo products.csv en C:/mariadb-lab del equipo cliente sin sobrescribir otro existente. Guarde el contenido siguiente como UTF-8. La primera fila es la cabecera; el primer nombre coreano, que significa «cuaderno grande», contiene una coma y necesita comillas.

product_id,product_name,price
1,"노트, 대형",3500.00
2,"머그컵",8000.00
3,"볼펜",1500.00

Conéctese como course_user al servidor de prácticas, seleccione shopcourse y compruebe SELECT VERSION(), DATABASE();. Verifique que csv45_stage y csv45_products no existen. Si existen, revise sus datos y definiciones antes de decidir si son prescindibles. No reemplace tablas de una aplicación.

La tabla intermedia, denominada staging, conserva texto original para revisarlo antes de convertir números. Prepare ambas tablas:

USE shopcourse;
CREATE TABLE csv45_stage(raw_id TEXT,raw_name TEXT,raw_price TEXT)
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE csv45_products(product_id INT PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL CHECK(price>=0))
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Cargar el archivo del cliente y revisar advertencias

Desde la terminal del sistema operativo, abra el cliente con LOCAL permitido para este servidor de prácticas de confianza. Ajuste el puerto si no utiliza 3306 e introduzca la contraseña cuando se solicite:

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --local-infile=1 --default-character-set=utf8mb4 shopcourse

Dentro del cliente SQL, ejecute el bloque siguiente. SHOW WARNINGS debe consultar las advertencias inmediatamente después de la carga, antes de que otra sentencia sustituya esa información.

LOAD DATA LOCAL INFILE 'C:/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
SELECT * FROM csv45_stage;
SELECT * FROM csv45_stage
WHERE raw_id IS NULL OR raw_id NOT REGEXP '^[0-9]+$'
 OR raw_price IS NULL OR raw_price NOT REGEXP '^[0-9]+([.][0-9]{1,2})?$'
 OR raw_name IS NULL OR raw_name='';
SELECT CAST(raw_id AS UNSIGNED) AS normalized_id,COUNT(*)
FROM csv45_stage GROUP BY CAST(raw_id AS UNSIGNED) HAVING COUNT(*)>1;
-- After format checks pass, verify numeric ranges and name length
SELECT * FROM csv45_stage
WHERE CAST(raw_id AS DECIMAL(65,0))<1
 OR CAST(raw_id AS DECIMAL(65,0))>2147483647
 OR CAST(raw_price AS DECIMAL(65,2))>99999999.99
 OR CHAR_LENGTH(raw_name)>100;

La tabla debe contener tres filas y las tres consultas de validación deben quedar vacías. Si la política local_infile del servidor impide cargar el archivo, revísela con la administración. El modo estricto no garantiza que una importación LOCAL rechace cualquier entrada incorrecta; compruebe las advertencias y los textos originales.

El ejemplo separa líneas por LF y elimina un CR final del campo de precio, por lo que también contempla el caso sencillo habitual de archivos CRLF. Los saltos de línea dentro de campos y las convenciones de escape más complejas requieren acuerdo con la herramienta productora. Ejecute primero las comprobaciones de formato; después interprete normalización numérica, duplicados e intervalos.

Si el cliente está en Ubuntu, cree un directorio de trabajo en la terminal y guarde allí el CSV UTF-8:

mkdir -p "$HOME/mariadb-lab"
printf '%s\n' "$HOME/mariadb-lab/products.csv"

Sustituya la ruta LOCAL por la ruta absoluta real que imprimió el comando anterior. El bloque siguiente supone, solo como ejemplo, que el usuario de Ubuntu se llama learner.

LOAD DATA LOCAL INFILE '/home/learner/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;

Ejecute únicamente la carga de Windows o la de Ubuntu que corresponda a su cliente. Cargar ambas añade filas repetidas a la tabla intermedia. La ruta de exportación del servidor Ubuntu se comprueba por separado más adelante.

La referencia oficial de LOAD DATA INFILE describe LOCAL, los permisos y la interpretación del archivo.

Transferir únicamente valores validados a las columnas tipadas

Exija resultados vacíos en las comprobaciones de formato, duplicados normalizados e intervalos. Los ID deben ser positivos y caber en INT con signo, los precios en DECIMAL(10,2) y los nombres tener como máximo 100 caracteres. Los textos 01 y 1 se convierten al mismo ID: la unicidad debe revisarse después de normalizar, no solo comparando textos.

Si una comprobación falla, corrija el archivo o defina una política explícita antes de ejecutar INSERT. No ejecute la conversión solo porque las tablas ya estén creadas.

INSERT INTO csv45_products(product_id,product_name,price)
SELECT CAST(raw_id AS UNSIGNED),raw_name,CAST(raw_price AS DECIMAL(10,2))
FROM csv45_stage;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id;

Deben aparecer tres productos. El primer nombre debe conservar su coma, y los otros nombres significan taza y bolígrafo. Repita la consulta y compare campo por campo con el CSV. Volver a cargar el mismo archivo puede añadir más filas a staging; conserve el origen y el diagnóstico antes de limpiar un intento fallido.

Exportar un CSV en el servidor con una cuenta autorizada

Utilice una cuenta administrativa o de exportación autorizada con FILE. Compruebe secure_file_priv, el permiso de escritura del proceso del servidor y un nombre de archivo nuevo. En un servidor remoto, la ruta C: pertenece a ese servidor, no al equipo cliente.

SELECT @@secure_file_priv;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE 'C:/mariadb-lab/products-export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

El archivo contiene tres registros CSV sin cabecera. No se sobrescribe un archivo existente. Si necesita cabecera, añádala mediante un procedimiento de exportación explícito; cambiar la extensión de la salida tabulada de mariadb --batch a .csv no la convierte en CSV.

En un servidor Ubuntu, consulte también secure_file_priv y los permisos de escritura de la cuenta del servicio. Si el directorio aprobado es /var/lib/mysql-files/, puede usar un archivo nuevo como el siguiente. No es el directorio personal del cliente.

SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE '/var/lib/mysql-files/products-export-new.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Sustituya el directorio por el realmente aprobado y ejecute una sola exportación en ese servidor. No borre archivos existentes ni amplíe FILE simplemente para evitar un rechazo. La referencia oficial de SELECT INTO OUTFILE explica los requisitos y el formato de salida.

Comprobar los campos de ida y vuelta y limpiar las prácticas

Compare las comas, los caracteres Unicode, las cadenas vacías, los NULL y los saltos de línea además del número de filas. Las herramientas CSV no comparten siempre una convención para NULL: defina si un campo vacío representa texto vacío o un dato ausente. Este juego de datos utiliza deliberadamente tres campos con valores en cada fila.

Determine por separado si necesita conservar los archivos de origen y exportación. Elimine las siguientes tablas solo si son objetos prescindibles de este ejercicio. DROP TABLE borra estructuras y datos y no se revierte con un ROLLBACK ordinario; eliminar tablas no elimina los archivos CSV.

DROP TABLE shopcourse.csv45_products;
DROP TABLE shopcourse.csv45_stage;