functions - php-mysql package




Best practice: importare il file mySQL in PHP; domande divise (9)

Esportare

Il primo passo è ottenere l'input in un formato sano per l'analisi quando lo si esporta. Dalla tua domanda sembra che tu abbia il controllo sull'esportazione di questi dati, ma non sull'importazione.

~: mysqldump test --opt --skip-extended-insert | grep -v '^--' | grep . > test.sql

Questo scarica il database di test escludendo tutte le righe di commento e le righe vuote in test.sql. Disabilita anche gli inserimenti estesi, ovvero c'è un'istruzione INSERT per riga. Ciò consentirà di limitare l'utilizzo della memoria durante l'importazione, ma a un costo di velocità di importazione.

Importare

Lo script di importazione è semplice come questo:

<?php

$mysqli = new mysqli('localhost', 'hobodave', 'p4ssw3rd', 'test');
$handle = fopen('test.sql', 'rb');
if ($handle) {
    while (!feof($handle)) {
        // This assumes you don't have a row that is > 1MB (1000000)
        // which is unlikely given the size of your DB
        // Note that it has a DIRECT effect on your scripts memory
        // usage.
        $buffer = stream_get_line($handle, 1000000, ";\n");
        $mysqli->query($buffer);
    }
}
echo "Peak MB: ",memory_get_peak_usage(true)/1024/1024;

Ciò utilizzerà una quantità di memoria assurdamente bassa come mostrato di seguito:

daves-macbookpro:~ hobodave$ du -hs test.sql 
 15M    test.sql
daves-macbookpro:~ hobodave$ time php import.php 
Peak MB: 1.75
real    2m55.619s
user    0m4.998s
sys 0m4.588s

Quello che dice è che hai elaborato un mysqldump da 15 MB con un utilizzo massimo della RAM di 1,75 MB in poco meno di 3 minuti.

Esportazione alternativa

Se hai abbastanza memoria memory_limit e questo è troppo lento, puoi provarlo usando la seguente esportazione:

~: mysqldump test --opt | grep -v '^--' | grep . > test.sql

Ciò consentirà inserimenti estesi, che inseriscono più righe in una singola query. Ecco le statistiche per lo stesso database:

daves-macbookpro:~ hobodave$ du -hs test.sql 
 11M    test.sql
daves-macbookpro:~ hobodave$ time php import.php 
Peak MB: 3.75
real    0m23.878s
user    0m0.110s
sys 0m0.101s

Si noti che utilizza più di 2x la RAM a 3,75 MB, ma impiega circa 1/6 di tempo. Suggerisco di provare entrambi i metodi e vedere quale si adatta alle tue esigenze.

Modificare:

Non ero in grado di ottenere una nuova riga per apparire letteralmente in qualsiasi output di mysqldump utilizzando uno qualsiasi dei tipi di campo CHAR, VARCHAR, BINARY, VARBINARY e BLOB. Se si dispone di campi BLOB / BINARY, utilizzare quindi quanto segue nel caso in cui:

~: mysqldump5 test --hex-blob --opt | grep -v '^--' | grep . > test.sql

Ho una situazione in cui devo aggiornare un sito web su un provider di hosting condiviso. Il sito ha un CMS. Il caricamento dei file CMS è piuttosto semplice tramite FTP.

Devo anche importare un file di database grande (relativo ai confini di uno script PHP) (circa 2-3 MB non compressi). Mysql è chiuso per l'accesso dall'esterno, quindi devo caricare un file tramite FTP e avviare uno script PHP per importarlo. Purtroppo, non ho accesso alla funzione della riga di comando mysql quindi devo analizzare e interrogare usando PHP nativo. Inoltre non posso usare LOAD DATA INFILE. Inoltre, non posso utilizzare alcun tipo di front-end interattivo come phpMyAdmin, deve essere eseguito in modo automatico. Inoltre non posso usare mysqli_multi_query() .

Qualcuno sa o ha una soluzione semplice, già codificata, che divide in modo affidabile un tale file in singole query (potrebbero esserci istruzioni multilinea) ed esegue la query. Vorrei evitare di iniziare a giocherellare io stesso a causa dei numerosi trucchi che probabilmente incontrerò (come rilevare se un delimitatore di campo è parte dei dati, come gestire le interruzioni di riga nei campi memo e così via sopra). Ci deve essere una soluzione pronta per questo.


È possibile utilizzare phpMyAdmin per importare il file. Anche se è enorme, basta usare la UploadDir configurazione di UploadDir , caricarlo lì e sceglierlo dalla pagina di importazione di phpMyAdmin. Una volta che l'elaborazione del file sarà vicina ai limiti PHP, phpMyAdmin interromperà l'importazione, ti mostrerà di nuovo la pagina di importazione con valori predefiniti che indicano dove proseguire nell'importazione.


Ecco una funzione di memoria che dovrebbe essere in grado di dividere un grosso file in singole query senza dover aprire l'intero file contemporaneamente :

function SplitSQL($file, $delimiter = ';')
{
    set_time_limit(0);

    if (is_file($file) === true)
    {
        $file = fopen($file, 'r');

        if (is_resource($file) === true)
        {
            $query = array();

            while (feof($file) === false)
            {
                $query[] = fgets($file);

                if (preg_match('~' . preg_quote($delimiter, '~') . '\s*$~iS', end($query)) === 1)
                {
                    $query = trim(implode('', $query));

                    if (mysql_query($query) === false)
                    {
                        echo '<h3>ERROR: ' . $query . '</h3>' . "\n";
                    }

                    else
                    {
                        echo '<h3>SUCCESS: ' . $query . '</h3>' . "\n";
                    }

                    while (ob_get_level() > 0)
                    {
                        ob_end_flush();
                    }

                    flush();
                }

                if (is_string($query) === true)
                {
                    $query = array();
                }
            }

            return fclose($file);
        }
    }

    return false;
}

