如何将JSON，Text，XML，CSV 数据文件导入 MySQL

将外部数据导入(import)数据库是在数据库应用中一个很常见的需求。其实这就是在数据的管理和 *** 作中的ETL (Extract, transform, load)的L (Load)部分，也就是说，将特定结构(structure)或者格式(format)的数据导入某个目的地(比如数据库，这里我们讨论MySQL)。
ETL Process
本文要讨论的内容，是如何方便地将多种格式(JSON, Text, XML, CSV)的数据导入MySQL之中。
本文大纲：
将Text文件（包括CSV文件）导入MySQL
将XML文件导入MySQL
将JSON文件导入MySQL
使用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 '你的文件路径（如~/filecsv）' 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 See Section 616, “Security Issues with LOAD DATA LOCAL”
这是MySQL出于安全考虑的默认配置。因此，我们需要在配置文件mycnf中（以Debian发行版的Linux, 如Ubuntu为例， 即是在/etc/mycnf中），确保：
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="10">
<row>
<field name="id">1</field>
<field name="name">Free</field>
<field name="balance">23333333</field>
</row>
<row>
<field name="id">2</field>
<field name="name">Niki</field>
<field name="balance">12892333</field>
</row>
或者
<row column1="value1" column2="value2" />
我们就可以很方便使用LOAD XML来导入，这里可以参见MySQL的官方手册--LOAD XML Syntax。
然而我们可能有另外一些需求，比如说，我们可能会想要将XML文件的域映射到不同名字的列(TABLE COLUMN)之中。这里要注意，MySQL v507以后，MySQL的Stored Procedure中不能再运行LOAD XML INFILE 或者LOAD DATA INFILE。所以转换的程序(procedure)的编写方式与在此之前有所不同。这里，我们需要使用Load_File()和ExtractValue()这两个函数。
以下是一个示例XML文件和程序：
文件：
<xml version="10">
<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, '[', 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中，使用它进行导入：
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_schemaextract_json_value(fevent_data,'/name') as name, common_schemaextract_json_value(fevent_data,'/gender') as gender, sum(fevent_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 >List list = new ArraryList();
listadd(employee); //添加类employee
listadd(employer); //添加类employer
jsonArray = JSONArrayfromObject(list); //转换为json数组
这样就行了

我们知道，JSON是一种轻量级的数据交互的格式，大部分NO SQL数据库的存储都用JSON。MySQL从57开始支持JSON格式的数据存储，并且新增了很多JSON相关函数。MySQL 80 又带来了一个新的把JSON转换为TABLE的函数JSON_TABLE，实现了JSON到表的转换。

举例一

我们看下简单的例子：

简单定义一个两级JSON 对象

mysql> set @ytt='{"name":[{"a":"ytt","b":"action"},  {"a":"dble","b":"shard"},{"a":"mysql","b":"oracle"}]}';Query OK, 0 rows affected (000 sec)

第一级：

mysql> select json_keys(@ytt);+-----------------+| json_keys(@ytt) |+-----------------+| ["name"]        |+-----------------+1 row in set (000 sec)

第二级：

mysql> select json_keys(@ytt,'$name[0]');+-----------------------------+| json_keys(@ytt,'$name[0]') |+-----------------------------+| ["a", "b"]                  |+-----------------------------+1 row in set (000 sec)

我们使用MySQL 80 的JSON_TABLE 来转换 @ytt。

mysql> select from json_table(@ytt,'$name[]' columns (f1 varchar(10) path '$a', f2 varchar(10) path '$b')) as tt;

+-------+--------+

| f1    | f2     |

+-------+--------+

| ytt   | action |

| dble  | shard  |

| mysql | oracle |

+-------+--------+

3 rows in set (000 sec)

举例二

再来一个复杂点的例子，用的是EXPLAIN 的JSON结果集。

JSON 串 @json_str1。

set @json_str1 = ' {  "query_block": {    "select_id": 1,    "cost_info": {      "query_cost": "100"    },    "table": {      "table_name": "bigtable",      "access_type": "const",      "possible_keys": [        "id"      ],      "key": "id",      "used_key_parts": [        "id"      ],      "key_length": "8",      "ref": [        "const"      ],      "rows_examined_per_scan": 1,      "rows_produced_per_join": 1,      "filtered": "10000",      "cost_info": {        "read_cost": "000",        "eval_cost": "020",        "prefix_cost": "000",        "data_read_per_join": "176"      },      "used_columns": [        "id",        "log_time",        "str1",        "str2"      ]    }  }}';

第一级：

mysql> select json_keys(@json_str1) as 'first_object';+-----------------+| first_object    |+-----------------+| ["query_block"] |+-----------------+1 row in set (000 sec)

第二级：

mysql> select json_keys(@json_str1,'$query_block') as 'second_object';+-------------------------------------+| second_object                       |+-------------------------------------+| ["table", "cost_info", "select_id"] |+-------------------------------------+1 row in set (000 sec)

第三级：

mysql>  select json_keys(@json_str1,'$query_blocktable') as 'third_object'\G 1 row third_object: ["key","ref","filtered","cost_info","key_length","table_name","access_type","used_columns","possible_keys","used_key_parts","rows_examined_per_scan","rows_produced_per_join"]1 row in set (001 sec)

第四级：

mysql> select json_extract(@json_str1,'$query_blocktablecost_info') as 'forth_object'\G 1 row forth_object: {"eval_cost":"020","read_cost":"000","prefix_cost":"000","data_read_per_join":"176"}1 row in set (000 sec)

那我们把这个JSON 串转换为表。

SELECT FROM JSON_TABLE(@json_str1,

"$query_block"

COLUMNS(

rowid FOR ORDINALITY,

NESTED PATH '$table'

COLUMNS (

a1_1 varchar(100) PATH '$key',

a1_2 varchar(100) PATH '$ref[0]',

a1_3 varchar(100) PATH '$filtered',

nested path '$cost_info'

columns (

a2_1 varchar(100) PATH '$eval_cost' ,

a2_2 varchar(100) PATH '$read_cost',

a2_3 varchar(100) PATH '$prefix_cost',

a2_4 varchar(100) PATH '$data_read_per_join'

),

a3 varchar(100) PATH '$key_length',

a4 varchar(100) PATH '$table_name',

a5 varchar(100) PATH '$access_type',

a6 varchar(100) PATH '$used_key_parts[0]',

a7 varchar(100) PATH '$rows_examined_per_scan',

a8 varchar(100) PATH '$rows_produced_per_join',

a9 varchar(100) PATH '$key'

),

NESTED PATH '$cost_info'

columns (

b1_1 varchar(100) path '$query_cost'

),

c INT path "$select_id"

)

) AS tt;

+-------+------+-------+--------+------+------+------+------+------+----------+-------+------+------+------+------+------+------+

| rowid | a1_1 | a1_2  | a1_3   | a2_1 | a2_2 | a2_3 | a2_4 | a3   | a4       | a5    | a6   | a7   | a8   | a9   | b1_1 | c    |

+-------+------+-------+--------+------+------+------+------+------+----------+-------+------+------+------+------+------+------+

|     1 | id   | const | 10000 | 020 | 000 | 000 | 176  | 8    | bigtable | const | id   | 1    | 1    | id   | NULL |    1 |

|     1 | NULL | NULL  | NULL   | NULL | NULL | NULL | NULL | NULL | NULL     | NULL  | NULL | NULL | NULL | NULL | 100 |    1 |

+-------+------+-------+--------+------+------+------+------+------+----------+-------+------+------+------+------+------+------+

2 rows in set (000 sec)

当然，JSON_table 函数还有其他的用法，我这里不一一列举了，详细的参考手册。

后台解析ajax传递的数据，这主要看前端传递数据的格式，主要有以下几种常见的。
1、前端ajax传递表单数据，类似{name:'zhangsan',age:'17'}
后台只需利用requestgetParameter("name")形式即可获取对应的value值。
2、前端传递json数据格式。后台可以直接获取json字符串，然后利用相关的API转成对应的Java对象。或者直接利用springMvc的注解@RequestBody注解。
@RequestMapping(value = "/save")
public void save（@RequestBody User user）{
}
这样传递的json数据会自动封装成user对象。
如果当前传递的是一个json数组，则后端可以定义一个VO对象，vo对象中存放一个userList。
public class UserVO{
private List<User> dataList;
}
@RequestMapping(value = "/save")
public void save（@RequestBody UserVO userVO）{

1、使用原生的解析：
String json = "";
JSONArray array= new JSONArray(json);
//遍历数组里的值，得到每个独立的对象，然后获取对应的值设置到声明好的对象中，最终创建对象完成后添加到集合中，如我自己代码里的片段:
for (int j = 0; j < arraylength(); j++) {
obj = arraygetJSONObject(j);
Data data = new Data();
datasetThumbnail(objgetString("thumbnail"));
datasetTitle(objgetString("title"));
datasetUrl(objgetString("url"));
mDataListadd(data);
}
2、使用第三方包如Gson，但是这个你得保证你的JSON字符串个z

---