Comment importer des fichiers de données JSON, Texte, XML, CSV dans la base de données MySQL

L'importation de données externes dans la base de données est une exigence très courante dans les applications de base de données. En fait, il s'agit de la partie L (Charger) d'ETL (Extraire, transformer, charger) dans la gestion et l'exploitation des données, c'est-à-dire l'importation de données d'une structure ou d'un format spécifique dans une certaine destination (telle que la base de données, nous discutons ici de MySQL ).

Cet article explique comment importer facilement des données dans plusieurs formats (JSON, Texte, XML, CSV) dans MySQL.

Aperçu de cet article :

1. Importer des fichiers texte (y compris des fichiers CSV) dans MySQL

2. 
   

3. Importer un fichier XML dans MySQL

4. 
   

5. Importer des fichiers JSON dans MySQL

6. 
   

7. Utilisez l'assistant d'exportation et d'importation de données de table de MySQL Workbench pour importer et exporter des fichiers JSON ou CSV

1. Importez des fichiers texte (y compris des fichiers CSV) dans MySQL

Notre discussion ici est basée sur l'hypothèse que le fichier texte et le fichier CSV ont un format relativement standardisé (correctement formaté). Par exemple, chaque champ de données (champ) dans chaque ligne est séparé par un délimiteur commun (tel que tab : t). .

Donc d'abord, vous devez concevoir la table correspondante (Schéma) de la base de données en fonction du format de vos données (quels sont les champs).

Par exemple, le fichier texte ou CSV à traiter utilise t comme délimiteur, et chaque ligne comporte trois champs de données : id, name et balance. Ensuite, nous devons d'abord créer cette table dans la base de données :

CREATE TABLE sometable(id INT, name VARCHAR(255), balance DECIMAL(8,4));

Une fois la création réussie, vous pouvez l'importer. La méthode de fonctionnement est très simple :

LOAD DATA LOCAL INFILE '你的文件路径（如~/file.csv）' INTO TABLE sometable FIELDS TERMINATED BY '\t' [ENCLOSED BY '"'(可选)] LINES TERMINATED BY '\n' (id, name, balance)

Ce qu'il convient de noter ici, c'est que nous devons activer le paramètre de configuration MySQL local-infile pour réussir l'importation. La raison est que vous pouvez voir ce passage du manuel MySQL :

LOCAL ne fonctionne que si votre serveur et votre client ont tous deux été configurés pour le permettre. Par exemple, si mysqld a été démarré avec --local-infile=0, LOCAL ne fonctionne pas. Voir Section 6.1.6, « Problèmes de sécurité avec LOAD DATA. LOCAL”.

Il s'agit de la configuration par défaut de MySQL pour des raisons de sécurité. Par conséquent, nous devons nous assurer que dans le fichier de configuration my.cnf (en prenant comme exemple la distribution Debian de Linux, comme Ubuntu, il se trouve dans /etc/my.cnf) :

local-infile=1

Ou ajoutez l'élément --local-infile lors du démarrage de MySQL à partir de la ligne de commande :

mysql --local-infile -uroot -pyourpwd yourdbname

De plus, nous pouvons également utiliser mysqlimport, un programme d'importation officiel de MySQL. Ce programme fournit essentiellement une interface de ligne de commande pour LOAD DATA FILE. Il est facile à comprendre et nous n'entrerons pas dans les détails ici.

2. Importez le fichier XML dans MySQL

La façon dont cela est fait a beaucoup à voir avec la forme de notre XML.

Par exemple, lorsque votre fichier de données XML a un format très standardisé, tel que :

<?xml version="1.0"?>
  <row>
    <field name="id">1</field>
    <field name="name">Free</field>
    <field name="balance">2333.3333</field>
   </row>

  <row>
    <field name="id">2</field>
    <field name="name">Niki</field>
    <field name="balance">1289.2333</field>
  </row>

Ou

<row column1="value1" column2="value2" .../>

Nous pouvons facilement utiliser LOAD XML pour importer. Ici vous pouvez voir le manuel officiel de MySQL - LOAD XML Syntax.

Cependant, nous pouvons avoir d'autres besoins. Par exemple, nous pouvons vouloir mapper les champs du fichier XML vers des colonnes avec des noms différents (TABLE COLUMN). Il convient de noter ici qu'après MySQL v5.0.7, LOAD XML INFILE ou LOAD DATA INFILE ne peut plus être exécuté dans la procédure stockée MySQL. Par conséquent, la procédure de conversion est écrite différemment d’avant. Ici, nous devons utiliser les deux fonctions Load_File() et ExtractValue().

​Ce qui suit est un exemple de fichier et de programme XML : Fichier :

<?xml version="1.0"?>
<some_list>
  <someone id="1" fname="Rob" lname="Gravelle"/>
  <someone id="2" fname="Al" lname="Bundy"/>
  <someone id="3" fname="Little" lname="Richard"/>
</some_list>

