MySQL数据库的导入与导出

MySQL数据库的导入与导出

Posted at Last edit Comments 0 Comment211 Views0 Likes

使用mysqldump导出数据

完整导出格式

会导出表结构+表中的数据

mysqldump -h<主机名> -P<端口> -u<用户名> -p<密码> --default-character-set=<字符集> --databases <数据库名> --tables <表名1> <表名2> …… <表名n> --where="<where条件语句>" > /path/to/exported.sql

# 举例
mysqldump -h127.0.0.1 -P3306 -uroot -p123 --default-character-set=utf8mb4 --databases wordpress --tables wp_posts --where="id<50 AND post_status='publish'" > ./wordpress.sql
  • -h mysql服务器所在的服务器ip，一般为lcoalhost(或127.0.0.1)，因为mysql一般不会允许远程连接(远程都是通ssh映射到127.0.0.1)；
  • -P 大写P，表示Port，mysql服务器端口；
  • -u 表示User，mysql服务器用户名；
  • -p 小写p，表示password，mysql服务器密码；
  • --default-character-set=<字符集> 指定导出的数据编码(现在基本上统一用utfmb4)；
  • <数据库名> 要导出的数据库名称；
  • <表名1> <表名2> …… <表名n> 要导出的表名，多张表用空格隔开(这些表必须是前面指定的数据库中的表)；
  • > 输出重定向符号，表示把mysqldump输出的内容全部重定向到/path/to/exported.sql文件中。

简略导出格式

省略一些参数

mysqldump -P<端口> -u<用户名> -p <数据库名> <表名1> <表名2> …… <表名n> > /path/to/exported.sql

# 举例
mysqldump -P3306 -uroot -p wordpress wp_posts wp_postmeta  > ./wordpress.sql
  • 省略-h：一般来说，mysql服务器都会设置成只允许本机访问，所以我们导出语句都需要登录到服务器上执行，所以不需要指定-h，它默认就是localhost；
  • 省略-p后面的密码：注意这是小写的p，-p后面不写密码，则执行命令后会提示你输入密码，这样比较安全，否则密码会被保存在命令执行日志中；
  • --default-character-set=<字符集>：字符集现在基本上都是utf8mb4，没必要去特意指定；
  • 省略--databases和--tables：默认第一个为数据库名，而后面的都为该库中的表名；
  • 省略--where：一般都是导所有数据比较多，所以大多数时候不会用--where；

省略表名和端口

# 如果导出整个数据库，那表名也可以省略
mysqldump -P<端口> -u<用户名> -p <数据库名> > /path/to/exported.sql
# 举例
mysqldump -P3306 -uroot -p wordpress > ./wordpress.sql

# 如果数据库是默认端口，则端口也可以省略
mysqldump -u<用户名> -p <数据库名> > /path/to/exported.sql
# 举例
mysqldump -uroot -p wordpress > ./wordpress.sql

同时导出多个库

如果用了--databases但没有用--tables，则--databases后面的参数都表示数据库名，表示同时导出多个库(这种情况一般很少用)

mysqldump -u<用户名> -p --databases <数据库名1> <数据库名2> > /path/to/exported.sql

# 举例
mysqldump -uroot -p --databases wordpress chuntu > ./wordpress+chuntu.sql

不省略–tables

省略--databases但不省略--tables，一眼就可以看出--tables前面是库名，后面是表名

mysqldump -u<用户名> -p <数据库名> --tables <表名1> <表名2> …… <表名n> > /path/to/exported.sql

# 举例
mysqldump -P3306 -uroot -p wordpress --tables wp_posts wp_postmeta  > ./wordpress.sql

只导出表结构

如果你只想导出表结构，不想导出数据，可以加个--no-data(可简写为-d)

mysqldump -u<用户名> -p <数据库名> --tables <表名1> <表名2> …… <表名n> --no-data > /path/to/exported.sql

# 举例
mysqldump -P3306 -uroot -p wordpress --tables wp_posts wp_postmeta --no-data  > ./wordpress.sql

