How to import JSON, Text, XML, CSV data files into MySQL database

Importing external data into the database is a very common requirement in database applications. In fact, this is the L (Load) part of ETL (Extract, transform, load) in data management and operation, that is, importing data of a specific structure or format into a certain destination (such as Database, here we discuss MySQL).

What this article will discuss is how to easily import data in multiple formats (JSON, Text, XML, CSV) into MySQL.

Outline of this article:

1. Import Text files (including CSV files) into MySQL

2. 
   

3. Import XML file into MySQL

4. 
   

5. Import JSON file into MySQL

6. 
   

7. Use the Table Data Export and Import Wizard of MySQL workbench to import and export JSON or CSV files

1. Import Text files (including CSV files) into MySQL

Our discussion here is based on the assumption that Text file and CSV file have a relatively standardized format (properly formatted). For example, each data field (field) in each line is separated by a common delimiter (such as tab : \t) separated.

So first, you need to design the corresponding table (Schema) of the database according to the format of your data (what fields are there).

For example, the Text file or CSV file to be processed uses \t as the delimiter, and each line has three data fields: id, name, and balance. Then first we need to create this table in the database:

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

After the creation is successful, you can import it. The operation method is very simple:

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

What should be noted here is that we need to enable the local-infile MySQL configuration parameter to successfully import. The reason is that you can see this passage from 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”.

This is the default configuration of MySQL for security reasons. Therefore, we need to ensure that in the configuration file my.cnf (taking the Debian distribution of Linux, such as Ubuntu, as an example, it is in /etc/my.cnf):

local-infile=1

Or add the --local-infile item when starting MySQL from the command line:

mysql --local-infile -uroot -pyourpwd yourdbname

In addition, we can also use mysqlimport, an official import program of MySQL. This program essentially provides a command line interface for LOAD DATA FILE. It is easy to understand and we will not go into details here.

2. Import the XML file into MySQL

The way this is done has a lot to do with the form of our XML.

For example, when your XML data file has a very standardized format, such as:

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

Or

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

We can easily use LOAD XML to import. Here you can see the official manual of MySQL - LOAD XML Syntax.

However, we may have other needs. For example, we may want to map the fields of the XML file to columns with different names (TABLE COLUMN). It should be noted here that after MySQL v5.0.7, LOAD XML INFILE or LOAD DATA INFILE can no longer be run in the MySQL Stored Procedure. Therefore, the conversion procedure is written differently from before. Here, we need to use the two functions Load_File() and ExtractValue().

​The following is a sample XML file and program: File:

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

Program:

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, use this to import:

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

The program is quite straightforward, as long as you understand MySQL scripting.

Mention DELIMITER $$ here. We know that MySQL's command delimiter defaults to a semicolon. However, there is obviously a semicolon in the script, but we do not want to execute it immediately, so we need to temporarily change the delimiter.

3. Import the JSON file into MySQL

How to import JSON files into MySQL is a very interesting topic. JSON is a file structure that is quite commonly used now, so mastering its import is of wider significance.

Many times, the JSON data we process appears in the following form:

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

Rather than regular [{},{},{},{}] (Export of some NoSQL databases).

This situation has an advantage for loading: because each line is a JSON Object, we can process this file line by line, without the need to separate the entire file (such as a .json file with many G) due to the strict structure of JSON. Load.

Method 1: Use common-schema

Common-schema is a widely used MySQL framework. It has rich functions and detailed documentation. We can use its JSON parsing capabilities. (It also has convenient functions such as converting JSON to XML, etc.)

　　具体说来，将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)，这需要另开一篇讨论了。

　　谢谢阅读，欢迎指正。

The above is the detailed content of How to import JSON, Text, XML, CSV data files into MySQL database. For more information, please follow other related articles on the PHP Chinese website!