mysql数据的导出与导入_MySQL数据导出与导入

利用CSV存储引擎加载数据：

mysql>create table ld_csv1(id int not null default ‘0’,username varchar(5) not null,city varchar(6) not null,email varchar(50) not null) engine=csv default charset=UTF8;

$more /tmp/loaddata.txt > /data/mysqldata/3306/data/hugcdb/ld_csv1.CSV

mysql>select * from ld_csv1;

mysqlimport命令行工具导入数据：

mysql>create table ld_cmd(id int not null default ‘0’,username varchar(5) not null,city varchar(6) not null,email varchar(50) not null) engine=innodb default charset=UTF8;

mysqlimport导入数据要求数据文件名与表对象名相同

[mysql@mysql1 data]$ mysqlimport --help

$mysqladmin -usystem -p123456 -S /data/mysqldata/3306/mysql.sock hugc --default-character-set=gbk --fields-terminated-by=’,’ /tmp/ld_cmd.txt

fields-terminated-by

fields-enclosed-by

lines-terminated-by

SQL语句导入数据：

LOAD DATA INFILE ‘file_name’ INTO TABLE tbl_name;

(system@localhost) [mysql]> create table ld_sql(id int not null default ‘0’,username varchar(5) not null,city varchar(6) not null,email varchar(50) not null) engine=innodb default charset=UTF8;

(system@localhost) [mysql]> load data infile ‘/tmp/loaddata.txt’ into table ld_sql charset gbk FIELDS TERMINATED BY ‘,’;

(system@localhost) [mysql]> help load data

Name: 'LOAD DATA'

Description:

Syntax:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'

[REPLACE | IGNORE]

INTO TABLE tbl_name

[PARTITION (partition_name,...)]

[CHARACTER SET charset_name]

[{FIELDS | COLUMNS}

[TERMINATED BY 'string']

[[OPTIONALLY] ENCLOSED BY 'char']

[ESCAPED BY 'char']

]

[LINES

[STARTING BY 'string']

[TERMINATED BY 'string']

]

[IGNORE number {LINES | ROWS}]

[(col_name_or_user_var,...)]

[SET col_name = expr,...]

LOAD DATA INFILE语句中默认的列分隔符是Tab符

通过CHARACTER SET子句，明确指定导入数据的字符集

在客户端本地导入数据

(system@localhost) [mysql]> load data LOCAL infile ‘/tmp/loaddata.txt’ into table ld_sql charset gbk FIELDS TERMINATED BY ‘,’;

如果启动MySQL时禁用了load-infile选项，就不再允许以LOCAL方式导入

如果指定LOCAL关键字，那就是从执行导入语句的客户端上读取文件

没有指定LOCAL关键字，那就是从运行MySQL的服务端上读取文件

如果指定了REPLACE子句，则出现重复值时会替换当前存在的记录

如果指定了IGNORE子句，则插入时遇到重复值会跳过重复的记录

数据文件的前N行不导入

(system@localhost) [mysql]> load data infile ‘/tmp/loaddata.txt’ into table ld_sql charset gbk FIELDS TERMINATED BY ‘,’ ignore 4 lines;

列和行的精确处理

FILES TERMINATED BY

ENCLOSED BY

ESCAPED BY

LINES TERMINATED BY

STARTIING BY

SQL语句导出数据：

select … into outfile

(system@localhost) [mysql]> help select;

Name: 'SELECT'

Description:

Syntax:

SELECT

...

[FROM table_references

[PARTITION partition_list]

[WHERE where_condition]

[GROUP BY {col_name | expr | position}

[ASC | DESC], ... [WITH ROLLUP]]

[HAVING where_condition]

[ORDER BY {col_name | expr | position}

[ASC | DESC], ...]

[LIMIT {[offset,] row_count | row_count OFFSET offset}]

[PROCEDURE procedure_name(argument_list)]

[INTO OUTFILE 'file_name'

[CHARACTER SET charset_name]

export_options

| INTO DUMPFILE 'file_name'

| INTO var_name [, var_name]]

[FOR UPDATE | LOCK IN SHARE MODE]]

(system@localhost) [mysql]> select * from ld_t1 into outfile ‘/tmp/ld_t1_out.txt’ fields terminated by ‘,’ optionally enclosed by ‘”’;