摘要：舉個例子，要處理的文件或者文件是以作為分隔符的，每行有這么三個數(shù)據(jù)域，那么首先我們需要在數(shù)據(jù)庫中創(chuàng)建這個表創(chuàng)建成功以后就可以導(dǎo)入了。

文/freenik

將外部數(shù)據(jù)導(dǎo)入(import)數(shù)據(jù)庫是在數(shù)據(jù)庫應(yīng)用中一個很常見的需求。其實這就是在數(shù)據(jù)的管理和操作中的ETL (Extract, transform, load)的L (Load)部分，也就是說，將特定結(jié)構(gòu)(structure)或者格式(format)的數(shù)據(jù)導(dǎo)入某個目的地(比如數(shù)據(jù)庫，這里我們討論MySQL)。

本文要討論的內(nèi)容，是如何方便地將多種格式(JSON, Text, XML, CSV)的數(shù)據(jù)導(dǎo)入MySQL之中。

本文大綱：

將Text文件（包括CSV文件）導(dǎo)入MySQL

將XML文件導(dǎo)入MySQL

將JSON文件導(dǎo)入MySQL

使用MySQL workbench的Table Data Export and Import Wizard進行JSON或CSV文件的導(dǎo)入導(dǎo)出

這里我們的討論是基于一個假定，Text file和CSV file是有著比較規(guī)范的格式的(properly formatted)，比如說每行的每個數(shù)據(jù)域(field)之間是由一個共同的分隔符（比如tab: ）分隔的。

那么首先，你需要根據(jù)你的數(shù)據(jù)的格式（有哪些域），來設(shè)計好數(shù)據(jù)庫的對應(yīng)的表 （的Schema）。

舉個例子，要處理的Text文件或者CSV文件是以 作為分隔符的，每行有id, name, balance這么三個數(shù)據(jù)域，那么首先我們需要在數(shù)據(jù)庫中創(chuàng)建這個表：

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

創(chuàng)建成功以后就可以導(dǎo)入了。操作方式很簡單：

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

這里要注意的是，我們需要開啟local-infile這個MySQL的配置參數(shù)，才能夠成功導(dǎo)入。究其原因，從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. See Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.

這是MySQL出于安全考慮的默認配置。因此，我們需要在配置文件my.cnf中（以Debian發(fā)行版的Linux, 如Ubuntu為例， 即是在/etc/my.cnf中），確保：

local-infile=1

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

mysql --local-infile -uroot -pyourpwd yourdbname

此外，我們也可以使用MySQL的一個官方導(dǎo)入程序 mysqlimport，這個程序本質(zhì)上就是為LOAD DATA FILE提供了一個命令行的interface，很容易理解，我們這里就不再詳述。

這件事的完成方式，與我們的XML的形式有著很大的關(guān)系。

舉個例子說，當(dāng)你的XML數(shù)據(jù)文件有著很非常規(guī)范的格式，比如：

  
    1
    Free
    2333.3333
   

  
    2
    Niki
    1289.2333
  

或者

我們就可以很方便使用LOAD XML來導(dǎo)入，這里可以參見MySQL的官方手冊--LOAD XML Syntax。

然而我們可能有另外一些需求，比如說，我們可能會想要將XML文件的域映射到不同名字的列(TABLE COLUMN)之中。這里要注意，MySQL v5.0.7以后，MySQL的Stored Procedure中不能再運行LOAD XML INFILE 或者LOAD DATA INFILE。所以轉(zhuǎn)換的程序(procedure)的編寫方式與在此之前有所不同。這里，我們需要使用 Load_File()和ExtractValue()這兩個函數(shù)。

以下是一個示例XML文件和程序：
文件：

程序：

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, "[", v_row_index, "]/@*");
        insert into applicants values (
            extractValue(xml_content, concat(v_xpath_row, "[1]")),
            extractValue(xml_content, concat(v_xpath_row, "[2]")),
            extractValue(xml_content, concat(v_xpath_row, "[3]"))
        );
    end while;
END

在MySQL中，使用它進行導(dǎo)入：

call import_some_xml("你的XML文件路徑", "/some_list/someone");

