SQL考点之解锁，备份与恢复

最新推荐文章于 2021-08-26 15:50:50 发布

guangod

最新推荐文章于 2021-08-26 15:50:50 发布

阅读量212

点赞数

分类专栏：自学考试 C#知识累积

本文链接：https://blog.csdn.net/guangod/article/details/108699043

版权

自学考试同时被 2 个专栏收录

223 篇文章 82 订阅

订阅专栏

C#知识累积

24 篇文章 3 订阅

订阅专栏

如题:2019年10月：

详见：P168

所以,答案选C.

注：根据计划，遇到这种语法题要深挖些，SQL语句所解决的问题，及实际常用用法。这里涉及到备份与恢复。

关于备份与恢复，没有书上的描述，也是很好理解的。写程序备份到github,当改动较多，不知道改哪里时，就再恢复遍程序。这样避免大量重复的工作，更何况是数据库，有备份与恢复是很正常的。买移动硬盘时，上面都会写着“硬盘有价，数据无价”，可见，数据的价值。

先看下书上列出的考点：

SELECT INTO…OUTFILE

语句把表数据导出到一个文本文件中，并用LOAD DATA …INFILE语句恢复数据。但是这种方法只能导出或导入数据的内容，不包括表的结构，如果表的结构文件损坏，则必须先恢复原来的表的结构。
也可以将查询结果保存在变量中。

1、语法结构：

SELECT *
[INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]

详解：

CHARACTER SET：
数据被转换成CHARACTER SET指定的编码格式输出。如果不指定的话默认为binary，即不做转换，如果指定多个编码格式，输出文件将不能被正确载入。

export_options：
用于语句的exort_options部分的语法包括部分FIELDS和LINES子句，这些子句与LOAD DATA INFILE语句同时使用。

DUMPFILE(dumpfile):翻译为：转存文件

SELECT***INTO DUMPFILE 'file_name'：
将选择的行输出到文件，输出内容没有任何格式，且所有内容都放在一行。

mysql> select * into dumpfile '/tmp/dumpfile.txt' from loadtest;

SELECT***INTO var_name [, var_name]：

将列值输出到变量。into语句可以命名一个或多个变量列表，这些变量可以是用户定义变量，存储过程及函数参数，或者存储程序局部变量。查询应该返回单行装入变量，变量的个数应该和查询的列的个数相同。用户变量对大小写不敏感。

mysql> select * into @a, @b, @c, @d from loadtest limit 1;
mysql> select c1, c2, c3, c4 into @a, @b, @c, @d from loadtest limit 1;

变量的个数和查询的列的个数不相同

mysql> select c1, c2, c3, c4 into @a, @b, @c from loadtest limit 1;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
mysql> select c1, c2, c3 into @a, @b, @c, @d from loadtest limit 1;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

查询的列返回多行

mysql> select c1, c2, c3, c4 into @a, @b, @c, @d from loadtest;
ERROR 1172 (42000): Result consisted of more than one row

2、实际常用用法：

指定格式输出：

mysql> select * into outfile '/tmp/outfile.txt' from loadtest;
mysql> select * into outfile '/tmp/outfile.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '#' LINES TERMINATED BY '\n' from loadtest;

LOAD DATA INFILE

语句以很高的速度从一个文本文件中读取行到一个表中。我们也可以通过mysqlimport工具来导入数据，它本质上是发送 LOAD DATA INFILE语句到服务端。

1、语法结构：

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,...]

详解：

file_name：文件名file_name需要是字符串，在windows下，路径可以为斜杠'/' 或双反斜杠'\\'（windows下验证）。

LOW_PRIORITY：低优先级 CONCURRENT：同时 IGNORE：忽略

