Corso MariaDB / Importare ed esportare CSV: percorsi, privilegi e codifica
Importare un CSV facendo corrispondere separatori, virgolette, terminatori di riga e codifica, verificando i testi in una tabella intermedia prima di convertirli nei tipi di destinazione. LOAD DATA LOCAL INFILE legge un file del client, mentre SELECT INTO OUTFILE scrive sul server: percorsi e privilegi richiesti sono diversi.
Individuare il computer che legge o scrive il file
CSV, comma-separated values, è un formato testuale che nell'esempio separa i campi con virgole. Un valore contenente una virgola richiede le virgolette secondo le regole concordate. Anche codifica e terminatori di riga fanno parte del formato da rispettare.
| Operazione | Posizione del file | Requisiti |
|---|---|---|
| LOAD DATA LOCAL INFILE | Computer del client | LOCAL consentito da client e server |
| LOAD DATA INFILE | Server del database | Privilegio FILE e regole sui percorsi del server |
| SELECT INTO OUTFILE | Server del database | FILE, secure_file_priv e permessi del sistema operativo |
Abilitare LOCAL soltanto verso server attendibili, considerando quali file del client possono essere letti e inviati. Non allentare impostazioni globali o permessi dei file soltanto per eliminare un errore di importazione. La guida al client e ai percorsi dei file SQL descrive il lato client; la guida a INSERT spiega la corrispondenza delle colonne di destinazione.
I riferimenti MariaDB di LOAD DATA INFILE e SELECT INTO OUTFILE descrivono operazioni e limiti.
Preparare un file UTF-8 e una tabella di testi grezzi
Creare un nuovo products.csv in C:/mariadb-lab sul computer client, conservando eventuali file esistenti. Salvare il contenuto seguente in UTF-8. La prima riga denomina tre campi; il primo prodotto significa quaderno grande e contiene una virgola, quindi è racchiuso tra virgolette.
product_id,product_name,price
1,"노트, 대형",3500.00
2,"머그컵",8000.00
3,"볼펜",1500.00
Collegarsi al server di esercitazione, controllare SELECT VERSION(), DATABASE(); e selezionare shopcourse. Verificare con SHOW TABLES che csv45_stage e csv45_products non esistano; se esistono, esaminarle prima di considerarle eliminabili. Caricare prima il testo per evitare che una conversione numerica nasconda subito un valore errato.
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;
Caricare il file client e controllare gli avvisi prima della conversione
Eseguire il comando client seguente nel terminale del sistema operativo e inserire la password al prompt. Adeguare la porta se il server usa un valore diverso. Dopo la connessione eseguire le istruzioni SQL successive nel client MariaDB.
mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --local-infile=1 --default-character-set=utf8mb4 shopcourse
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;
Se local_infile del server impedisce l'operazione, riesaminare le regole con l'amministratore. Un'importazione LOCAL non rifiuta necessariamente ogni input errato per il solo fatto che sia attiva la modalità rigorosa. Controllare SHOW WARNINGS e i testi originali prima di fidarsi delle conversioni.
L'esempio separa le righe con LF e rimuove un CR finale dall'ultimo campo per il caso semplice e comune di CRLF. Righe incorporate nei campi e regole di escape più complesse richiedono un accordo con il programma che produce il file. Eseguire prima il controllo del formato; soltanto se riesce, verificare ID normalizzati, duplicati, intervalli numerici e lunghezza dei nomi.
Su un client Ubuntu creare la directory di lavoro nel terminale e salvarvi products.csv in UTF-8. Il secondo comando stampa il percorso da usare e non crea automaticamente il contenuto CSV.
mkdir -p "$HOME/mariadb-lab"
printf '%s\n' "$HOME/mariadb-lab/products.csv"
Sostituire il percorso LOCAL con quello assoluto effettivamente stampato. L'esempio seguente presume che il nome di accesso del client sia 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;
Eseguire soltanto l'importazione Windows oppure Ubuntu adatta al computer client. Eseguirle entrambe aggiunge due volte le righe nella tabella intermedia. La destinazione OUTFILE su Ubuntu segue invece le regole del server descritte separatamente.
Trasferire soltanto valori validati nelle colonne tipizzate
I controlli su input errati e duplicati devono restituire zero righe. Gli ID devono rientrare nell'intervallo positivo di INT con segno, il prezzo in DECIMAL(10,2) e il nome in 100 caratteri. I testi ID 01 e 1 possono diventare lo stesso valore dopo la conversione: verificare anche l'unicità del risultato normalizzato.
Se la validazione fallisce, correggere la fonte o definire come gestire l'input prima di INSERT. Un numero convertibile non dimostra che l'intera stringa di origine sia corretta.
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;
Devono risultare tre prodotti e il primo nome deve conservare la virgola. Gli altri nomi significano tazza e penna. Confrontare i campi con la fonte, oltre al conteggio. Ripetere LOAD sulla stessa tabella intermedia può aggiungere righe: conservare file e diagnosi prima di ripulire una prova fallita.
Esportare sul server con un account autorizzato
Usare un amministratore o un account di esportazione approvato con privilegio FILE. Verificare secure_file_priv, permessi di scrittura del processo server e un nuovo nome di destinazione. Se un server remoto usa un percorso C:, quel percorso appartiene al server e non al computer client.
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';
Il file contiene tre record CSV senza intestazione; un file esistente non viene sovrascritto. Se serve un'intestazione, definire esplicitamente come aggiungerla. Cambiare in .csv l'estensione di un normale risultato a tabulazioni di mariadb --batch non lo converte in CSV.
Su un server Ubuntu controllare SELECT @@secure_file_priv e i permessi di scrittura dell'account del servizio MariaDB. Se la directory approvata è /var/lib/mysql-files/, si può creare un nuovo file del server come segue. Non si tratta della directory home del client.
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';
Sostituire la directory con una posizione realmente approvata ed eseguire una volta sul server pertinente. Non cancellare file esistenti né ampliare FILE soltanto per superare un errore.
Verificare importazione ed esportazione campo per campo
Oltre al numero di record, confrontare virgole, Unicode, stringhe vuote, NULL e terminatori di riga. I programmi CSV non condividono sempre la stessa convenzione per NULL: stabilire se un campo vuoto rappresenta testo vuoto oppure un valore mancante. L'esempio usa intenzionalmente tre campi compilati.
Accertarsi che il file esportato venga letto con regole coerenti per separatori e virgolette prima di inviarlo a un altro sistema. Valutare la conservazione dei file separatamente da quella delle tabelle. Dopo aver verificato che gli oggetti siano eliminabili, rimuovere soltanto le due tabelle seguenti. DROP TABLE elimina struttura e righe e non viene annullato da un normale ROLLBACK; file sorgente ed esportato non vengono rimossi automaticamente.
DROP TABLE shopcourse.csv45_products;
DROP TABLE shopcourse.csv45_stage;




