LOAD DATA INFILE in MySQL con campi variabili

phpillole ott 02, 2019

La LOAD DATA è uno dei metodi più rapidi ed efficienti per caricare (il discorso è valido anche per l'esportazione) un grande volume di dati all'interno di un database, evitando di avere direttamente a che fare con operazioni su file, cicli, controlli e altre amenità varie.

Ricordarsi la sintassi corretta ogni volta è un po' noioso (anche se c'è da dire che è abbastanza intuitiva) e nel corso del tempo mi sono ritrovato, mio malgrado, a dover cercare sul manuale di MySQL o a rovistare fra progetti vari in cerca di un esempio di utilizzo che sia "prêt a coller" , diciamo.

Recentemente mi è capitato di dover lavorare su un progetto che prevedeva una LOAD DATA a partire da file csv dal tracciato variabile (1), i cui dati dovevano però conferire all'interno della stessa tabella del database.
Ho risolto la questione con questo semplice metodo, che tiene in considerazione gli utilizzi più comuni (quantomeno, quelli più comuni relativamente alla mie esperienza diretta ed indiretta fatta finora!):

class LoadDataHelper {

	private $db; // DB class	
	private $table = 'my_table';
    private $default = ['separator' => ';', 'enclosure' => '"', 'eol' => '\n', 'skip' => 0, 'update' => ''];
    const BASE_SYNTAX = "LOAD DATA INFILE
				  '%s' %s INTO TABLE %s
				  CHARACTER SET UTF8
				  FIELDS TERMINATED BY '%s'
				  OPTIONALLY ENCLOSED BY '%s'
				  LINES TERMINATED BY '%s' ";
	
	public function __construct($db)
	{
		$this->db = $db;
	}
	
    /**
     * @param $file
     * @param $fields
     * @param array $custom
     * @throws \Doctrine\DBAL\DBALException
     */
    public function load($file, $fields, $custom = [])
    {
        try {
            $config = array_merge($this->default, $custom);
            $header = $this->getCsvHeader($file, $config['separator'], $config['enclosure']);
            $sql = sprintf(self::BASE_SYNTAX, $file, $config['update'], $this->table, $config['separator'], $config['enclosure'], $config['eol']);
            if ($config['skip'] > 0){
                $sql .= sprintf("IGNORE %d LINES", $config['skip']);
            }
            if (!empty($header)) {
                $useFields = array_map(function($element) use($fields) {
                    return empty($fields[$element]) ? '@dummy' : $fields[$element];
                }, $header);
                $sql .= sprintf(" (%s) ", implode(',', $useFields));
            }

            // Eventuali campi extra
            //$sql .= "SET id = NULL, created_at = CURDATE()";
            $this->db->execute($sql);
            return true;
        } catch (\Exception $e) {
            // Log $e->getMessage();
            return false;
        }
    }
    
    /**
     * @param $file
     * @param string $delimiter
     * @param string $enclosure
     * @return array
     */
    protected function getCsvHeader($file, $delimiter = ',', $enclosure = '"')
    {
        ini_set('auto_detect_line_endings',TRUE);
        $handle = fopen($file,'r');
        $heading = [];
        while (false !== ($data = fgetcsv($handle, 1000, $delimiter, $enclosure)))
        {
            $heading = $data;
            break;
        }
        fclose($handle);
        return $heading;
    }
}

Il processo è questo: configuro una mappatura tra i tutti i campi possibili dei file e le relative colonne della mia tabella (in questo caso basta un semplice JSON), e poi passo tutto al mio metodo che non fa altro che leggere l'intestazione del file, verificare il matching con la configurazione fatta e costruire la lista corretta di campi per completare la sintassi della LOAD DATA.

Nella versione originale uso l'ORM di Symfony 3 ma basta una semplice istanza wrappata di PDO (o qualsiasi altra classe per la gestione del database) per eseguire la query finale. Purtroppo, quando si tratta di campi diversi dalle colonne, non è possibile usare i prepared statements, ma vista la query non mi vengono in mente SQL Injection che possano farla funzionare senza generare errori di sintassi MySql (in caso ditemi!).

Un esempio di utilizzo:

$mapping = '{ 
              "fields" : 
                 "Campo1file" : "campo_1_db",
                 "Campo2file" : "campo_2_db",
                 "Campo3file" : "campo_3_db",
                 "AltroCampo" : "campo_1_db",
                 "AltroCampo2": "campo_4_db",
                 "AltoCampo3" : "campo_4_db"
              }';
$ld = new LoadDataHelper($dbClass);
// personalizzo il separatore
$params = ['separator' => ';'];
$status = $ld->load('my_file.csv', json_decode($mapping['fields']), $params);

Ora se arrivano più file con tracciati parzialmente differenti (ad esempio, utenti provenienti da sorgenti diverse) possono confluire tutti in un'unica tabella con minimo sforzo e minima configurazione.

(1)Una base concordata e fissa è condizione imprescindibile, naturalmente. Fare una mappatura completamente dinamica sarebbe un lavoro decisamente più vasto e delicato.

Matteo Vignoli

Sviluppatore Web Full-Stack, autodidatta, curioso per natura, attualmente impiegato a Milano in ContactLab