注意：导入文件不支持ucs2, utf16, utf16le, 或 utf32编码格式。
LOW_PRIORITY：该参数适用于表锁存储引擎，比如MyISAM, MEMORY, 和 MERGE，在写入过程中如果有客户端程序读表，写入将会延后，直至没有任何客户端程序读表再继续写入。
CONCURRENT：使用该参数，允许在写入过程中其它客户端程序读取表内容。
对复制的影响：使用基于语句格式（statement-based）的复制，在mysql5.5.1之前，CONCURRENT参数不能被复制。使用基于行格式（row-based）的复制,在mysql5.1及其后续版本，都可以被正确复制。mysql5.6认为使用LOAD DATA INFILE是不安全的，当使用statement-based格式的日志时会发出警告，使用mixed格式的日志时会被存储为row格式。
LOCAL：该关键字影响文件的预期读取位置及错误句柄。需要客户端及服务端都开启该选项，如果mysqld的启动参数包含local-infile=0，那么指定该参数是无效的。

对文件查找的影响：
指定该选项：文件将会由客户端程序在客户端主机上查找，然后发送到服务端。可以指定文件的绝对路径和相对路径，指定相对路径的话会在客户端程序目录下查找。
执行导入的时候会先将文件复制到服务端的临时文件夹下（linux下是 /tmp， windows下是C:\WINDOWS\TEMP），如果临时文件夹空间不足将会导致LOAD DATA失败。
未指定该选项：文件将由服务端程序在服务端主机上查找，查找规则如下：
如果给定了一个绝对的路径名称，则服务器使用此路径名称。
如果给定了带有一个或多个引导组件的相对路径名称，则服务器会搜索相对于服务器（数据目录）的文件。
如果给定了一个不带引导组件的文件名称，则服务器会在默认（数据库）的（数据库目录）中寻找文件。
例如：./myfile.txt会在数据库的data目录下查找，myfile.txt会在我们连接时使用的默认数据库，如test的数据目录下查找。
LOCAL对错误句柄的影响：
使用LOAD DATA INFILE，数据解释和主键冲突error会终止操作。
使用LOAD DATA LOCAL INFILE数据解释和主键冲突error会变成warning，操作继续，因为mysql server没有办法在操作过程中结束数据的传输。对于主键冲突error来讲，该方法类似于指定IGNORE。

REPLACE 与 IGNORE：控制输入的行与唯一主键的重复。
REPLACE：输入行替换已经存在的行。即与主键或唯一索引列的重复。
IGNORE：输入行与已经存在的行主键或唯一索引列重复，则丢弃。
假如二者都未指定：行为就会取决于是否指定了LOCAL参数。

PARTITION：MySQL 5.6.2及其后续版本，LOAD DATA INFILE开始支持PARTITION分区选项。对于分区表应采用使用表锁的存储引擎，例如myisam，不适用与行锁的存储引擎，如Innodb，因为LOAD DATA不能移除任何分区锁。

CHARACTER SET：由character_set_database系统变量指示的字符集被用于解释文件中的信息。SET NAMES和character_set_client的设置不会影响对输入的解释。假如文件中的字符编码与默认不一致，最好用CHARACTER SET语句指定编码方式，这就需要我们通过mysqldump或SELECT ... INTO OUTFILE导出文件时通过参数--default-character-set指定导出文件的编码格式。

LINES STARTING BY 'prefix_string'：如果所有希望读入的行都含有一个我们希望忽略的共用前缀，则可以使用'prefix_string'来跳过前缀（以及该前缀
前的所有字符）。如果某行不包括前缀，则整个行被跳过
例如：

mysql> LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test LINES STARTING BY "xxx";

假如/tmp/test.txt文件内容如下

xxx"abc",1
something xxx"def",2
"ghi",3

则我们读入的内容包括("abc",1) 和 ("def",2)，第三行直接被跳过。

IGNORE number LINES：该选项可以被用于在文件的开始处忽略行。例如，我们可以使用IGNORE 1 LINES来跳过一个包含列名称的起始标题行。

LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES;

