mysql+数据导出+导入,mysql数据导入导出

数据导入

语法说明：

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

[REPLACE | IGNORE]

INTO TABLE tbl_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,...]

mysql默认是以一个tab键做为分隔符，而一般情况下我们习惯用|，所以可以这么来导入数据：

mysql> load data infile '~/test.txt' into table subscriber;

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

咦，执行报错了，百度搜索错误信息，得知：导入导出数据必须在系统变量secure-file-priv指定的目录操作，如果变量为空，则不允许导入导出。把导入文件复制到要求的目录，重新导入成功：

mysql> show variables like 'secure%'

-> ;

+------------------+-----------------------+

| Variable_name | Value |

+------------------+-----------------------+

| secure_auth | OFF |

| secure_file_priv | /var/lib/mysql-files/ |

+------------------+-----------------------+

2 rows in set (0.00 sec)

mysql> load data infile '/var/lib/mysql-files/test.txt' into table subscriber fields terminated by '|';

Query OK, 4 rows affected (0.06 sec)

Records: 4 Deleted: 0 Skipped: 0 Warnings: 0

数据导出

select * into outfile filename fields terminate by '|' from user;

mysql> select * into outfile '/var/lib/mysql-files/user.txt' fields terminated by '|' from user;

Query OK, 7 rows affected (0.00 sec)