MySQL表级别数据导入导出：5 分钟掌握高效操作技巧！

最新推荐文章于 2025-03-28 16:43:37 发布

IT成长日记

最新推荐文章于 2025-03-28 16:43:37 发布

阅读量881

点赞数 12

分类专栏： MySQL实战探索之旅文章标签： mysql 数据库表导出导入小技巧

本文链接：https://blog.youkuaiyun.com/qq_43715111/article/details/146137450

版权

MySQL实战探索之旅专栏收录该内容

26 篇文章

订阅专栏

在 MySQL 中，除了库级别的备份操作，表级别的数据导出和导入是日常运维和开发中常见的操作。以下是一些实用的小技巧，帮助你高效地完成表数据的导入和导出！

1. 表数据导出小技巧

1.1 `SELECT ... INTO OUTFILE`

SELECT ... INTO OUTFILE 导出方式是比较轻量的实现方式，可以灵活地实现语句级别的数据查询导出到结果文件中。

SELECT * INTO OUTFILE '/data/mysql/tmp/test.csv'
FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test;

mysql> select * into outfile '/data/mysql/tmp/test.csv'
    -> fields terminated by ',' 
    -> optionally enclosed by '"'
    -> lines terminated by '\n'
    -> from test;
Query OK, 1 row affected (0.01 sec)

mysql>

说明：

文件路径必须是 MySQL 服务器有写权限的路径。
导出的文件格式为 CSV，字段以逗号分隔，行以换行符分隔。

1.2 导出表结构和表数据

要导出表结构和表数据，我们可以使用 mysqldump 来快速实现。

mysqldump -u root -p dbname tablename > /data/mysql/tmp/tmp.sql

[root@node1 tmp]# mysqldump -u root -p mysqldb test > /data/mysql/tmp/tmp.sql
Enter password: 

[root@node1 tmp]# ls -lrht
total 8.0K
-rw-rw-rw- 1 mysql mysql    9 Mar  9 14:36 test.csv
-rw-r--r-- 1 root  root  2.1K Mar  9 14:45 tmp.sql
[root@node1 tmp]#

说明：

导出的文件是 SQL 脚本，包含表结构和数据。

如果需要只导出部分数据，可以通过 --where 选项导出部分数据：

mysqldump -u root -p dbname tablename --where "id=1" > /data/mysql/tmp/tmp1.sql

1.3 命令行 `mysql -e` 导出

如果存在多表关联或者是导出逻辑相对较为复杂的情况，可以使用 --execute 选项来实现（--execute 可以简写为 -e）。

mysql -u root -p -e "SELECT * FROM dbe_name.table_name" > /data/mysql/tmp/tmp.txt

[root@node1 tmp]# mysql -u root -p -e "SELECT * FROM mysqldb.test" > /data/mysql/tmp/tmp.txt
Enter password: 
[root@node1 tmp]# ls -lrht
total 16K
-rw-rw-rw- 1 mysql mysql    9 Mar  9 14:36 test.csv
-rw-r--r-- 1 root  root  2.1K Mar  9 14:45 tmp.sql
-rw-r--r-- 1 root  root  2.1K Mar  9 14:47 tmp1.sql
-rw-r--r-- 1 root  root    15 Mar  9 14:56 tmp.txt
[root@node1 tmp]# more tmp.txt 
id      name
1       test
[root@node1 tmp]#

说明：

导出的文件是纯文本格式，字段以制表符分隔。

1.4 垂直列结果文件导出

实际应用过程中，我们经常会遇到表有很多列的情况，导出查看不方便，可以使用 --vertical 选项来导出垂直列结果文件。

mysql -u root -p --vertical -e "select * from mysqldb.test;" > /data/mysql/tmp/tmp2.txt

[root@node1 tmp]# mysql -u root -p --vertical -e "select * from mysqldb.test;" >/data/mysql/tmp/tmp2.txt
Enter password: 
[root@node1 tmp]# ls -lrht
total 20K
-rw-rw-rw- 1 mysql mysql    9 Mar  9 14:36 test.csv
-rw-r--r-- 1 root  root  2.1K Mar  9 14:45 tmp.sql
-rw-r--r-- 1 root  root  2.1K Mar  9 14:47 tmp1.sql
-rw-r--r-- 1 root  root    15 Mar  9 14:56 tmp.txt
-rw-r--r-- 1 root  root    82 Mar  9 15:02 tmp2.txt
[root@node1 tmp]# more tmp2.txt
*************************** 1. row ***************************
  id: 1
name: test
[root@node1 tmp]#

1.5 导出 XML 格式文件

mysql -u root -p --xml -e "select * from mysqldb.test;" > /data/mysql/tmp/tmp3.xml

[root@node1 tmp]# mysql -u root -p --xml -e "select * from mysqldb.test;" >/data/mysql/tmp/tmp3.xml
Enter password: 
[root@node1 tmp]# ls -lrht
total 24K
-rw-rw-rw- 1 mysql mysql    9 Mar  9 14:36 test.csv
-rw-r--r-- 1 root  root  2.1K Mar  9 14:45 tmp.sql
-rw-r--r-- 1 root  root  2.1K Mar  9 14:47 tmp1.sql
-rw-r--r-- 1 root  root    15 Mar  9 14:56 tmp.txt
-rw-r--r-- 1 root  root    82 Mar  9 15:02 tmp2.txt
-rw-r--r-- 1 root  root   219 Mar  9 15:04 tmp3.xml
[root@node1 tmp]# more tmp3.xml
<?xml version="1.0"?>