Programme :

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `import_some_xml`(path varchar(255), node varchar(255))
BEGIN
    declare xml_content text;
    declare v_row_index int unsigned default 0;   
    declare v_row_count int unsigned;  
    declare v_xpath_row varchar(255); 
 
    set xml_content = load_file(path);
 
    -- calculate the number of row elements.   
    set v_row_count  = extractValue(xml_content, concat('count(', node, ')')); 
    
    -- loop through all the row elements    
    while v_row_index < v_row_count do                
        set v_row_index = v_row_index + 1;        
        set v_xpath_row = concat(node, &#39;[&#39;, v_row_index, &#39;]/@*&#39;);
        insert into applicants values (
            extractValue(xml_content, concat(v_xpath_row, &#39;[1]&#39;)),
            extractValue(xml_content, concat(v_xpath_row, &#39;[2]&#39;)),
            extractValue(xml_content, concat(v_xpath_row, &#39;[3]&#39;))
        );
    end while;
END

​Dans MySQL, utilisez ceci pour importer :

call import_some_xml('你的XML文件路径', '/some_list/someone');

Le programme est assez simple, tant que vous comprenez les scripts MySQL.

Mentionnez DELIMITER $$ ici. Nous savons que le délimiteur de commande de MySQL est par défaut un point-virgule. Cependant, il y a évidemment un point-virgule dans le script, mais nous ne voulons pas l'exécuter immédiatement, nous devons donc temporairement modifier le délimiteur.

3. Importez le fichier JSON dans MySQL

Comment importer des fichiers JSON dans MySQL est un sujet très intéressant. JSON est une structure de fichiers assez couramment utilisée de nos jours, la maîtrise de son importation revêt donc une importance plus large.

Plusieurs fois, les données JSON que nous traitons apparaissent sous la forme suivante :

{"name":"Julia","gender":"female"}
{"name":"Alice","gender":"female"}
{"name":"Bob","gender":"male"}
{"name":"Julian","gender":"male"}

Plutôt que des [{},{},{},{}] réguliers (Export de certaines bases de données NoSQL).

Cette situation présente un avantage pour le chargement : comme chaque ligne est un objet JSON, nous pouvons traiter ce fichier ligne par ligne, sans avoir besoin de séparer l'intégralité du fichier (comme un fichier .json avec plusieurs G) en raison de la structure stricte de JSON.

Méthode 1 : utiliser un schéma commun

Common-schema est un framework MySQL largement utilisé. Il possède des fonctions riches et une documentation détaillée. Nous pouvons utiliser ses capacités d'analyse JSON. (Il dispose également de fonctions pratiques telles que la conversion JSON en XML)

　　具体说来，将common-schema导入之后，使用它的extract_json_value函数即可。源码中：

create function extract_json_value(
    json_text text charset utf8,
    xpath text charset utf8
) returns text charset utf8

　　该函数接受两个参数，一个是json_text，表示json文件的内容，另一个是xpath，表示数据的结构（这里可以类比XML文件的处理）。很多读者应该知道，XPath是用来对XML中的元素进行定位的，这里也可以作一样的理解。

　　以本段开始的几行JSON为例，这里common-schema的使用如下例：

select common_schema.extract_json_value(f.event_data,'/name') as name, common_schema.extract_json_value(f.event_data,'/gender') as gender, sum(f.event_count) as event_count from json_event_fact f group by name, gender;

　　关于event_data，我们需要先理解LOAD DATA INFILE是一个event，不同的event type对应不同的event data。这部分知识可以参看Event Data for Specific Event Types

　　如果感兴趣，可以参看其源码。参看一个受到广泛使用的项目的源码，对于自身成长是很有益的。

　　当然了，我们也可以像之前处理XML文件导入一样，自己编写程序。这里便不再给出实例程序，有兴趣的读者可以自行编写或者跟笔者交流。

　　方式二 使用mysqljsonimport

　　这是Anders Karlsson的一个完成度很高的作品。这一份程序由C写成。它依赖于一个JSON Parser，Jansson。他们都有着比较好的维护和文档，所以使用上体验很好。

　　mysqljsonimport的下载在SourceForge上。具体使用参照其文档即可。

　　为了方便不熟悉源码安装的朋友，笔者在这里提一下安装流程和注意事项。安装命令顺序如下：

$ wget http://sourceforge.net/projects/mysqljson/files/myjsonimport_1.6/mysqljsonimport-1.6.tar.gz 
$ tar xvfz mysqljsonimport-1.6.tar.gz 
$ cd mysqljsonimport-1.6 
$ ./configure –-with-mysql=/xxx/mysql 
$ make
$ make check 
$ sudo make install

　　--with-mysql这一步不是必要的，只要你安装的mysql的路径是系统的默认路径。很关键的，而且很容易被不熟悉的朋友忽略的是，这一个C程序要成功编译和运行，是需要MySQL的C API的，所以需要安装的依赖，除了jansson，还有libmysqlclient-dev。

　　jansson的安装就是简单的源码安装，libmysqlclient-dev则可以使用包管理工具(比如ubuntu中使用apt-get即可；编译和安装前，建议先sudo apt-get update以避免不必要的麻烦)。

　　导入命令：

$ ./mysqljsonimport –-database test –-table tablename jsonfilename

　　还有一个parser，作者是Kazuho，感兴趣的读者可以参看一下，他的相关博文是mysql_json - a MySQL UDF for parsing JSON ，github项目是mysql_json。

　4. 使用MySQL workbench

　　Workbench这个工具对于许多不熟悉SQL语言或者命令行的朋友还是很方便和友好的。利用它，可以方便地导入和导出CSV和JSON文件。

　　具体操作图例参见MySQL官方手册即可:Table Data Export and Import Wizard，这里不再赘述。

　总结

　　本文介绍了将不同格式（JSON, Text, XML, CSV）的文件导入MySQL数据库的一些详细手段，并进行了一些分析，目的在于帮助读者扫除一些导入的障碍，理清一些概念。之所以没有讨论导出，是因为导出是一个MySQL到外的操作，是以MySQL本身为转移的，只要参考MySQL本身的机理即可。

　　真正对于大量数据的导入导出，需要思考的问题会很多(比如说在导入时，如何考虑Sharding)，这需要另开一篇讨论了。

　　谢谢阅读，欢迎指正。

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!