只导出表数据

如果你只想导出表数据，不想导出表结构，可以加个--no-create-info(可简写为-t)

mysqldump -u<用户名> -p <数据库名> --tables <表名1> <表名2> …… <表名n> --no-create-info > /path/to/exported.sql

# 举例
mysqldump -P3306 -uroot -p wordpress --tables wp_posts wp_postmeta --no-create-info  > ./wordpress.sql

导出的sql中的注释

MySQL中有三种注释符号(见9.7 Comments)

-- 这是注释(推荐)

# 这是注释

/* 
这也是注释1
这也是注释2
*/

但有一种注释是这样的

/*!<数字> <语句> */

实例

/*!40000 ALTER TABLE `wp_posts` DISABLE KEYS */;

那么这种注释看上去是注释，但其实MySQL是会执行它的，感叹号后面的数字表示该语句在哪个版本的mysql上执行，读的时候是4.00.00这么读的，这个都是0不好区分，换一个，比如40103，就读作4.01.03(当然0开头一般不会被读出来，所以一般读作4.1.3)。

这种是mysql的一个特殊处理，也就是它从sql上来说，就是注释，但mysql会特别的去处理这个注释，这样这个sql在mysql上导入的话，这个“注释的语句”就会被运行，但是在其它非mysql中导入的话，这个注释就会被当成真正的注释。

导出的时候的锁表问题

Myisam导出锁表

在mysqldump文档中搜索–lock-tables，可以找到以下一段描述

Some options, such as –opt, automatically enable –lock-tables. If you want to override this, use –skip-lock-tables at the end of the option list.
翻译：有些选项，例如–opt，会自动启用–lock-tables。如果你想覆盖这个选项(即不想启用)，可以使用位于选项列表最后的–skip-lock-tables选项来覆盖。

我们再搜索–opt选项，可以看到该选项其实是以下所有选项(包括–lock-tables)的组合，并且–opt选项是被默认启用的(意思就是以下所有选项其实都是被默认启用了的)

--add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset

通过以上分析，可以得知，我们前面所说的所有mysqldump导出数据库的命令，其实都是默认启用了–lock-tables选项的，该选项是用于添加“READ LOCAL”锁的。

具体来说，就是在执行导出命令后，你指定的那个要导出的库中的所有表，都会被加上“READ LOCAL”锁，这个锁允许其它客户端连接在你导出数据过程中并发插入数据(因为同一时间可能有多个客户端访问数据库)，但不允许修改和删除数据(详见Table Lock Acquisition)，导出的数据将会是上锁那一刻表中的数据(因为在导出的过程中，表中的数据可能会不断的增加)。

总结：导出Myisam引擎表的时候，是会锁表的，我们只要知道这个就好，导出语句还是前面说的那些导出语句，不需要增加任何选项，因为锁表选项是默认启用的，当然你要显式启用也可以。

mysqldump导出数据库时显示启用锁表

mysqldump -uroot -p wordpress --lock-tables > ./wordpress.sql

InnoDB设置不锁表

以上锁表的情况，是对Myisam引擎的表来说的，如果你是Innodb引擎的表，虽然你也可以用以上方法锁表，但完全没必要，因为Innodb表可以执行事务，通过添加–single-transaction选项，即可在不锁表的情况下，保证你导出的数据就是你执行导出命令那一刻的数据(即在导出的过程中增加的数据是不会被导出的)。

–single-transaction选项会把事务隔离级别设置为REPEATABLE READ(即可重复读模式)，可重复读的意思是，我第一次读，跟我重复读一次，得到的结果是一致的，不会出现第一次读跟第二次读结果不一样的情况，虽然可能这个读取数据(即导出数据)的过程中，一直有新记录在插入，但可重复读模式能保证mysqldump读取的数据就是执行该命令那一刻的数据，后面新插入的数据不会被读取，但要注意，它只能保证一个库里的所有表的数据一致性，不能保证两个数据库之间的数据一致性，不过我想应该也比较少人会同时导出多个库吧。

