MySQL数据库的导入与导出

MySQL数据库的导入与导出

使用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 INFILESELECT 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"
打赏

订阅评论
提醒
guest

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

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

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

MySQL数据库的导入与导出