mysql怎么通过outfile恢复_mysql select into outfile 和 load data local infile备份还原

在 my.ini 的  [mysqld] 节点增加如下配置：

secure-file-priv="D:/mysql_secure_file"max_allowed_packet=1000M

备份

select IdentityID,IdentityNO,IdentityName,ParentID,Remark,PNum,Validity from sys_identity into outfile 'd:/mysql_secure_file/t_zone.csv' fields terminated by ',' optionally enclosed by '"';

还原

load data local infile 'd:/mysql_secure_file/t_zone.csv' into table t_zone fields terminated by','optionally enclosed by '"' escaped by '"' (zone_id,zone_no,zone_name,parent_id,`explain`,person_num,validity);

备份表结构

CREATE TABLE `sys_identity` (

`Id`int(11) NOT NULL AUTO_INCREMENT COMMENT '对象Id',

`IdentityID`int(11) NOT NULL COMMENT '区县编号',

`IdentityNO` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '区县代码',

`IdentityName` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '区县名称',

`ParentID`int(11) NULL DEFAULT NULL COMMENT '区县父ID',

`Remark` blob NULL COMMENT'描述',

`Validity`char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '1' COMMENT '是否可用,1-可用0-不可用(删除标记)',

`PNum`int(11) NULL DEFAULT 20 COMMENT '单位考核人数',

PRIMARY KEY (`Id`) USING BTREE

) ENGINE= InnoDB AUTO_INCREMENT = 137 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

还原表结构

CREATE TABLE `t_zone` (

`zone_id`char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '区域id',

`zone_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '区县代码',

`zone_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '区县名称',

`parent_id`char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '区县父id',

`explain` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '描述',

`person_num`int(11) NULL DEFAULT 0 COMMENT '区域考核人数',

`validity`char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '0' COMMENT '是否可用(0:是 1:否)',

`create_user`char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '创建人',

`create_date` timestamp(0) NULL DEFAULT NULL COMMENT '创建时间',

`update_user`char(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '修改人',

`update_date` timestamp(0) NULL DEFAULT NULL COMMENT '修改时间',

PRIMARY KEY (`zone_id`) USING BTREE

) ENGINE= InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

授权

update mysql.user set File_priv='Y' where user='root';