程序相當(dāng)?shù)闹卑祝灰私庖幌翸ySQL的腳本編寫即可。

這里提一下DELIMITER $$。我們知道MySQL的命令分隔符默認為分號，然而腳本中很顯然是有分號的，但是我們并不希望立即執(zhí)行，所以我們需要臨時更改分隔符。

如何將JSON文件導(dǎo)入MySQL中，是一個很有趣的話題。JSON是一種現(xiàn)在相當(dāng)常用的文件結(jié)構(gòu)，所以掌握它的導(dǎo)入具有比較廣泛的意義。

很多時候，我們處理的JSON數(shù)據(jù)是以如下形式出現(xiàn)的：

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

而并不是規(guī)整的[{},{},{},{}]（一些NoSQL數(shù)據(jù)庫的Export）。

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

common-schema是一個應(yīng)用很廣泛的MySQL的框架，它有著很豐富的功能和詳細的文檔。我們可以使用它的JSON解析的功能。（它還具有JSON轉(zhuǎn)換成XML等等方便的功能）

具體說來，將common-schema導(dǎo)入之后，使用它的extract_json_value函數(shù)即可。源碼中：

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

該函數(shù)接受兩個參數(shù)，一個是json_text，表示json文件的內(nèi)容，另一個是xpath，表示數(shù)據(jù)的結(jié)構(gòu)（這里可以類比XML文件的處理）。很多讀者應(yīng)該知道，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;

關(guān)于event_data，我們需要先理解LOAD DATA INFILE是一個event，不同的event type對應(yīng)不同的event data。這部分知識可以參看Event Data for Specific Event Types

如果感興趣，可以參看其源碼。參看一個受到廣泛使用的項目的源碼，對于自身成長是很有益的。

當(dāng)然了，我們也可以像之前處理XML文件導(dǎo)入一樣，自己編寫程序。這里便不再給出實例程序，有興趣的讀者可以自行編寫或者跟筆者交流。

這是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的路徑是系統(tǒng)的默認路徑。很關(guān)鍵的，而且很容易被不熟悉的朋友忽略的是，這一個C程序要成功編譯和運行，是需要MySQL的C API的，所以需要安裝的依賴，除了jansson，還有libmysqlclient-dev。

jansson的安裝就是簡單的源碼安裝，libmysqlclient-dev則可以使用包管理工具(比如ubuntu中使用apt-get即可；編譯和安裝前，建議先sudo apt-get update以避免不必要的麻煩)。

導(dǎo)入命令：

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

還有一個parser，作者是Kazuho，感興趣的讀者可以參看一下，他的相關(guān)博文是mysql_json - a MySQL UDF for parsing JSON ，github項目是mysql_json。

Workbench這個工具對于許多不熟悉SQL語言或者命令行的朋友還是很方便和友好的。利用它，可以方便地導(dǎo)入和導(dǎo)出CSV和JSON文件。

具體操作圖例參見MySQL官方手冊即可:Table Data Export and Import Wizard，這里不再贅述。

本文介紹了將不同格式（JSON, Text, XML, CSV）的文件導(dǎo)入MySQL數(shù)據(jù)庫的一些詳細手段，并進行了一些分析，目的在于幫助讀者掃除一些導(dǎo)入的障礙，理清一些概念。之所以沒有討論導(dǎo)出，是因為導(dǎo)出是一個MySQL到外的操作，是以MySQL本身為轉(zhuǎn)移的，只要參考MySQL本身的機理即可。

真正對于大量數(shù)據(jù)的導(dǎo)入導(dǎo)出，需要思考的問題會很多(比如說在導(dǎo)入時，如何考慮Sharding)，這需要另開一篇討論了。

謝謝閱讀，歡迎指正。

作者: 
freenik@Jianshu, SegmentFault; 
VonRyan@CSDN

本文采用以下協(xié)議進行授權(quán)：自由轉(zhuǎn)載-非商用-非衍生-保持署名 | Creative Commons BY-NC-ND 3.0，轉(zhuǎn)載請標明作者出處，尊重原創(chuàng)。