mysql插入json自動(dòng)轉(zhuǎn)義方法，通過(guò)json_encode后的數(shù)據(jù)寫(xiě)入Mysql數(shù)據(jù)庫(kù)時(shí)，mysql對(duì)會(huì)json_encode值里面有中文的unicode反斜杠進(jìn)行轉(zhuǎn)義，這是在數(shù)據(jù)庫(kù)層的轉(zhuǎn)義。
站在用戶的角度思考問(wèn)題，與客戶深入溝通，找到周寧網(wǎng)站設(shè)計(jì)與周寧網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類(lèi)型包括：網(wǎng)站設(shè)計(jì)制作、網(wǎng)站設(shè)計(jì)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、主機(jī)域名、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋周寧地區(qū)。
將外部數(shù)據(jù)導(dǎo)入(import)數(shù)據(jù)庫(kù)是在數(shù)據(jù)庫(kù)應(yīng)用中一個(gè)很常見(jiàn)的需求。其實(shí)這就是在數(shù)據(jù)的管理和操作中的ETL (Extract, transform, load)的L (Load)部分，也就是說(shuō)，將特定結(jié)構(gòu)(structure)或者格式(format)的數(shù)據(jù)導(dǎo)入某個(gè)目的地(比如數(shù)據(jù)庫(kù)，這里我們討論MySQL)。
ETL Process
本文要討論的內(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進(jìn)行JSON或CSV文件的導(dǎo)入導(dǎo)出
1. 將Text文件（包括CSV文件）導(dǎo)入MySQL
這里我們的討論是基于一個(gè)假定，Text file和CSV file是有著比較規(guī)范的格式的(properly formatted)，比如說(shuō)每行的每個(gè)數(shù)據(jù)域(field)之間是由一個(gè)共同的分隔符（比如tab: \t）分隔的。
那么首先，你需要根據(jù)你的數(shù)據(jù)的格式（有哪些域），來(lái)設(shè)計(jì)好數(shù)據(jù)庫(kù)的對(duì)應(yīng)的表 （的Schema）。
舉個(gè)例子，要處理的Text文件或者CSV文件是以\t作為分隔符的，每行有id, name, balance這么三個(gè)數(shù)據(jù)域，那么首先我們需要在數(shù)據(jù)庫(kù)中創(chuàng)建這個(gè)表：
CREATE TABLE sometable(id INT, name VARCHAR(255), balance DECIMAL(8,4));
創(chuàng)建成功以后就可以導(dǎo)入了。操作方式很簡(jiǎn)單：
LOAD DATA LOCAL INFILE '你的文件路徑（如~/file.csv）' INTO TABLE sometable FIELDS TERMINATED BY '\t' [ENCLOSED BY '"'(可選)] LINES TERMINATED BY '\n' (id, name, balance)
這里要注意的是，我們需要開(kāi)啟local-infile這個(gè)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出于安全考慮的默認(rèn)配置。因此，我們需要在配置文件my.cnf中（以Debian發(fā)行版的Linux, 如Ubuntu為例， 即是在/etc/my.cnf中），確保：
local-infile=1
抑或是在命令行啟動(dòng)MySQL時(shí)加上--local-infile這一項(xiàng)：
mysql --local-infile -uroot -pyourpwd yourdbname
此外，我們也可以使用MySQL的一個(gè)官方導(dǎo)入程序mysqlimport ，這個(gè)程序本質(zhì)上就是為L(zhǎng)OAD DATA FILE提供了一個(gè)命令行的interface，很容易理解，我們這里就不再詳述。
2. 將XML文件導(dǎo)入MySQL
這件事的完成方式，與我們的XML的形式有著很大的關(guān)系。
舉個(gè)例子說(shuō)，當(dāng)你的XML數(shù)據(jù)文件有著很非常規(guī)范的格式，比如：
?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來(lái)導(dǎo)入，這里可以參見(jiàn)MySQL的官方手冊(cè)--LOAD XML Syntax。
然而我們可能有另外一些需求，比如說(shuō)，我們可能會(huì)想要將XML文件的域映射到不同名字的列(TABLE COLUMN)之中。這里要注意，MySQL v5.0.7以后，MySQL的Stored Procedure中不能再運(yùn)行LOAD XML INFILE 或者LOAD DATA INFILE。所以轉(zhuǎn)換的程序(procedure)的編寫(xiě)方式與在此之前有所不同。這里，我們需要使用Load_File()和ExtractValue()這兩個(gè)函數(shù)。
以下是一個(gè)示例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, '[', 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中，使用它進(jìn)行導(dǎo)入：
call import_some_xml('你的XML文件路徑', '/some_list/someone');
程序相當(dāng)?shù)闹卑祝灰私庖幌翸ySQL的腳本編寫(xiě)即可。
這里提一下DELIMITER $$。我們知道MySQL的命令分隔符默認(rèn)為分號(hào)，然而腳本中很顯然是有分號(hào)的，但是我們并不希望立即執(zhí)行，所以我們需要臨時(shí)更改分隔符。
3. 將JSON文件導(dǎo)入MySQL
如何將JSON文件導(dǎo)入MySQL中，是一個(gè)很有趣的話題。JSON是一種現(xiàn)在相當(dāng)常用的文件結(jié)構(gòu)，所以掌握它的導(dǎo)入具有比較廣泛的意義。
很多時(shí)候，我們處理的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ù)庫(kù)的Export）。
這樣的形勢(shì)對(duì)于載入有一個(gè)好處：因?yàn)槊恳恍惺且粋€(gè)JSON Object，所以我們便可以按行處理此文件，而不需要因?yàn)镴SON的嚴(yán)格結(jié)構(gòu)將整個(gè)文件（比如一個(gè)許多G的.json文件）全部載入。
方式一 使用common-schema
common-schema是一個(gè)應(yīng)用很廣泛的MySQL的框架，它有著很豐富的功能和詳細(xì)的文檔。我們可以使用它的JSON解析的功能。（它還具有JSON轉(zhuǎn)換成XML等等方便的功能）
具體說(shuō)來(lái)，將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ù)接受兩個(gè)參數(shù)，一個(gè)是json_text，表示json文件的內(nèi)容，另一個(gè)是xpath，表示數(shù)據(jù)的結(jié)構(gòu)（這里可以類(lèi)比XML文件的處理）。很多讀者應(yīng)該知道，XPath是用來(lái)對(duì)XML中的元素進(jìn)行定位的，這里也可以作一樣的理解。
以本段開(kāi)始的幾行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是一個(gè)event，不同的event type對(duì)應(yīng)不同的event data。這部分知識(shí)可以參看Event Data for Specific Event Types
如果感興趣，可以參看其源碼。參看一個(gè)受到廣泛使用的項(xiàng)目的源碼，對(duì)于自身成長(zhǎng)是很有益的。
當(dāng)然了，我們也可以像之前處理XML文件導(dǎo)入一樣，自己編寫(xiě)程序。這里便不再給出實(shí)例程序，有興趣的讀者可以自行編寫(xiě)或者跟筆者交流。
方式二 使用mysqljsonimport
這是Anders Karlsson的一個(gè)完成度很高的作品。這一份程序由C寫(xiě)成。它依賴于一個(gè)JSON Parser，Jansson。他們都有著比較好的維護(hù)和文檔，所以使用上體驗(yàn)很好。
mysqljsonimport的下載在SourceForge上。具體使用參照其文檔即可。
為了方便不熟悉源碼安裝的朋友，筆者在這里提一下安裝流程和注意事項(xiàng)。
安裝命令順序如下：
$ wget
$ 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)的默認(rèn)路徑。很關(guān)鍵的，而且很容易被不熟悉的朋友忽略的是，這一個(gè)C程序要成功編譯和運(yùn)行，是需要MySQL的C API的，所以需要安裝的依賴，除了jansson，還有l(wèi)ibmysqlclient-dev。
jansson的安裝就是簡(jiǎn)單的源碼安裝，libmysqlclient-dev則可以使用包管理工具(比如ubuntu中使用apt-get即可；編譯和安裝前，建議先sudo apt-get update以避免不必要的麻煩)。
導(dǎo)入命令：
$ ./mysqljsonimport –-database test –-table tablename jsonfilename
還有一個(gè)parser，作者是Kazuho，感興趣的讀者可以參看一下，他的相關(guān)博文是mysql_json - a MySQL UDF for parsing JSON ，github項(xiàng)目是mysql_json。
4. 使用MySQL workbench
Workbench這個(gè)工具對(duì)于許多不熟悉SQL語(yǔ)言或者命令行的朋友還是很方便和友好的。利用它，可以方便地導(dǎo)入和導(dǎo)出CSV和JSON文件。
具體操作圖例參見(jiàn)MySQL官方手冊(cè)即可:Table Data Export and Import Wizard，這里不再贅述。
文／freenik（簡(jiǎn)書(shū)作者）
原文鏈接：
解析json字符串，定義一個(gè)dto對(duì)象，字段名和類(lèi)型和json里面一致，然后用jdbc連接數(shù)據(jù)庫(kù)，insert into插入表中，代碼自己寫(xiě)吧，沒(méi)時(shí)間幫你寫(xiě)
我們知道，JSON是一種輕量級(jí)的數(shù)據(jù)交互的格式，大部分NO SQL數(shù)據(jù)庫(kù)的存儲(chǔ)都用JSON。MySQL從5.7開(kāi)始支持JSON格式的數(shù)據(jù)存儲(chǔ)，并且新增了很多JSON相關(guān)函數(shù)。MySQL 8.0 又帶來(lái)了一個(gè)新的把JSON轉(zhuǎn)換為T(mén)ABLE的函數(shù)JSON_TABLE，實(shí)現(xiàn)了JSON到表的轉(zhuǎn)換。
舉例一
我們看下簡(jiǎn)單的例子：
簡(jiǎn)單定義一個(gè)兩級(jí)JSON 對(duì)象
mysql set @ytt='{"name":[{"a":"ytt","b":"action"}, ?{"a":"dble","b":"shard"},{"a":"mysql","b":"oracle"}]}';Query OK, 0 rows affected (0.00 sec)
第一級(jí)：
mysql select json_keys(@ytt);+-----------------+| json_keys(@ytt) |+-----------------+| ["name"] ? ? ? ?|+-----------------+1 row in set (0.00 sec)
第二級(jí)：
mysql select json_keys(@ytt,'$.name[0]');+-----------------------------+| json_keys(@ytt,'$.name[0]') |+-----------------------------+| ["a", "b"] ? ? ? ? ? ? ? ? ?|+-----------------------------+1 row in set (0.00 sec)
我們使用MySQL 8.0 的JSON_TABLE 來(lái)轉(zhuǎn)換 @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 (0.00 sec)
舉例二
再來(lái)一個(gè)復(fù)雜點(diǎn)的例子，用的是EXPLAIN 的JSON結(jié)果集。
JSON 串 @json_str1。
set @json_str1 = ' { ?"query_block": { ? ?"select_id": 1, ? ?"cost_info": { ? ? ?"query_cost": "1.00" ? ?}, ? ?"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": "100.00", ? ? ?"cost_info": { ? ? ? ?"read_cost": "0.00", ? ? ? ?"eval_cost": "0.20", ? ? ? ?"prefix_cost": "0.00", ? ? ? ?"data_read_per_join": "176" ? ? ?}, ? ? ?"used_columns": [ ? ? ? ?"id", ? ? ? ?"log_time", ? ? ? ?"str1", ? ? ? ?"str2" ? ? ?] ? ?} ?}}';
第一級(jí)：
mysql select json_keys(@json_str1) as 'first_object';+-----------------+| first_object ? ?|+-----------------+| ["query_block"] |+-----------------+1 row in set (0.00 sec)
第二級(jí)：
mysql select json_keys(@json_str1,'$.query_block') as 'second_object';+-------------------------------------+| second_object ? ? ? ? ? ? ? ? ? ? ? |+-------------------------------------+| ["table", "cost_info", "select_id"] |+-------------------------------------+1 row in set (0.00 sec)
第三級(jí)：
mysql ?select json_keys(@json_str1,'$.query_block.table') 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 (0.01 sec)
第四級(jí)：
mysql select json_extract(@json_str1,'$.query_block.table.cost_info') as 'forth_object'\G*************************** 1. row ***************************forth_object: {"eval_cost":"0.20","read_cost":"0.00","prefix_cost":"0.00","data_read_per_join":"176"}1 row in set (0.00 sec)
那我們把這個(gè)JSON 串轉(zhuǎn)換為表。
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 | 100.00 | 0.20 | 0.00 | 0.00 | 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 | 1.00 | ? ?1 |
+-------+------+-------+--------+------+------+------+------+------+----------+-------+------+------+------+------+------+------+
2 rows in set (0.00 sec)
當(dāng)然，JSON_table 函數(shù)還有其他的用法，我這里不一一列舉了，詳細(xì)的參考手冊(cè)。
請(qǐng)點(diǎn)擊輸入圖片描述
MySQL支持JSON數(shù)據(jù)類(lèi)型。相比于Json格式的字符串類(lèi)型，JSON數(shù)據(jù)類(lèi)型的優(yōu)勢(shì)有：
存儲(chǔ)在JSON列中的任何JSON文檔的大小都受系統(tǒng)變量 max_allowed_packet 的值的限制，可以使用 JSON_STORAGE_SIZE() 函數(shù)獲得存儲(chǔ)JSON文檔所需的空間。
在MySQL8.0中，優(yōu)化器可以執(zhí)行JSON列的局部就地更新，而不用刪除舊文檔再將整個(gè)新文檔寫(xiě)入該列。局部更新的條件：
JSON數(shù)組包含在 字符 [ 和 ] 字符中，其中為一個(gè)由逗號(hào)分隔的值列表：
JSON對(duì)象包含在字符 { 和 } 字符中，其中為一組由逗號(hào)分隔的鍵值對(duì)，鍵必須是字符串：
在JSON數(shù)組和JSON對(duì)象的值中允許嵌套：
下例中向創(chuàng)建一個(gè)只有一個(gè)JSON列的表格 t_json ，并向其中添加JSON值：
若添加的值為非JSON格式，則報(bào)錯(cuò)：
查看 t_json :
如果傳入的參數(shù)不能組成鍵值對(duì)，則報(bào)錯(cuò)：
因此我們也可以使用以上三種方法向表中添加JSON值，可以一定程度地避免輸入格式錯(cuò)誤：
解析字符串并發(fā)現(xiàn)字符串是有效的JSON文檔時(shí)，它在被解析時(shí)也會(huì)被規(guī)范化。對(duì)于重復(fù)的鍵（ key ）,后面的值（ value ）會(huì)覆蓋前面的值。如下：
這種“覆蓋”在向JSON列添加值時(shí)也會(huì)發(fā)生。
在MySQL8.0.3之前的版本中，與此相反，對(duì)于被重復(fù)的鍵，它的第一個(gè)值會(huì)被保留，后添加的值則會(huì)被拋棄。
MySQL8.0.3及更高版本中，有兩種合并函數(shù)： JSON_MERGE_PRESERVE() 和 JSON_MERGE_PATCH() 。下面具討論它們的區(qū)別。
合并數(shù)組時(shí)， JSON_MERGE_PRESERVE 只保留最后傳入的數(shù)組參數(shù)，而 JSON_MERGE_PRESERVE 則按傳入順序?qū)?shù)組參數(shù)連接。
合并對(duì)象時(shí)，對(duì)于重復(fù)鍵， JSON_MERGE_PRESERVE 只保留最后傳入的鍵值，而 JSON_MERGE_PRESERVE 重復(fù)鍵的所有值保留為數(shù)組。
在了解搜索和修改JSON值之前，先來(lái)看看JSON的路徑語(yǔ)法。
JSON_EXTRACT 提取JSON值，直接看例子：
JSON_REPLACE 與 JSON_SET 的區(qū)別：
JSON_INSERT 和 JSON_SET :
JSON_REMOVE :
可以使用 = ， ， = ， ， = ， ， != ，和 = 對(duì)JSON值進(jìn)行比較。
JSON值的比較先比較值的類(lèi)型。如果類(lèi)型不同，則直接 返回類(lèi)型的優(yōu)先級(jí)的比較結(jié)果；如果類(lèi)型相同，再進(jìn)行值的內(nèi)容的比較。
OPAQUE 值是不屬于其他類(lèi)型的值。
轉(zhuǎn)換規(guī)則為：
重慶分公司
重慶分公司