L'ho provato su un grande dump SQL phpMyAdmin e ha funzionato bene.

Alcuni dati di test:

CREATE TABLE IF NOT EXISTS "test" (
    "id" INTEGER PRIMARY KEY AUTOINCREMENT,
    "name" TEXT,
    "description" TEXT
);

BEGIN;
    INSERT INTO "test" ("name", "description")
    VALUES (";;;", "something for you mind; body; soul");
COMMIT;

UPDATE "test"
    SET "name" = "; "
    WHERE "id" = 1;

E il rispettivo output:

SUCCESS: CREATE TABLE IF NOT EXISTS "test" ( "id" INTEGER PRIMARY KEY AUTOINCREMENT, "name" TEXT, "description" TEXT );
SUCCESS: BEGIN;
SUCCESS: INSERT INTO "test" ("name", "description") VALUES (";;;", "something for you mind; body; soul");
SUCCESS: COMMIT;
SUCCESS: UPDATE "test" SET "name" = "; " WHERE "id" = 1;

Ho incontrato lo stesso problema. L'ho risolto usando un'espressione regolare:

function splitQueryText($query) {
    // the regex needs a trailing semicolon
    $query = trim($query);

    if (substr($query, -1) != ";")
        $query .= ";";

    // i spent 3 days figuring out this line
    preg_match_all("/(?>[^;']|(''|(?>'([^']|\\')*[^\\\]')))+;/ixU", $query, $matches, PREG_SET_ORDER);

    $querySplit = "";

    foreach ($matches as $match) {
        // get rid of the trailing semicolon
        $querySplit[] = substr($match[0], 0, -1);
    }

    return $querySplit;
}

$queryList = splitQueryText($inputText);

foreach ($queryList as $query) {
    $result = mysql_query($query);
}

La suddivisione di una query non può essere eseguita in modo affidabile senza analisi. Qui è valido SQL che sarebbe impossibile dividere correttamente con un'espressione regolare.

SELECT ";"; SELECT ";\"; a;";
SELECT ";
    abc";

Ho scritto una piccola classe SqlFormatter in PHP che include un tokenizzatore di query. Ho aggiunto un metodo splitQuery che divide tutte le query (incluso l'esempio precedente) in modo affidabile.

https://github.com/jdorn/sql-formatter/blob/master/SqlFormatter.php

Puoi rimuovere il formato e i metodi di evidenziazione se non ne hai bisogno.

Uno svantaggio è che richiede l'intera stringa sql di essere in memoria, che potrebbe essere un problema se si sta lavorando con enormi file sql. Sono sicuro che con un po 'di complicazioni, potresti far funzionare il metodo getNextToken su un puntatore di file.


Non puoi installare phpMyAdmin, gzip il file (che dovrebbe renderlo molto più piccolo) e importarlo usando phpMyAdmin?

EDIT: Bene, se non è possibile utilizzare phpMyAdmin, è possibile utilizzare il codice da phpMyAdmin. Non sono sicuro di questa parte, ma è generalmente ben strutturato.


Puoi usare LOAD DATA INFILE?

Se si formatta il file dump dump usando SELECT INTO OUTFILE, questo dovrebbe essere esattamente ciò di cui si ha bisogno. Nessun motivo per far analizzare PHP.


Quando ha pubblicato il loro dump di dati mensile in formato XML, ho scritto script PHP per caricarlo in un database MySQL. Ho importato circa 2,2 gigabyte di XML in pochi minuti.

La mia tecnica è di prepare() un'istruzione INSERT con i segnaposto dei parametri per i valori delle colonne. Quindi utilizzare XMLReader per XMLReader loop sugli elementi XML ed execute() la query preparata, inserendo i valori per i parametri. Ho scelto XMLReader perché è un lettore XML in streaming; legge l'input XML in modo incrementale invece di richiedere di caricare l'intero file in memoria.

Si può anche leggere un file CSV una riga alla volta con fgetcsv() .

Se si sta effettuando l'importazione in tabelle InnoDB, si consiglia di avviare e commettere transazioni esplicitamente, per ridurre il sovraccarico di autocommit. Commetto ogni 1000 righe, ma questo è arbitrario.

Non invierò qui il codice (a causa del criterio di licenza di ), ma in pseudocodice:

connect to database
open data file
PREPARE parameterizes INSERT statement
begin first transaction
loop, reading lines from data file: {
    parse line into individual fields
    EXECUTE prepared query, passing data fields as parameters
    if ++counter % 1000 == 0,
        commit transaction and begin new transaction
}
commit final transaction

Scrivere questo codice in PHP non è una scienza missilistica, e funziona piuttosto rapidamente quando si usano istruzioni preparate e transazioni esplicite. Queste funzionalità non sono disponibili nell'estensione PHP mysql obsoleta, ma puoi usarle se usi mysqli o PDO_MySQL .

Ho anche aggiunto elementi utili come la verifica degli errori, la segnalazione dei progressi e il supporto per i valori predefiniti quando il file di dati non include uno dei campi.

Ho scritto il mio codice in una classe PHP abstract che ho sottoclasse per ogni tabella che ho bisogno di caricare. Ciascuna sottoclasse dichiara le colonne che desidera caricare e le associa ai campi nel file di dati XML per nome (o per posizione se il file di dati è CSV).


http://www.ozerov.de/bigdump/ stato molto utile per me nell'importazione di oltre 200 MB di file sql.

Nota: il file SQL deve essere già presente nel server in modo che il processo possa essere completato senza problemi





mysql