So importieren Sie JSON-, Text-, XML- und CSV-Datendateien in die MySQL-Datenbank

Das Importieren externer Daten in die Datenbank ist eine sehr häufige Anforderung in Datenbankanwendungen. Tatsächlich ist dies der L-Teil (Laden) von ETL (Extrahieren, Transformieren, Laden) in der Datenverwaltung und -operation, dh das Importieren von Daten einer bestimmten Struktur oder eines bestimmten Formats in ein bestimmtes Ziel (z. B. eine Datenbank, hier besprechen wir MySQL). ).

In diesem Artikel geht es darum, wie Sie Daten in mehreren Formaten (JSON, Text, XML, CSV) einfach in MySQL importieren können.

Gliederung dieses Artikels:

1. Importieren Sie Textdateien (einschließlich CSV-Dateien) in MySQL

2. 
   

3. XML-Datei in MySQL importieren

4. 
   

5. JSON-Dateien in MySQL importieren

6. 
   

7. Verwenden Sie den Assistenten zum Exportieren und Importieren von Tabellendaten der MySQL-Workbench, um JSON- oder CSV-Dateien zu importieren und zu exportieren

1. Importieren Sie Textdateien (einschließlich CSV-Dateien) in MySQL

Unsere Diskussion hier basiert auf der Annahme, dass Textdateien und CSV-Dateien ein relativ standardisiertes Format haben (richtig formatiert). Beispielsweise ist jedes Datenfeld (Feld) in jeder Zeile durch ein gemeinsames Trennzeichen (z. B. Tab: t) getrennt .

Daher müssen Sie zunächst die entsprechende Tabelle (Schema) der Datenbank entsprechend dem Format Ihrer Daten (welche Felder sind vorhanden) entwerfen.

Beispielsweise verwendet die zu verarbeitende Textdatei oder CSV-Datei t als Trennzeichen und jede Zeile enthält drei Datenfelder: ID, Name und Kontostand. Dann müssen wir zuerst diese Tabelle in der Datenbank erstellen:

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

Nachdem die Erstellung erfolgreich war, können Sie sie importieren. Die Bedienungsmethode ist sehr einfach:

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

Hier ist zu beachten, dass wir den MySQL-Konfigurationsparameter „local-infile“ aktivieren müssen, um erfolgreich importieren zu können. Der Grund dafür ist, dass Sie diese Passage im MySQL-Handbuch sehen können:

LOCAL funktioniert nur, wenn sowohl Ihr Server als auch Ihr Client dies zulassen. Wenn mysqld beispielsweise mit --local-infile=0 gestartet wurde, funktioniert LOCAL nicht. Siehe Abschnitt 6.1.6, „Sicherheitsprobleme mit LOAD DATA“. LOKAL“.

Aus Sicherheitsgründen ist dies die Standardkonfiguration von MySQL. Daher müssen wir sicherstellen, dass in der Konfigurationsdatei my.cnf (am Beispiel der Debian-Distribution von Linux, wie Ubuntu, sie befindet sich in /etc/my.cnf):

local-infile=1

Oder fügen Sie das Element --local-infile hinzu, wenn Sie MySQL über die Befehlszeile starten:

mysql --local-infile -uroot -pyourpwd yourdbname

Darüber hinaus können wir auch mysqlimport verwenden, ein offizielles Importprogramm von MySQL. Dieses Programm stellt im Wesentlichen eine Befehlszeilenschnittstelle für LOAD DATA FILE bereit. Es ist leicht zu verstehen und wir werden hier nicht näher darauf eingehen.

2. Importieren Sie die XML-Datei in MySQL

Die Art und Weise, wie dies geschieht, hat viel mit der Form unseres XML zu tun.

Wenn Ihre XML-Datendatei beispielsweise ein sehr standardisiertes Format hat, wie zum Beispiel:

<?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>

Oder

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

Wir können LOAD XML problemlos zum Importieren verwenden. Hier können Sie das offizielle Handbuch von MySQL sehen – LOAD XML-Syntax.

Möglicherweise haben wir jedoch andere Anforderungen. Beispielsweise möchten wir die Felder der XML-Datei möglicherweise Spalten mit unterschiedlichen Namen zuordnen (TABELLE COLUMN). Hierbei ist zu beachten, dass nach MySQL v5.0.7 LOAD XML INFILE oder LOAD DATA INFILE nicht mehr in der MySQL Stored Procedure ausgeführt werden kann. Daher ist der Konvertierungsvorgang anders geschrieben als zuvor. Hier müssen wir die beiden Funktionen Load_File() und ExtractValue() verwenden.

​Das Folgende ist eine Beispiel-XML-Datei und ein Beispielprogramm: Datei:

<?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>

Programm:

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

​In MySQL verwenden Sie dies zum Importieren:

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

Das Programm ist recht einfach, solange Sie MySQL-Skripting verstehen.

Erwähnen Sie hier DELIMITER $$. Wir wissen, dass das Befehlstrennzeichen von MySQL standardmäßig ein Semikolon ist. Das Skript enthält jedoch offensichtlich ein Semikolon, aber wir möchten es nicht sofort ausführen, daher müssen wir das Trennzeichen vorübergehend ändern.

3. Importieren Sie die JSON-Datei in MySQL

Das Importieren von JSON-Dateien in MySQL ist ein sehr interessantes Thema. JSON ist eine Dateistruktur, die heutzutage recht häufig verwendet wird, daher ist die Beherrschung ihres Imports von größerer Bedeutung.

Oftmals erscheinen die von uns verarbeiteten JSON-Daten in der folgenden Form:

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

Anstelle von regulären [{},{},{},{}] (Export einiger NoSQL-Datenbanken).

Diese Situation hat einen Vorteil beim Laden: Da jede Zeile ein JSON-Objekt ist, können wir diese Datei Zeile für Zeile verarbeiten, ohne dass die gesamte Datei (z. B. eine .json-Datei mit vielen G) aufgrund der strengen Struktur von getrennt werden muss JSON. Laden.

Methode 1: Common-Schema verwenden

Common-Schema ist ein weit verbreitetes MySQL-Framework. Es verfügt über umfangreiche Funktionen und eine detaillierte Dokumentation. Wir können seine JSON-Parsing-Funktionen nutzen. (Es verfügt außerdem über praktische Funktionen wie die Konvertierung von JSON in 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)，这需要另开一篇讨论了。

　　谢谢阅读，欢迎指正。

Das obige ist der detaillierte Inhalt vonSo importieren Sie JSON-, Text-, XML- und CSV-Datendateien in die MySQL-Datenbank. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!