如何將 JSON, Text, XML, CSV 資料檔匯入 MySQL資料庫中

　　將外部資料導入(import)資料庫是在資料庫應用中一個很常見的需求。其實這就是在資料的管理與操作中的ETL (Extract, transform, load)的L (Load)部分，也就是說，將特定結構(structure)或格式(format)的資料導入某個目的地(例如資料庫，這裡我們討論MySQL)。

# 　　本文要討論的內容，是如何方便地將多種格式(JSON, Text, XML, CSV)的資料匯入MySQL之中。

　　本文大綱：

1. # 將Text檔案（包括CSV檔案）匯入MySQL

2. 
   

3. # 將XML檔案匯入MySQL

4. 
   

5. # 將JSON檔案匯入MySQL

6. 
   

7. 使用MySQL workbench的Table Data Export and Import Wizard進行JSON或CSV檔案的導入匯出

　1. 將Text檔案（包括CSV檔案）匯入MySQL

# 這裡我們的討論是基於一個假定，Text file和CSV file是有著比較規範的格式的(properly formatted)，比如說每行的每個資料域(field)之間是由一個共同的分隔符號（比如tab : \t）分隔的。

　　那麼首先，你需要根據你的資料的格式（有哪些領域），來設計好資料庫的對應的表 （的Schema）。

舉個例子，要處理的Text檔案或CSV檔案是以\t作為分隔符號的，每行有id, name, balance這麼三個資料域，那麼首先我們需要在資料庫中建立這個表：

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的Manual可以看到這麼一段話：

#

LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. 3.1.1.6, “ LOCAL”.

# 　　這是MySQL出於安全考慮的預設配置。因此，我們需要在設定檔my.cnf中（以Debian發行版的Linux, 如Ubuntu為例， 即是在/etc/my.cnf中），確保：

local-infile=1

　　抑或是在命令列啟動MySQL時加上--local-infile這一項：

mysql --local-infile -uroot -pyourpwd yourdbname

　　此外，我們也可以使用MySQL的一個官方導入程式 mysqlimport，這個程式本質上就是為LOAD DATA FILE提供了一個命令列的interface，很容易理解，我們這裡就不再詳述。

　2. 將XML檔案匯入MySQL

　　這件事的完成方式，與我們的XML的形式有著很大的關係。

　　舉個例子說，當你的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>

　　或

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

　　我們就可以很方便使用LOAD XML來導入，這裡可以參考MySQL的官方手冊--LOAD XML Syntax。

　　然而我們可能有另外一些需求，比如說，我們可能會想要將XML檔案的域映射到不同名字的列(TABLE COLUMN)之中。這裡要注意，MySQL v5.0.7以後，MySQL的Stored Procedure中就不能再執行LOAD XML INFILE 或LOAD DATA INFILE。所以轉換的程式(procedure)的寫法與在此之前有所不同。這裡，我們需要使用 Load_File()和ExtractValue()這兩個函數。

　　以下是一個範例XML檔案和程式：檔案：

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

　　程式：

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

　　在MySQL中，使用它進行導入：

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

　　程式相當的直白，只要了解MySQL的腳本編寫即可。

　　這裡提一下DELIMITER $$。我們知道MySQL的命令分隔符號預設為分號，然而腳本中很顯然是有分號的，但是我們並不希望立即執行，所以我們需要臨時更改分隔符號。

　3. 將JSON檔案匯入MySQL

　　如何將JSON檔案匯入MySQL中，是一個很有趣的話題。 JSON是現在相當常用的檔案結構，所以掌握它的導入具有比較廣泛的意義。

　　很多時候，我們處理的JSON資料是以如下形式出現的：

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

　　而並不是規則的[{},{},{},{}]（一些NoSQL資料庫的Export）。

這樣的情況對於載入有一個好處：因為每一行是一個JSON Object，所以我們便可以按行處理此文件，而不需要因為JSON的嚴格結構將整個文件（比如一個許多G的.json文件）全部載入。

　　方式一 使用common-schema

　　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, Text, XML, CSV 資料檔匯入 MySQL資料庫中的詳細內容。更多資訊請關注PHP中文網其他相關文章！