FIELDS 和 LINES：
对于LOAD DATA INFILE 和 SELECT ... INTO OUTFILE，FIELDS 和 LINES语句的语法都是相同的，二者都是可选的，但同时出现时FIELDS语句要放在LINES语句的前面。
假如我们指定了FIELDS语句，它的子句(TERMINATED BY, [OPTIONALLY] ENCLOSED BY, 和 ESCAPED BY)也是可选的，不过必须指定至少一项。

注：

TERMINATED：终止 ENCLOSED：封闭 ESCAPED ：空
如果FIELDS 和 LINES语句我们都未指定，则默认的句法为：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED BY '\n' STARTING BY ''

所有field-或line-handling选项都可以指定一个空字符串('')。如果字符串不是空的，则FIELDS [OPTIONALLY] ENCLOSED BY和FIELDS ESCAPED BY值必须为单一字符。FIELDS TERMINATED BY, LINES STARTING BY和LINES TERMINATED BY值可以超过一个字符。例如，要编写由回车/换行成对字符作为结尾的行，或读取
包含这类行的文件，则应指定一个LINES TERMINATED BY ‘\r\n’子句。
FIELDS [OPTIONALLY] ENCLOSED BY ：对于SELECT ... INTO OUTFILE输出，如果不包含OPTIONALLY选项，所有的字段将会被ENCLOSED BY指定的字符包裹，例如：

"1","a string","100.20"
"2","a string containing a , comma","102.20"
"3","a string containing a \" quote","102.20"
"4","a string containing a \", quote and comma","102.20"

如果我们指定OPTIONALLY，只有string数据类型（如 CHAR, BINARY, TEXT, 或 ENUM）的字段才会被ENCLOSED BY指定的字符包裹，例如：

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a \" quote",102.20
4,"a string containing a \", quote and comma",102.20

注意，如果在字段值内出现ENCLOSED BY字符，则通过使用ESCAPED BY字符作为前缀，对ENCLOSED BY字符进行转义。另外，要注意，如果指定了一个空ESCAPED BY值，则可能会生成不能被LOAD DATAINFILE正确读取的输出值。例如：

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a " quote",102.20
4,"a string containing a ", quote and comma",102.20

FIELDS ESCAPED BY：用来控制如何对特殊字符进行读写，如上面一个例子，导出和导入时指定FIELDS ESCAPED BY为双引号["]才能被正确的导入，导出的格式如下，对字段内的双引号["]进行了转义。

1,"a string",100.20
2,"a string containing a , comma",102.20
3,"a string containing a #" quote",102.20
4,"a string containing a #", quote and comma",102.20

对于输入：
假如FIELDS ESCAPED BY指定字符非空，则输入时该字符被移除，后续的内容被添加到字段里。一些两个字符的字符串序列且第一个字符是转义字符'\'是例外，如\0,\b,\n,\r,\t,\Z\,\N等,这些字符序列本身有特殊意义。
假如FIELDS ESCAPED BY指定字符为空，将不会发生转义序列的解释。
对于输出：
假如FIELDS ESCAPED BY指定字符非空，字符作为以下输出的前缀。

在特定情况下，field-和line-handling选项相互影响：
如果LINES TERMINATED BY是空字符串，FIELDS TERMINATED BY非空,行以FIELDS TERMINATED BY指定的字符串作为结尾。
假如FIELDS TERMINATED BY 与 FIELDS ENCLOSED BY值均为空('')，将使用固定行（无分割）格式。使用固定行格式，字段之间将没有分隔符（行终止符依然可使用），列字段数据的读取和写入均按照字段定义的宽度去操作，如 TINYINT, SMALLINT, MEDIUMINT, INT, 和 BIGINT, 字段宽度分别为4, 6, 8, 11, 和 20。
不适合使用LOAD DATA INFILE的情况
使用固定行格式（即FIELDS TERMINATED BY 和 FIELDS ENCLOSED BY 均为空）列字段类型为BLOB或TEXT。
指定分隔符与其它选项前缀一样，LOAD DATA INFILE不能对输入做正确的解释。例如：

