备份语句：SELECT * FROM 表名 INTO OUTFILE '文件路径' CHARACTER SET gbk FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
例：SELECT * FROM MT INTO OUTFILE 'MT.csv' CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
注：如果程序操作出现异常需要恢复数据：
|
恢复语句：LOAD DATA INFILE '文件路径' INTO TABLE 表名 CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; 例：LOAD DATA INFILE 'MT.csv ' INTO TABLE MT CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; |
问题1导出时报错：[Err] 1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
解决方法：
执行show variables like 'secure_file_priv'; VALUE为NULL就是禁止备份，修改my.ini，输入secure_file_priv=
问题2导入时报错：[Err] 1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 's_playtime' at row 1 set session sql_mode=’’;
备份语句：SELECT * FROM 表名 INTO OUTFILE '文件路径' CHARACTER SET gbk FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
例：SELECT * FROM MTTASK202012 INTO OUTFILE 'MTTASK202012.csv' CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
注：如果程序操作出现异常需要恢复数据：
|
恢复语句：LOAD DATA INFILE '文件路径' INTO TABLE 表名 CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; 例：LOAD DATA INFILE 'MTTASK202012.csv ' INTO TABLE MTTASK202012 CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n'; |
问题1导出时报错：[Err] 1290 - The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
解决方法：
执行show variables like 'secure_file_priv'; VALUE为NULL就是禁止备份，修改my.ini，输入secure_file_priv=
问题2导入时报错：[Err] 1292 - Incorrect datetime value: '0000-00-00 00:00:00' for column 's_playtime' at row 1 set session sql_mode=’’;