另外–single-transaction一般要配合–quick一起使用，–quick选项用于快速导出，它会强制要求mysqldump一次只获取一行数据(没有该选项的话，mysqldump会把获取到的行放到内存中，获取一批后，再一起输出)，不过其实我们不用手动添加–quick选项，因为它也在–opt选项里，所以它默认就是被启用了的。

mysqldump -u<用户名> -p <数据库名> --tables <表名1> <表名2> …… <表名n>  > /path/to/exported.sql

# 举例：只需要加入--single-transaction就行
mysqldump -P3306 -uroot -p wordpress --tables wp_posts wp_postmeta --single-transaction > ./wordpress.sql

导入数据

使用mysql命令导入

使用mysql把mysqldump导出的文件导入到数据库中

mysql -h<主机名> -u<用户名> -p <数据库名> < /path/to/xxx.sql

# 举例
mysql -hlocalhost -uroot -p wordpress < ~/MyDocuments/wordpress-sql-backup/wordpress_2022-05-23.sql
  • -hlocalhost：由于localhost表示本机，所以这个选项其实是可以省略不写的；
  • 记忆技巧：只需要把mysqldump导出语句中的mysqldump命令改成mysql命令，再把>符号改成<，输入密码后即会开始导入；
  • 导入成功后不会有任何输出(Linux准则：没有消息就是好消息，因为坏消息一定会显示报错)；
  • 这是官方文档：7.4.2 Reloading SQL-Format Backups。

不指定数据库：当被导入的sql中包含创建数据库语句及use数据库语句时，不需要指定数据库

mysql -h<主机名> -u<用户名> -p < /path/to/xxx.sql

# 举例
mysql -hlocalhost -uroot -p < ~/MyDocuments/wordpress-sql-backup/wordpress_2022-05-23.sql

特别注意：很多文章说mysqldump也可以导入数据，说只要把>改成<就是导入，但我多次尝试，都是无法导入的，官方文档也没有说mysqldump可以导入数据，所以我觉得网上很多人都是抄来抄去，写错了而已。

使用source命令导入

source命令是mysql命令，需要先登录数据库，然后再使用use <库名>命令选择数据库，然后使用以下命令即可把数据导入到当前数据库中

source /path/to/xxx.sql;

实例显示source导入数据

use wordpress;
mysql> source /path/to/wordpress.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL [wordpress]>

使用SELECT…INTO导出表

前面说的mysqldump既可以导出库，也可以导出单张或多张表，但SELECT … INTO一般只用来导出单张表，本质上来说，SELECT … INTO并不是导出表，而是把SELECT查询到的结果导出到一批变量中，或者导出到一个文件中，导出到变量中一般用于写存储过程，我们这里不介绍导出到变量，只介绍导出到文件中。

什么是csv文件？

在讲SELECT … INTO导出表之前，我们讲一下什么是csv文件，因为我们需要用SELECT … INTO导出表csv文件中。

我想很多人应该都见过或者使用过csv文件，我们导出文件也经常导出这种格式的文件，一般这种文件是用Excel来打开的，csv其实是Comma separated value的缩写，意思是“逗号分隔的值”，没错，其实csv文件就是一个纯文本文件，它只不过是一行一行的逗号分隔开的值。

假设有如下表格

id name sex age
1 zhangsan 1 22
2 lisi 0 18
3 wangwu 1 21

把以上表格的数据写成以下格式，并存成到一个csv文件中，你双击打开，就可以用Excel打开它

1,"zhangsan",1,"22"
2,"lisi",0,"18"
3,"wangwu",1,"21"

导出到文件前提条件

SELECT … INTO导出数据到文件，需要设置secure_file_priv变量，否则执行会报这个错

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

在设置之前，我们先用以下sql语句查询一下它的值，如下所示，一般默认都是“NULL”

mysql> SHOW VARIABLES LIKE "secure_file_priv"
    -> ;
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| secure_file_priv | NULL  |
+------------------+-------+
1 row in set (0.01 sec)

