Français
Français
Dans PHP, si vous avez besoin d'importer des données d'un fichier local vers une base de données MySQL, vous utilisez généralement l'instruction Infile locale de données . Cependant, cette fonctionnalité peut être désactivée ou limitée dans certains environnements pour des raisons de sécurité. PHP fournit une alternative - personnalisez le comportement de la lecture des fichiers locaux via la méthode Mysqli :: set_local_infile_handler () . Cela améliore non seulement la sécurité, mais donne également aux développeurs une plus grande flexibilité.
Cet article expliquera en détail comment utiliser la fonction SET_LOCAL_INFILE_HANDLER () pour implémenter l'importation de fichiers locaux de MySQL et l'expliquer en collaboration avec un exemple complet.
mysqli :: set_local_infile_handler () est une méthode fournie par l'extension mysqli en php. Il est utilisé pour lire le contenu du fichier local via une fonction de rappel définie par l'utilisateur lors de l'exécution de données de chargement local Infile .
Syntaxe de base:
<span><span>mysqli::</span><span><span class="hljs-variable constant_">set_local_infile_handler</span></span><span> ( </span><span><span class="hljs-keyword">callable</span></span><span> </span><span><span class="hljs-variable">$read_func</span></span><span> ) : </span><span><span class="hljs-keyword">bool</span></span><span>
</span></span>où $ read_func est une fonction définie par l'utilisateur qui gère la lecture et la transmission des données.
Avant de l'utiliser, vous devez vous assurer que les éléments suivants sont configurés correctement:
MySQL Server-Side a local_infile activé:
<span><span><span class="hljs-keyword">SHOW</span></span><span> VARIABLES </span><span><span class="hljs-keyword">LIKE</span></span><span> </span><span><span class="hljs-string">'local_infile'</span></span><span>;
</span></span>Si vous êtes désactivé , vous devez le définir dans le fichier de configuration du serveur my.cnf :
<span><span><span class="hljs-section">[mysqld]</span></span><span>
</span><span><span class="hljs-attr">local_infile</span></span><span>=</span><span><span class="hljs-number">1</span></span><span>
</span></span> Mysqli.allow_local_infile est activé dans la configuration PHP:
Vérifiez si php.ini contient ce qui suit:
<span><span><span class="hljs-attr">mysqli.allow_local_infile</span></span><span> = </span><span><span class="hljs-literal">On</span></span><span>
</span></span>Lors de la connexion à la base de données, ajoutez l'option MySqli_Client_Local_Files :
<span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-title function_ invoke__">mysqli_init</span></span><span>();
</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">options</span></span><span>(MYSQLI_OPT_LOCAL_INFILE, </span><span><span class="hljs-literal">true</span></span><span>);
</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">real_connect</span></span><span>(</span><span><span class="hljs-string">"host"</span></span><span>, </span><span><span class="hljs-string">"user"</span></span><span>, </span><span><span class="hljs-string">"password"</span></span><span>, </span><span><span class="hljs-string">"database"</span></span><span>, </span><span><span class="hljs-literal">null</span></span><span>, </span><span><span class="hljs-literal">null</span></span><span>, MYSQLI_CLIENT_LOCAL_FILES);
</span></span>La fonction de cette fonction est de fournir le contenu de fichier local à MySQL de manière tamponnée. Voici un exemple de lecture d'un fichier CSV:
<span><span><span class="hljs-function"><span class="hljs-keyword">function</span></span></span><span> </span><span><span class="hljs-title">localInfileReader</span></span><span>(</span><span><span class="hljs-params"><span class="hljs-variable">$stream</span></span></span><span>, &</span><span><span class="hljs-variable">$buffer</span></span><span>, </span><span><span class="hljs-variable">$buflen</span></span><span>)
{
</span><span><span class="hljs-built_in">static</span></span><span> </span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-literal">null</span></span><span>;
</span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$fp</span></span><span>) {
</span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-title function_ invoke__">fopen</span></span><span>(</span><span><span class="hljs-string">'/path/to/data.csv'</span></span><span>, </span><span><span class="hljs-string">'r'</span></span><span>);
</span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$fp</span></span><span>) {
</span><span><span class="hljs-keyword">return</span></span><span> -</span><span><span class="hljs-number">1</span></span><span>;
}
}
</span><span><span class="hljs-variable">$data</span></span><span> = </span><span><span class="hljs-title function_ invoke__">fread</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>, </span><span><span class="hljs-variable">$buflen</span></span><span>);
</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$data</span></span><span> === </span><span><span class="hljs-literal">false</span></span><span>) {
</span><span><span class="hljs-keyword">return</span></span><span> -</span><span><span class="hljs-number">1</span></span><span>;
}
</span><span><span class="hljs-variable">$buffer</span></span><span> = </span><span><span class="hljs-variable">$data</span></span><span>;
</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-title function_ invoke__">feof</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>)) {
</span><span><span class="hljs-title function_ invoke__">fclose</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>);
</span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-literal">null</span></span><span>;
}
</span><span><span class="hljs-keyword">return</span></span><span> </span><span><span class="hljs-title function_ invoke__">strlen</span></span><span>(</span><span><span class="hljs-variable">$data</span></span><span>);
}
</span></span>Voici un exemple complet montrant comment utiliser set_local_infile_handler () pour implémenter l'importation de données de fichiers locales:
<span><span><span class="hljs-meta"><?php</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span> = </span><span><span class="hljs-title function_ invoke__">mysqli_init</span></span><span>();
</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">options</span></span><span>(MYSQLI_OPT_LOCAL_INFILE, </span><span><span class="hljs-literal">true</span></span><span>);
</span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">real_connect</span></span><span>(</span><span><span class="hljs-string">'localhost'</span></span><span>, </span><span><span class="hljs-string">'root'</span></span><span>, </span><span><span class="hljs-string">'password'</span></span><span>, </span><span><span class="hljs-string">'test_db'</span></span><span>, </span><span><span class="hljs-literal">null</span></span><span>, </span><span><span class="hljs-literal">null</span></span><span>, MYSQLI_CLIENT_LOCAL_FILES)) {
</span><span><span class="hljs-keyword">die</span></span><span>(</span><span><span class="hljs-string">'Connect Error ('</span></span><span> . </span><span><span class="hljs-title function_ invoke__">mysqli_connect_errno</span></span><span>() . </span><span><span class="hljs-string">') '</span></span><span> . </span><span><span class="hljs-title function_ invoke__">mysqli_connect_error</span></span><span>());
}
</span><span><span class="hljs-function"><span class="hljs-keyword">function</span></span></span><span> </span><span><span class="hljs-title">localInfileReader</span></span><span>(</span><span><span class="hljs-params"><span class="hljs-variable">$stream</span></span></span><span>, &</span><span><span class="hljs-variable">$buffer</span></span><span>, </span><span><span class="hljs-variable">$buflen</span></span><span>)
{
</span><span><span class="hljs-built_in">static</span></span><span> </span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-literal">null</span></span><span>;
</span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$fp</span></span><span>) {
</span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-title function_ invoke__">fopen</span></span><span>(</span><span><span class="hljs-string">'/path/to/data.csv'</span></span><span>, </span><span><span class="hljs-string">'r'</span></span><span>);
</span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$fp</span></span><span>) {
</span><span><span class="hljs-keyword">return</span></span><span> -</span><span><span class="hljs-number">1</span></span><span>;
}
}
</span><span><span class="hljs-variable">$data</span></span><span> = </span><span><span class="hljs-title function_ invoke__">fread</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>, </span><span><span class="hljs-variable">$buflen</span></span><span>);
</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-variable">$data</span></span><span> === </span><span><span class="hljs-literal">false</span></span><span>) {
</span><span><span class="hljs-keyword">return</span></span><span> -</span><span><span class="hljs-number">1</span></span><span>;
}
</span><span><span class="hljs-variable">$buffer</span></span><span> = </span><span><span class="hljs-variable">$data</span></span><span>;
</span><span><span class="hljs-keyword">if</span></span><span> (</span><span><span class="hljs-title function_ invoke__">feof</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>)) {
</span><span><span class="hljs-title function_ invoke__">fclose</span></span><span>(</span><span><span class="hljs-variable">$fp</span></span><span>);
</span><span><span class="hljs-variable">$fp</span></span><span> = </span><span><span class="hljs-literal">null</span></span><span>;
}
</span><span><span class="hljs-keyword">return</span></span><span> </span><span><span class="hljs-title function_ invoke__">strlen</span></span><span>(</span><span><span class="hljs-variable">$data</span></span><span>);
}
</span><span><span class="hljs-comment">// Configurer le processeur de lecture</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">set_local_infile_handler</span></span><span>(</span><span><span class="hljs-string">"localInfileReader"</span></span><span>);
</span><span><span class="hljs-comment">// Exécuter l'instruction IMPORT</span></span><span>
</span><span><span class="hljs-variable">$sql</span></span><span> = </span><span><span class="hljs-string">"LOAD DATA LOCAL INFILE 'dummy.csv' INTO TABLE my_table FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'"</span></span><span>;
</span><span><span class="hljs-keyword">if</span></span><span> (!</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">query</span></span><span>(</span><span><span class="hljs-variable">$sql</span></span><span>)) {
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Échec de l'importation: ("</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>->errno . </span><span><span class="hljs-string">") "</span></span><span> . </span><span><span class="hljs-variable">$mysqli</span></span><span>->error;
} </span><span><span class="hljs-keyword">else</span></span><span> {
</span><span><span class="hljs-keyword">echo</span></span><span> </span><span><span class="hljs-string">"Importé avec succès！"</span></span><span>;
}
</span><span><span class="hljs-comment">// Éteindre le processeur</span></span><span>
</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">set_local_infile_default</span></span><span>();
</span><span><span class="hljs-variable">$mysqli</span></span><span>-></span><span><span class="hljs-title function_ invoke__">close</span></span><span>();
</span><span><span class="hljs-meta">?></span></span><span>
</span></span>illustrer:
Dummy.csv est un nom de fichier d'espace réservé que MySQL ne lit pas réellement le chemin, mais s'appuie plutôt sur la fonction de rappel que vous fournissez.
Le chemin de fichier réel dans la fonction de rappel peut être n'importe quel chemin légal lisible.
La fonction de rappel doit être en mesure de lire et de renvoyer correctement la longueur des données.
Assurez-vous de fermer le rappel ( set_local_infile_default () ) pour éviter le déclenchement d'erreur dans les opérations suivantes.
Veuillez vous assurer que l'utilisateur de la base de données a des autorisations suffisantes avant d'utiliser des données de chargement locales .
Grâce à la méthode set_local_infile_handler () , PHP fournit une méthode de support plus sécurisée et contrôlable pour les données de chargement Infile local . Cette méthode convient particulièrement aux scénarios où il existe une exigence de vérification stricte pour l'entrée de données. Bien qu'il soit un peu compliqué à utiliser, il offre aux développeurs une flexibilité et une sécurité précieuses dans les environnements de production réels.
La maîtrise de cette technique ajoutera des outils puissants à votre manipulation de tâches d'importation de données à grande échelle.
Étiquettes associées:
MySQL
