MySQL数据的导入与导出

标签:数据库|MySQL

数据的导出

参考链接

blog.csdn.net/caoxiaohong…

语法

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]
 
export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

例子

SELECT customer_id, firstname, surname INTO OUTFILE '/exportdata/customers.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM customers;

实测

MySQL要求导入导出文件只能存放在secure_file_priv变量指定的目录(/var/lib/mysql-files/)

-- 3001011
select count(*) from emp;
SHOW VARIABLES LIKE "secure_file_priv";
-- 时间: 9.915s
select * into outfile '/var/lib/mysql-files/emp.txt' from emp;

数据导入

参考链接

www.jianshu.com/p/bcafd8f3a…

本地:(转)LOAD DATA INFILE 导入数据

语法

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

实测

--  32.245s
load data infile '/var/lib/mysql-files/emp.txt' into table empx;
select count(*) from empx;

默认导入导出说明

分隔符\t 换行符\n

大概就长这个样子吧

实用

数据冷备