JSON、テキスト、XML、CSV データ ファイルを MySQL データベースにインポートする方法

外部データをデータベースにインポートすることは、データベース アプリケーションでは非常に一般的な要件です。実際、これはデータ管理と操作における ETL (抽出、変換、ロード) の L (ロード) 部分です。つまり、特定の構造または形式のデータを特定の宛先 (データベースなど、ここでは MySQL について説明します) にインポートします。 ）。

この記事では、複数の形式 (JSON、テキスト、XML、CSV) のデータを MySQL に簡単にインポートする方法について説明します。

この記事の概要:

1. テキスト ファイル (CSV ファイルを含む) を MySQL にインポートします

2. 
   

3. XML ファイルを MySQL にインポートします

4. 
   

5. JSON ファイルを MySQL にインポートします

6. 
   

7. MySQL ワークベンチのテーブル データのエクスポートおよびインポート ウィザードを使用して、JSON または CSV ファイルをインポートおよびエクスポートします

1. テキスト ファイル (CSV ファイルを含む) を MySQL にインポートします

ここでの説明は、テキスト ファイルと CSV ファイルが比較的標準化された (適切にフォーマットされた) 形式であることを前提としています。たとえば、各行の各データ フィールド (フィールド) が共通の区切り文字 (タブ : t など) で区切られています。 。

したがって、まず、データの形式 (どのようなフィールドがあるか) に従って、データベースの対応するテーブル (スキーマ) を設計する必要があります。

たとえば、処理されるテキスト ファイルまたは CSV ファイルは区切り文字として t を使用し、各行には id、name、balance の 3 つのデータ フィールドがあります。次に、最初にデータベースにこのテーブルを作成する必要があります:

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

。 作成が成功したら、インポートできます。操作方法はとても簡単です:

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

ここで注意すべき点は、正常にインポートするには local-infile MySQL 設定パラメータを有効にする必要があるということです。その理由は、MySQL マニュアルに次の一節があるからです:

LOCAL は、サーバーとクライアントの両方が許可するように構成されている場合にのみ機能します。たとえば、mysqld が --local-infile=0 で起動された場合、LOCAL は機能しません。「LOAD DATA のセキュリティの問題」を参照してください。ローカル」

これは、セキュリティ上の理由から MySQL のデフォルト構成です。したがって、構成ファイル my.cnf (Ubuntu などの Linux の Debian ディストリビューションを例として、/etc/my.cnf にある) を確認する必要があります。 または、コマンドラインから MySQL を起動するときに --local-infile を追加します:

local-infile=1

さらに、MySQL の公式インポート プログラムである mysqlimport を使用することもできます。このプログラムは基本的に LOAD DATA FILE 用のコマンド ライン インターフェイスを提供します。これは理解するのが簡単なので、ここでは詳しく説明しません。

2. XML ファイルを MySQL にインポートします

これを行う方法は、XML の形式に大きく関係します。

たとえば、XML データ ファイルが次のような非常に標準化された形式である場合:

mysql --local-infile -uroot -pyourpwd yourdbname

それとも

LOAD XML を使用して簡単にインポートできます。MySQL の公式マニュアル - LOAD XML 構文を参照してください。

ただし、他のニーズがある場合もあります。たとえば、XML ファイルのフィールドを別の名前の列 (TABLE COLUMN) にマップしたい場合があります。ここで、MySQL v5.0.7 以降では、LOAD XML INFILE または LOAD DATA INFILE を MySQL ストアド プロシージャで実行できなくなることに注意してください。そのため、変換手順の書き方が従来とは異なります。ここでは、Load_File() と ExtractValue() という 2 つの関数を使用する必要があります。

以下はサンプル XML ファイルとプログラムです: ファイル:

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

プログラム:

MySQL では、これを使用してインポートします:

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

MySQL スクリプトを理解している限り、プログラムは非常に簡単です。

ここで DELIMITER $$ について言及します。 MySQL のコマンド区切り文字はデフォルトでセミコロンであることはわかっていますが、スクリプトには明らかにセミコロンがありますが、それをすぐに実行したくないため、区切り文字を一時的に変更する必要があります。

3. JSON ファイルを MySQL にインポートします

JSON ファイルを MySQL にインポートする方法は非常に興味深いトピックです。 JSON は現在非常に一般的に使用されているファイル構造であるため、そのインポートをマスターすることはより広い意味を持っています。

多くの場合、私たちが処理する JSON データは次の形式で表示されます:

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

通常の [{}、{}、{}、{}] ではなく (一部の NoSQL データベースのエクスポート)。

この状況には読み込みの利点があります。各行は JSON オブジェクトであるため、ファイル全体 (G が多く含まれる .json ファイルなど) を分割する必要がなく、このファイルを 1 行ずつ処理できます。 JSONをロードします。

方法 1: 共通スキーマを使用する

Common-schema は広く使用されている MySQL フレームワークであり、豊富な機能と詳細なドキュメントが備わっています。 JSON 解析機能を使用できます。 (JSONから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)，这需要另开一篇讨论了。

　　谢谢阅读，欢迎指正。

以上がJSON、テキスト、XML、CSV データ ファイルを MySQL データベースにインポートする方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。