mongo數(shù)據(jù)庫(kù)導(dǎo)入mysql數(shù)據(jù)庫(kù)

最近項(xiàng)目做了個(gè)定時(shí)器，由于之前是python做的，最近重構(gòu)成了java，感覺(jué)mysql還是比mongo符合我們的項(xiàng)目管理要求，重構(gòu)之后需要把以前的mongo數(shù)據(jù)導(dǎo)入到mysql記錄如下

進(jìn)入自己服務(wù)器（mongo所在的服務(wù)器）：
/usr/bin/mongoexport -h 127.0.0.1 -u root -p shen@9527 -d TimerService -c timer_point -f _id,timer_status,timer_time,timer_data,timer_action,timer_object_id,timer_type --type=csv -o /var/share/TimerPoint.csv

/usr/bin/mongoexport 可能和我的位置不一樣自行調(diào)整

-h #是記錄本機(jī)（mongo）ip -u #mongo用戶名 -p #mongo密碼 -d #數(shù)據(jù)庫(kù)名稱 -c #集合名稱 -f #導(dǎo)出的字段 --type #導(dǎo)出的數(shù)據(jù)文件類型 -o #導(dǎo)出的文件路徑

導(dǎo)出之后文件格式如下

_id,timer_status,timer_time,timer_data,timer_action,timer_object_id,timer_type 0,528202844,"{'flag': 4, 'userId': 'd5b107dd-ccad-43ff-a0f0-8421c73aea62', 'requestId': 1192, 'taskStatus': 1}",0,528202724,1 0,527696000,"{'flag': 3, 'taskId': '76f84e0a-579c-435e-a98b-83faa2a46842'}",0,528173978,1 0,528202958,{'userId': 'd5b107dd-ccad-43ff-a0f0-8421c73aea62'},1,528202778,1 0,524470400,"{'taskId': 'c0d12a8c-1426-4ba6-9b3c-d29b76cb140d', 'flag': 3}",0,528175961,1 0,528204941,{'userId': 'd5b107dd-ccad-43ff-a0f0-8421c73aea62'},1,528204761,1 0,524470400,"{'taskId': 'dc240521-957f-4ef2-90be-ea76eb5411dc', 'flag': 3}",0,528176647,1 0,535981446,{'userId': 'd5b107dd-ccad-43ff-a0f0-8421c73aea62'},1,528205446,1 0,524470400,"{'taskId': 'a6e028ae-2065-4516-bc18-8865b8a90437', 'flag': 3}",0,528177282,1 0,528265940,"{'taskStatus': 1, 'flag': 4, 'userId': 'd5b107dd-ccad-43ff-a0f0-8421c73aea62', 'requestId': 1195}",0,528265820,1 O0,531148400,"{'flag': 3, 'taskId': '724cbe1d-3cc3-4483-93ee-590c18fbe907'}",0,528657686,1 0,531148400,"{'flag': 3, 'taskId': '75434d01-b13d-4c3e-8226-f1aa8a888092'}",0,528677908,1 0,540955336,"{'flag': 3, 'taskId': 'fe885ad1-682c-4593-aa34-94776cc893f5'}",0,529627052,1 0,545967065,"{'flag': 3, 'taskId': '5a7dd4ec-1b3b-4124-bc93-e522916f9a73'}",0,529627105,1

接下來(lái)是導(dǎo)入mysql工作了，
進(jìn)入mysql shell界面執(zhí)行如下命令：

load data infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\TimerPoint.csv' into table `timer_point_server` fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' ignore 1 lines;

如果指定列，查看官網(wǎng)語(yǔ)法：

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'[REPLACE | IGNORE]INTO TABLE tbl_name[CHARACTER SET charset_name][{FIELDS | COLUMNS}[TERMINATED BY 'string'][[OPTIONALLY] ENCLOSED BY 'char'][ESCAPED BY 'char']][LINES[STARTING BY 'string'][TERMINATED BY 'string']][IGNORE number {LINES | ROWS}][(col_name_or_user_var[, col_name_or_user_var] ...)][SET col_name={expr | DEFAULT},[, col_name={expr | DEFAULT}] ...]

我的是：

load data infile 'C:\\ProgramData\\MySQL\\MySQL Server 5.7\\Uploads\\TimerPoint.csv' into table `timer_point_server` fields terminated by ',' optionally enclosed by '"' (timer_status,timer_time,timer_data,timer_action,timer_object_id,timer_type) lines terminated by '\n' ignore 1 lines;

中間可能遇到的問(wèn)題有：
too many positional arguments:檢查拼寫(xiě)

The MySQL server is running with the --secure-file-priv option so it cannot execute this statement:執(zhí)行 show global variables like '%secure%';查看文件路徑，將csv拷貝到下面去

里面字段如果有引號(hào)的需要執(zhí)行語(yǔ)句

show variables like 'sql_mode'; set sql_mode='';

更多的需要看官網(wǎng)解釋 load data

轉(zhuǎn)載請(qǐng)說(shuō)明出處： https://www.cnblogs.com/ontoweb-zp/p/9260050.html

轉(zhuǎn)載于:https://www.cnblogs.com/ontoweb-zp/p/9260050.html

總結(jié)

以上是生活随笔為你收集整理的mongo数据库数据迁移到muysql数据库的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。