secure_file_priv可以有三个值

  • secure_file_priv="" 即空字符串，这样表示支持任意目录的导入与导出；
  • secure_file_priv="/tmp/" 只能从/tmp/目录上导入与导出；
  • secure_file_priv=NULL 空值，如果你不设置，它默认就是NULL，这样表示禁止使用SELECT INTO导出文件以及禁止LOAD DATA导入文件。

现在我们来设置secure_file_priv变量的值，我们要在my.cnf(Win叫my.ini)中的[mysqld]组设置

[mysqld]
secure_file_priv=""

为了方便，我们需要从任意目录导入与导出文件，所以我们把它的值设置为空，然后重启mysql服务器(不同系统重启方法不一样，这里就不说怎么重启了)。

SELECT INTO OUTFILE

以下是把一个SELECT语句查询结果使用INTO OUTFILE导出到指定的文件中

SELECT * FROM wp_options ORDER BY option_id ASC LIMIT 5
INTO OUTFILE '~/Downloads/wp_options.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY "\n";
  • 第一句SELECT * FROM …… 就是正常的SELECT语句，只要按SELECT语句的语法写就可以；
  • INTO OUTFILE /path/to/xxx.csv 用于把SELECT输出的结果输出到指定的csv文件中(当然你也可以写成.txt格式，因为.csv和.txt内容都是完全一样的，只不过.csv一般会默认用Excel打开而已)；
  • FIELDS TERMINATED BY ',' 设置用逗号作为列的终止符(其实就是设置列分隔符)；
  • OPTIONALLY ENCLOSED BY '"' 注意这个也属于FIELDS的属性，只不过一行太长，我分开来解释而已，该选项用于设置用选择性的用双引号包住输出内容(之所以是“选择性的”，是因为数字类型不用双引号包围，而字符串类型则需要)；
  • ESCAPED BY '\\' 注意这个也属于FIELDS的属性，表示使用反斜杠转义(部分符号可能需要转义，这个看情况，如果没有需要转义的，可以不写)，注意，这句不能放在LINES TERMINATED BY后面，因为它其实是属于Fields关键字下边的，而LINES是另一个关键字；
  • LINES TERMINATED BY "\n" 这个就不是FIELDS属性了，而是LINES属性，用于设置用”\n”作为行终止符(其实就是设置换行符)，官网中用的是单引号括住，但是用单引号括住的字符串其实是不会被解析的，所以如果要用单引号括住，你必须双写反斜杠(即\\n)。
  • 以上语句其实是要写成一行的，只不过我为了讲解方便，换行写了而已(在mysql命令行客户端中不写分号换行，是不会执行语句的，也是为了方便格式化，因为全部写成一行可能不好检查有没有什么漏写了)；
  • 如果导出的文件已经存在，它是不会替换的，而是会提示：ERROR 1086 (HY000): File ‘~/Downloads/wp_options.csv’ already exists，即提示文件已存在。

把要导出的数据写在语句中

如下所示，我们直接用数据构建“虚拟表”

(VALUES ROW(1,"zhangsan",1,"22"),ROW(2,"lisi",0,"18"),ROW(3,"wangwu",1,"21")) AS t

这样就不需要真正具有一张表也能用来测试INTO OUTFILE功能，经常用来暂时性的测试用

SELECT * FROM (VALUES ROW(1,"zhangsan",1,"22"),ROW(2,"lisi",0,"18"),ROW(3,"wangwu",1,"21")) AS t
INTO OUTFILE '~/Downloads/wp_options.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY "\n";

使用TABLE代替SELECT * FROM

如果你要导出一个表的所有字段，那么可以不写SELECT * FROM，而是把它换成TABLE就行

TABLE wp_options ORDER BY option_id ASC LIMIT 2
INTO OUTFILE '~/Downloads/wp_options.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY "\n";

SELECT INTO DUMPFILE

