-- 导出和导入数据;
1.可用的导出/导入操作类型:
1.使用SELECT...INTO OUTFILE将数据导出到文件;
2.使用LOAD DATA INFILE语句从文件中导入数据;
2.数据导出操作的用途:
1.将数据库从一个服务器复制到另一个服务器:
1.从同一主机;
2.复制到另一主机;
2.使用实际数据测试新MySQL版本;
3.将数据从一个RDBMS传输到另一个RDBMS,异构数据库间同步;
-- 通过同时使用SELECT和INTO OUTFILE来导出数据;
1.将SELECT与INTO OUTFILE一起使用:
1.将结果集直接写入文件;
2.除非另有说明,否则,假定文件路径位于数据库数据目录下:SELECT * INTO OUTFILE '/tmp/City.txt' FROM City;
2.输出文件:
1.写入服务器主机,而不是通过网络写入客户机;文件不能已存在;
2.使用文件系统访问权限进行创建:用户必须要有创建文件的权限,生成的文件属于该用户,文件对所有用户可读;
3.针对语句所选的每一行,都包含对应的一行;默认情况下,列值由制表符分隔,而行在换行符处终止;
补充:
1.从服务器端导出文本文件到服务器:SELECT * FROM tbname INTO OUTFILE '/path';
2.从客户端导出文本到客户端;mysql -e 'SELECT * FROM tbname' > '/path';
-- 使用数据文件格式说明符;
1.默认情况下,将SELECT和INTO OUTFILE一起使用将输出TSV(Tab-Separated Values);
1.“制表符分隔值”文件用制表符分隔列,用换行符分隔记录;
2.CSV(Comma-Separated Values):使用逗号[,]符号分割列,用换行符分隔记录;
2.您可以选择替代的分隔符:
FIELDS
TERMINATED BY 'string'
ENCLOSED BY 'char'
ESCAPED BY 'char'
LINES TERMINATED BY 'string'
– FIELDS子句指定如何显示列:
1.TERMINATED BY指定字段分隔符,默认情况下是制表符;
2.ENCLOSED BY指定如何引住列值,默认设置为不使用引号(即,默认值为空字符串);
3.ESCAPED BY指明当表示换行符或制表符之类的非打印字符时要使用的转义符,默认转义符是反斜杠(\)字符;
– LINES TERMINATED BY子句指定行分隔符,默认情况下是换行符;
-- 转义序列;
1.行终结符说明符:
1.默认为换行符:命令行终结符包括换行符和回车/换行符对;(默认的换行符终结符常见于Linux系统,而回车/换行符对常见于Windows系统)
2.幻灯片中所显示的所有序列单独使用或者在较长的字符串中使用,但\N除外,该序列只有在单独出现时才用作NULL;
2.ESCAPED BY选项:
1.仅控制数据文件中值的输出,它不会更改MySQL解释语句中特殊字符的方式;
2.如果通过写入ESCAPED BY '@'指定数据文件转义符为“@”,并不表示必须使用“@”来转义语句中其他的特殊字符;您必须使用 MySQL的转义符(反斜杠:\)来转义语句中的特殊字符,使用LINES TERMINATED BY '\r\n'(而不是LINES TERMINATED BY '@r@n')之类的语法;
-- 使用LOAD DATA INFILE导入数据;
1.查看帮助:HELP LOAD DATA;
2.说明:
1.将文件中的行值读入表;
2.是SELECT...INTO OUTFILE的逆向操作;
3.使用相似的子句和格式说明符:
LOAD DATA INFILE '/tmp/City.txt' FIELDS TERMINATED BY '\t'
INTO TABLE City;
4.假定文件位于服务器主机上的数据库数据目录中
补充:
1.从服务器端导入文本文件服务器:LOAD DATA INFILE '/path';
2.从客户端导入文本文件到服务器:LOAD DATA LOCAL INFILE '/path';
-- 跳过或变换输入数据;
1.要忽略数据文件中的行,请使用IGNORE n LINES:
1.mysql> LOAD DATA INFILE /tmp/City.txt' INTO TABLE City IGNORE 2 LINES;
2.忽略数据文件的开始部分;当文件以列名行(而不是数据值行)开始时,请使用此子句;
2.要忽略或变换列值,请执行以下操作:
1.在语句列的列表中指定用户变量(而不是列名称);
2.(可选)通过使用SET子句变换列(该子句的语法类似于UPDATE语句中的SET子句):
LOAD DATA INFILE '/tmp/City.txt'
INTO TABLE City ( @skip, @Name, CountryCode, @District, Population)
SET name=CONCAT(@Name,' ',@District);
3.该语句将忽略SET表达式中未使用的变量的值;
4.在将从文件中读取的数据值插入表中之前,LOAD DATA INFILE将对其进行变换,处理用户变量中所包含的值;
eg:
1.创建表;CREATE TABLE City1 LIKE City;
2.修改列长度:ALTER TABLE City MODIFY COLUMN name char(100);
3.导入数据:
LOAD DATA INFILE '/tmp/City1.txt'
INTO TABLE City1 ( @skip, @Name, CountryCode, @District, Population)
SET name=CONCAT(@Name,' ',@District);
4.查看数据:SELECT * FROM City1 LIMIT 10;
-- 重复记录;
1.要控制LOAD DATA INFILE对包含重复的主键或唯一键的行的处理方式,请执行以下操作:
1.使用IGNORE关键字放弃包含重复键的行;
2.使用REPLACE关键字将这些行替换为文件中包含相同键的版本;
2.这与使用INSERT和REPLACE语句控制重复项的方法类似:
1.IGNORE;
2.ON DUPLICATE KEY UPDATE;
3.其重复项处理行为根据数据文件是位于服务器主机上还是位于客户机主机上而稍有不同,所以使用LOAD DATA INFILE时,必须考虑数据文件的位置:
1.从服务器主机装入文件:
1.默认情况下,输入记录造成重复键违规将产生一个错误,不会装入数据文件的剩余部分,该点之前的已处理记录将被装入表中;
2.如果在文件名后提供IGNORE关键字,将忽略造成重复键违规的新记录,并且语句不会生成错误;LOAD DATA INFILE将处理整个文件,装入所有不包含重复键的记录,并放弃剩余记录;
3.如果在文件名后提供REPLACE关键字,造成重复键违规的新记录将替换表中现存的包含重复键值的任何记录;LOAD DATA INFILE将处理整个文件,将文件中的所有记录装入表中;
2.从客户机主机装入文件:
1.默认情况下LOAD DATA INFILE将忽略包含重复键的记录;即,默认行为与指定IGNORE选项时相同;
2.这是因为客户机/服务器协议不允许在传输开始后中断从客户机主机到服务器的数据文件传输,因此不方便在操作过程中中止操作;
-- 补充:
1.mysqldump:会在备份恢复时候讲;
2.mysqlimport:就是LOAD DATA INFILE的客户端版;
2.mysqlexp:就是SELECT INTO OUTFILE的客户端版;
-- 课后练习;