???????????????(import)???????????????????????????????????????????????????????е?ETL (Extract?? transform?? load)??L (Load)??????????????????(structure)??????(format)????????????????(?????????????????????MySQL)??
???????????????????????η???????????(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????????????????????????к???????з????????????????????????У????????????????????????
????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 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???????e????????(????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???????????????????