<resultset statement="select * from mysqldb.test" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <row>
        <field name="id">1</field>
        <field name="name">test</field>
  </row>
</resultset>
[root@node1 tmp]#

1.6 导出 HTML 格式文件

mysql -u root -p --html -e "select * from mysqldb.test;" > /data/mysql/tmp/tmp4.html

[root@node1 tmp]# mysql -u root -p --html -e "select * from mysqldb.test;" >/data/mysql/tmp/tmp4.html
Enter password: 
[root@node1 tmp]# ls -lrht|grep html
-rw-r--r-- 1 root  root    89 Mar  9 15:06 tmp4.html
[root@node1 tmp]# more tmp4.html 
<TABLE BORDER=1><TR><TH>id</TH><TH>name</TH></TR><TR><TD>1</TD><TD>test</TD></TR></TABLE>
[root@node1 tmp]#

2. 表数据导入小技巧

2.1 `LOAD DATA INFILE` 加载数据文件

在实际的大中型数据规模的迁移中，适合采用 LOAD DATA 的数据文件加载方式，该方式加载速度极快。

load data infile '/data/mysql/tmp/test.csv'
into table mysqldb.test
fields terminated by ',' 
optionally enclosed by '"'
lines terminated by '\n';

mysql> load data infile '/data/mysql/tmp/test.csv'
    -> into table mysqldb.test
    -> fields terminated by ',' 
    -> optionally enclosed by '"'
    -> lines terminated by '\n';
Query OK, 2 rows affected (0.03 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

mysql>

2.2 表结构及表数据导入

主要指的是导入使用 mysqldump 导出的 SQL 文件。

mysqldump -u root -p --single-transaction mysqldb > /data/mysql/tmp/tmp.sql

[root@node1 tmp]# mysqldump -u root -p --single-transaction mysqldb > /data/mysql/tmp/tmp.sql
Enter password: 
[root@node1 tmp]#

说明：

--single-transaction：SQL 文件是通过 mysqldump 导出的，并且包含 LOCK TABLES 语句，可以在导入时使用 --skip-lock-tables 参数跳过锁定表的步骤。

2.3 `mysqlimport` 加载数据文件

mysqlimport 主要是导入 CSV 或文本文件到表中。

mysqlimport -u root -p --fields-terminated-by=',' --lines-terminated-by='\n' mysqldb /data/mysql/tmp/test.csv

[root@node1 tmp]# mysqlimport -u root -p --fields-terminated-by=',' --lines-terminated-by='\n' mysqldb /data/mysql/tmp/test.csv
Enter password: 
mysqldb.test: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
[root@node1 tmp]#

3. 其他使用小技巧

3.1 导入时忽略错误

使用 --force 选项忽略导入的错误。

mysql -u root -p mysqldb < /data/mysql/tmp/tmp.sql --force

3.2 导出时压缩文件

使用管道和 gzip 在导出时压缩文件。

mysqldump -u root -p mysqldb test | gzip > /data/mysql/tmp/test.sql.gz

[root@node1 tmp]# mysqldump -u root -p mysqldb test | gzip > /data/mysql/tmp/test.sql.gz
Enter password: 

[root@node1 tmp]#

3.3 导入压缩文件

使用 zcat 和管道导入压缩文件。

zcat /data/mysql/tmp/test.sql.gz | mysql -u root -p mysqldb

[root@node1 tmp]# zcat /data/mysql/tmp/test.sql.gz | mysql -u root -p mysqldb
Enter password: 

[root@node1 tmp]#

3.4 记录操作日志

MySQL 中，tee 选项是很容易忽略的一个功能，但其却是一个非常有用的工具，可以方便地将 MySQL 命令行中的输出保存到文件中，适合用于记录操作日志或调试。

启用 tee 功能：

mysql> tee /data/mysql/log/outpu_tee.log
Logging to file '/data/mysql/log/outpu_tee.log'

禁用 tee 功能：

mysql> notee
Outfile disabled.

示例：
启用 tee 并执行查询，输出结果会同时显示在屏幕并写入 /data/mysql/log/outpu_tee.log 文件。

mysql> tee /data/mysql/log/outpu_tee.log
Logging to file '/data/mysql/log/outpu_tee.log'
mysql> select * from mysqldb.test;
+----+------+
| id | name |
+----+------+
|  1 | test |
|  2 | test |
|  3 | test |
+----+------+
3 rows in set (0.00 sec)

mysql> exit
Bye
[root@node1 tmp]# cat /data/mysql/log/outpu_tee.log
mysql> 
mysql> notee
mysql> select *from mysqldb.test;
+----+------+
| id | name |
+----+------+
|  1 | test |
|  2 | test |
|  3 | test |
+----+------+
3 rows in set (0.00 sec)

mysql> 
mysql> exit
[root@node1 tmp]#