DUMPFILE与INTOFILE的区别是，DUMPFILE是被设计来用于导出一个blob类型字段中的内容的，虽然你可以用它导出多个字段，也可以导出非blob类型字段，但由于DUMPFILE不能设置列分隔符，行分隔符等等，如果你导出多个字段，则你所有的字段内容都会连在一起，没有任何分隔，也不能换行，所以这样做没有意义。

DUMPFILE的意义，就在于把一个blob类型字段的内容输出到一个文件中，比如一张图片转换成base64编码就能存到blob类型的字段中，那么我现在想把这张图片的base64编码输出到一个文件中，就可以用这种方法。

具有用法

SELECT <字段> FROM <表名> LIMIT 1
INTO DUMPFILE '~/Downloads/wp_options.txt'

注意：由于DUMPFILE的特殊作用，我们SELECT时一定要用limit 1限制输出一行，如果不限制，它也只能输出两行(亲测)，并且这两行是完全连在一起的，这样的输出结果对我们没有任何意义。

LOAD DATA INFILE导入数据

LOAD DATA INFILE与SELECT INTO OUTFILE是一对，SELECT INTO OUTFILE导出的数据，必须要用LOAD DATA INFILE才能导入。

导入数据

LOAD DATA INFILE "~/Downloads/wp_options.csv"
IGNORE INTO TABLE <库名>.<表名>
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'
LINES TERMINATED BY "\n";
  • LOAD DATA INFILE 指定从哪个文件中加载数据；
  • IGNORE INTO TABLE IGNORE表示遇到相同的唯一索引时，跳过该记录；
  • FIELDS和LINES两条，主要是确定列分隔符和换行符，具体请看SELECT INTO OUTFILE。

IGNORE和REPLACE

如果把IGNORE改为REPLACE，表示有相同的唯一索引时，替换原记录

LOAD DATA INFILE "~/Downloads/wp_options.csv"
REPLACE INTO TABLE <库名>.<表名>
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY "\n";

注：相同唯一索引，就是比如某个字段有唯一索引(主键也算)，然后有一条记录这个字段的值为1，而你要导入的数据中这个字段也是1，那就重复了，如果插入这条记录，就会有两条记录该字段值都是1，但由于它有唯一索引，所以是根本不允许插入的，但是却可以选择忽略或替换，这就要靠IGNORE(忽略，即跳过)和REPLACE(替换)来确定了。

跳过开头n行

IGNORE 5 LINES表示跳过开头5行，从第6行开始导入

LOAD DATA INFILE "~/Downloads/wp_options.csv"
REPLACE INTO TABLE <库名>.<表名>
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY "\n";
IGNORE 5 LINES;

指定行开始符

使用STARTING BY 'xxx'来指定行开始符

LOAD DATA INFILE "~/Downloads/wp_options.csv"
REPLACE INTO TABLE <库名>.<表名>
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY "\n" STARTING BY 'xxx';

比如以下文件

xxx1,"zhangsan",1,"22"
abc,123,465,xxx2,"lisi",0,"18"
3,"wangwu",1,"21"
  • 第一行识别到xxx，所以它会把xxx后面的都当成一行；
  • 第二行也有xxx，它会把xxx后面的当成一行，而xxx前面的内容不会被使用；
  • 第三行没有xxx开头，不会被算作一行(即会被忽略掉)，不会被插入。

所以以上内容最终会被识别为以下内容并插入

1,"zhangsan",1,"22"
2,"lisi",0,"18"
扫一扫打赏
觉得文章对你有用的话鼓励一下我吧
打赏
Mysql

©版权声明

本文章由博主『Bruce』撰写，采用『CC BY-NC-SA 4.0(创作共用许可协议4.0)』授权发布，转载请以链接形式标明本文地址。
订阅评论
提醒
guest

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据。

0 评论
内联反馈
查看所有评论
0
希望看到您的想法，请您发表评论x

扫码在手机查看
iPhone请用自带相机扫
安卓用UC/QQ浏览器扫

MySQL数据库的导入与导出