mysql插入json自動轉義

mysql插入json自動轉義方法，通過json_encode后的數據寫入Mysql數據庫時，mysql對會json_encode值里面有中文的unicode反斜杠進行轉義，這是在數據庫層的轉義。

站在用戶的角度思考問題，與客戶深入溝通，找到周寧網站設計與周寧網站推廣的解決方案，憑借多年的經驗，讓設計與互聯網技術結合，創造個性化、用戶體驗好的作品，建站類型包括：網站設計制作、網站設計、企業官網、英文網站、手機端網站、網站推廣、主機域名、網站空間、企業郵箱。業務覆蓋周寧地區。

如何將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 '你的文件路徑（如~/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. See Section 6.1.6, “Security Issues with LOAD DATA 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, '[', 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_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

$ 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，這里不再贅述。

文／freenik（簡書作者）

原文鏈接：

如下格式的json如何導入mysql數據庫？我要代碼，謝謝各位大佬了

解析json字符串，定義一個dto對象，字段名和類型和json里面一致，然后用jdbc連接數據庫，insert into插入表中，代碼自己寫吧，沒時間幫你寫

怎么在mysql中放入json數據

我們知道，JSON是一種輕量級的數據交互的格式，大部分NO SQL數據庫的存儲都用JSON。MySQL從5.7開始支持JSON格式的數據存儲，并且新增了很多JSON相關函數。MySQL 8.0 又帶來了一個新的把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 (0.00 sec)

第一級：

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

第二級：

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 來轉換 @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)

舉例二

再來一個復雜點的例子，用的是EXPLAIN 的JSON結果集。

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" ? ? ?] ? ?} ?}}';

第一級：

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

第二級：

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)

第三級：

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)

第四級：

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)

那我們把這個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 | 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)

當然，JSON_table 函數還有其他的用法，我這里不一一列舉了，詳細的參考手冊。

請點擊輸入圖片描述

MySQL JSON類型

MySQL支持JSON數據類型。相比于Json格式的字符串類型，JSON數據類型的優勢有：

存儲在JSON列中的任何JSON文檔的大小都受系統變量 max_allowed_packet 的值的限制，可以使用 JSON_STORAGE_SIZE() 函數獲得存儲JSON文檔所需的空間。

在MySQL8.0中，優化器可以執行JSON列的局部就地更新，而不用刪除舊文檔再將整個新文檔寫入該列。局部更新的條件：

JSON數組包含在 字符 [ 和 ] 字符中，其中為一個由逗號分隔的值列表：

JSON對象包含在字符 { 和 } 字符中，其中為一組由逗號分隔的鍵值對，鍵必須是字符串：

在JSON數組和JSON對象的值中允許嵌套：

下例中向創建一個只有一個JSON列的表格 t_json ，并向其中添加JSON值：

若添加的值為非JSON格式，則報錯：

查看 t_json :

如果傳入的參數不能組成鍵值對，則報錯：

因此我們也可以使用以上三種方法向表中添加JSON值，可以一定程度地避免輸入格式錯誤：

解析字符串并發現字符串是有效的JSON文檔時，它在被解析時也會被規范化。對于重復的鍵（ key ）,后面的值（ value ）會覆蓋前面的值。如下：

這種“覆蓋”在向JSON列添加值時也會發生。

在MySQL8.0.3之前的版本中，與此相反，對于被重復的鍵，它的第一個值會被保留，后添加的值則會被拋棄。

MySQL8.0.3及更高版本中，有兩種合并函數： JSON_MERGE_PRESERVE() 和 JSON_MERGE_PATCH() 。下面具討論它們的區別。

合并數組時， JSON_MERGE_PRESERVE 只保留最后傳入的數組參數，而 JSON_MERGE_PRESERVE 則按傳入順序將數組參數連接。

合并對象時，對于重復鍵， JSON_MERGE_PRESERVE 只保留最后傳入的鍵值，而 JSON_MERGE_PRESERVE 重復鍵的所有值保留為數組。

在了解搜索和修改JSON值之前，先來看看JSON的路徑語法。

JSON_EXTRACT 提取JSON值，直接看例子：

JSON_REPLACE 與 JSON_SET 的區別：

JSON_INSERT 和 JSON_SET :

JSON_REMOVE :

可以使用 = ， ， = ， ， = ， ， != ，和 = 對JSON值進行比較。

JSON值的比較先比較值的類型。如果類型不同，則直接 返回類型的優先級的比較結果；如果類型相同，再進行值的內容的比較。

OPAQUE 值是不屬于其他類型的值。

轉換規則為：