FIELDS TERMINATED BY '"' ENCLOSED BY '"'

如果FIELDS ESCAPED BY为空，字段值包含FIELDS ENCLOSED BY指定字符，或者LINES TERMINATED BY 的字符在 FIELDS TERMINATED BY 之前，都会导致过早的停止 LOAD DATA INFILE操作。因为LOAD DATA INFILE不能准确的确定行或列的结束。

选择导入的列：

下面的语句会导入文件的所有列

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata;
如果我们想导入表的某些列，需要指定列的列表
LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);
注意：如果sql-mode为严格事物模式STRICT_TRANS_TABLES，导入部分列会报错。
如果输入文件与表的列顺序不同，我们必须指定一个列清单，否则mysql不能把输入文件的字段与表的列匹配起来。

mysql> LOAD DATA INFILE '/tmp/loadtest.txt' INTO TABLE loadtest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '#' LINES TERMINATED BY '\n' (c1, c2, c4, c3);

列的清单可以包含列名或者用户变量，在写入列前我们需要使用SET语句对用户变量进行转换。对set语句及用户变量有如下使用方法：

方法1：在用户变量用于第一列之前，先把第一列的值赋予用户变量，进行除法操作后输入到c1。

mysql> LOAD DATA INFILE '/tmp/loadtest.txt' INTO TABLE loadtest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '#' LINES TERMINATED BY '\n' (@var1, c2, c3, c4) set c1 = @var1/2;

方法2：把c3列设为当前时间（sql-mode使用严格事物模式STRICT_TRANS_TABLES会报错）

mysql> LOAD DATA INFILE '/tmp/loadtest.txt' INTO TABLE loadtest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '#' LINES TERMINATED BY '\n' (c1, c2, c4) set c3 = CURRENT_DATE;

方法3：把输入赋予用户变量，而不把用户变量赋予表中的列，来丢弃此输入值。

mysql> LOAD DATA INFILE '/tmp/loadtest.txt' INTO TABLE loadtest FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '#' LINES TERMINATED BY '\n' (@dummy, c2, @dummy, c4);

通过管道导入数据：

在unix系统中，如果我们想要从管道（pipe）中load data，需要用如下方法：

mkfifo /tmp/ls.dat
chmod 666 /tmp/ls.dat
find / -ls > /tmp/ls.dat &
[root@localhost tmp]# mysql -e "LOAD DATA INFILE '/tmp/ls.dat' INTO TABLE test.tb1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '#' LINES TERMINATED BY '\n'"

注意：sql-mode使用严格事物模式STRICT_TRANS_TABLES会报错
在另一窗口将数据写入管道

[root@localhost /]# cat /tmp/loadtest.txt > /tmp/ls.dat

注：可以先读或者先写管道，谁先谁后都可以，在写入管道的数据被全部读出前，处于阻塞状态。

注意：

LOAD DATA INFILE可以读取外源的文件，如其它数据库或程序生成的逗号分隔的CSV格式的文件。
当我们使用SELECT ... INTO OUTFILE从数据库导出数据到文件，然后通过LOAD DATA INFILE读取该文件到数据库，这两个语句的field和lines选项必须匹配，否则 LOAD DATA INFILE将不能正确的解释文件的内容

guangod

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
SQL考点之解锁，备份与恢复

如题:2019年10月：详见：P168所以,答案选C.注：根据计划，遇到这种语法题要深挖些，SQL语句所解决的问题，及实际常用用法。这里涉及到备份与恢复。关于备份与恢复，没有书上的描述，也是很好理解的。写程序备份到github,当改动较多，不知道改哪里时，就再恢复遍程序。这样避免大量重复的工作，更何况是数据库，有备份与恢复是很正常的。买移动硬盘时，上面都会写着“硬盘有价，数据无价”，可见，数据的价值。先看下书上列出的考点：SELECT INTO…OUTFILE语句把表数据导...
复制链接

扫一扫