备份语句：SELECT * FROM 表名 INTO OUTFILE '文件路径' CHARACTER SET gbk FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"' LINES TERMINATED BY ' ';
例：SELECT * FROM MT INTO OUTFILE 'MT.csv' CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ';
注：如果程序操作出现异常需要恢复数据：
恢复语句：LOAD DATA INFILE '文件路径' INTO TABLE 表名CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY ' '; 例：LOAD DATA INFILE 'MT.csv ' INTO TABLE MT CHARACTER SET gbk FIELDS TERMINATED BY ',' LINES TERMINATED BY ' '; |
问题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=’’;
还有另外的两种备份的方法，可以参考：https://cloud.tencent.com/developer/article/1343387