è¿å å¡«æ£é¼äºä¸ä¸Oracleåmysqlå¹³é¢æä»¶å¯¼å ¥å¯¼åºçä¸äºé®é¢ï¼ç®ååä¸ä¸ªè®°å½ã
常è§çæ以ä¸å ç§æ åµï¼
1ãä»Oracle导åºæ°æ®å°excelï¼csvï¼
2ã导å
¥excelï¼csvï¼æ°æ®å°Oracle
3ãä»Oracle导åºæ°æ®ï¼csvãsqlï¼ï¼å¯¼å
¥å°mysqlï¼è¿ç§»Oracleæ°æ®å°mysqlï¼
4ãä»mysql导åºæ°æ®ä¸ºexcelï¼csvï¼
5ã导å
¥excelï¼csvï¼æ°æ®å°mysql
6ãmysql导åºæ°æ®ï¼å¯¼å
¥å°Oracleï¼è¿ç§»mysqlæ°æ®å°Oracleï¼
7ãä¸åmysqlçæ¬ä¹é´sql导åºå¯¼å
¥
æ ¹æ®ä»¥ä¸å ç§æ åµï¼åå«è¿è¡å¤ç
1ãä»Oracle导åºæ°æ®å°excelï¼csvï¼ å°æ¹éæ°æ®ä½¿ç¨PL/SQL DEVæè Oracle SQL Developer导åºå³å¯ï¼ 对äºå¤§æ¹éçæ°æ®ï¼å¦å¤§å åä¸åç¾ä¸ä»¥ä¸ï¼ä½¿ç¨sqluldr2linux64è¿è¡å¯¼åºï¼ä½æ¯éè¦æ³¨æå符éå串è¡çé®é¢ï¼
å符éï¼éè¿æå®charset=ZHS16GBK
串è¡ï¼ä¸²è¡é®é¢ä¸è¬æ¯å 为表åæ°æ®ä¸åå¨ä¸åé符ç¸åçæ åµï¼é»è®¤åé符æ¯è±æçéå·ï¼,ï¼ï¼ä½æ¯åæ°æ®ä¸åå¨éå·ï¼å æ¤å°±ä¼åºç°ä¸²è¡ï¼è§£å³åæ³å°±æ¯å°è±æçéå·è½¬æ¢ä¸ºå ¶ä»ç¬¦å·ï¼æ¯å¦ä½¿ç¨replaceè¿è¡æ¿æ¢ï¼select col1 A,replace(col1, ',', ) B, col2 C,replace(col2,',','ï¼') D from t17 a ;ï¼
2ãå¯¼å ¥excelï¼csvï¼æ°æ®å°Oracle
è¿é使ç¨çOracleæä¾çå·¥å ·sqlldrï¼éè¦å»ºç«å¥½è¡¨ï¼é 置好æ§å¶æ件ï¼æ§å¶æ件å¦ä¸æ示ï¼
$ more cx-1.ctl
load data
characterset zhs16gbk
infile '01.csv'
into table t_07
fields terminated by ","
optionally enclosed by '"'
trailing nullcols
(
col1,
col2,
col3,
col4,
col5,
col6,
col7,
col8,
col9,
col10 date "yyyy/mm/dd HH24:MI:SS",
col11 date "yyyy/mm/dd HH24:MI:SS"
)
éè¦æ³¨æçæ¯ï¼å¯¹äºæ¶é´åæ¥æå段ï¼è¦å ä¸å¯¹åºçåç±»åï¼å¦åä¼å¯¼å ¥å¤±è´¥
å¯¼å ¥è¯å¥ï¼
sqlldr userid=user/passwd control=import.ctl
3ãä»Oracle导åºæ°æ®ï¼csvãsqlææ¬ï¼ï¼å¯¼å ¥å°mysqlï¼è¿ç§»Oracleæ°æ®å°mysqlï¼
è¿ä¸ªä»½ä¸¤ç§æ åµï¼å¦æOracleæå¨æå¡å¨åmysqlæå¨æå¡å¨å¯ä»¥éä¿¡ï¼é£ä¹ä½¿ç¨dataxæ¯ææ¹ä¾¿çï¼
å¦åå°±éè¦å两æ¥èµ°ï¼é¦å ä»Oracleä¸ä½¿ç¨sqluldr2linux64导åºï¼æ³¨æä¸è¦æå®charset=ZHS16GBK  head=yesï¼çå»ä¸äºéº»ç¦ï¼ç¶å使ç¨mysqlimportæè load dataå¯¼å ¥è¿mysqlï¼éè¦é ç½®secure_file_privï¼ï¼å¦æ没æ对åºçæéï¼ä¹å¯ä»¥ä½¿ç¨è¯¸å¦mysql workbenchæDBeaverï¼æ¨è使ç¨è¯¥å·¥å ·ï¼å¯¼å ¥è¿mysqlï¼æ³¨æï¼å¦ææ¯ä»Oracle导åºçcsvï¼ç¡®ä¿å ¶ç¼ç 为utf-8ï¼å¦åå å«ä¸æçæ°æ®å¯¼è¿å»ä¹åä¼ä¹±ç ï¼ã
æè å°å ¶ç´æ¥å¯¼åºè³åç½æ®µçmysqlä¸ï¼ä½¿ç¨dataxï¼ï¼ç¶åä»mysqlä¸ä½¿ç¨mysqldumpå°å ¶å¯¼åºåå¯¼å ¥å°ç®æ mysqlæ°æ®åºå³å¯ã
4ãä»mysql导åºæ°æ®ä¸ºexcelï¼csvï¼
DBeaveræ¯ä¸ªæ¯è¾å¥½ç¨çå·¥å ·ï¼æµè¯ä¹åè¿ä¸éï¼å¤§æ°æ®é没æåºç°å¡æ»çæ åµï¼
使ç¨mysqldump导åºï¼éè¦é ç½®secure_file_privï¼ï¼
5ãå¯¼å ¥excelï¼csvï¼æ°æ®å°mysql
è¿ç±»å3ä¸ç±»ä¼¼ï¼å¯ä»¥åè3ï¼ä½¿ç¨å®¢æ·ç«¯å·¥å ·DBeaveræè mysqlimportï¼åçåload dataç¸åï¼
6ãmysql导åºæ°æ®ï¼å¯¼å ¥å°Oracleï¼è¿ç§»mysqlæ°æ®å°Oracleï¼
å3类似ï¼å¦æä¹é´å¯ä»¥éä¿¡ï¼é£ä¹é 置使ç¨dataxæ çææ¹ä¾¿ï¼æ éè½å°ï¼çå»ä¸å°æ¶é´ã空é´ã