http://www.web008.net

数据插入,MySQL备份和恢复

2.导出、导入数据

load data infileselect into outfile语句是配套的。select into outfile语句是将检索出来的数据按格式导出到文件中,数据迁移跨数据库系统时,该选项很有用,因为它可以指定分隔符。load data infile是将带有格式的数据文件导入到表中。

导出、导入数据时需要指定格式(如不指定,则使用默认)。格式涉及几个方面:字段分隔符、行分隔符、引用符号、转义符号。

还需注意一点,默认情况下(MySQL 5.6.34之后)这两个语句无法执行成功,因为全局变量secure_file_priv的默认值为null,它表示禁用这两种语句的导入导出。

图片 1

所以应该将其设置为空(不指定任何值)或者指定一个目录,将来该目录中的所有文件都可以进行mysql file类的交互。当然,变量指定的目录必须已经存在,且mysql系统用户和组必须对该目录有读写权限。

mkdir /data
chown -R mysql.mysql /data

这个变量是全局静态变量,只能在mysqld实例未启动的时候才能修改。所以将其写入配置文件。

[mysqld]
secure-file-priv=/data
# 或者
# secure-file-priv=

查看变量。

select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /data/                    |
+---------------------------+

再看这两个语句的语法:

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
        [export_options]
    [IGNORE number {LINES|ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]


export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

其中'char'表示只能使用一个字符,'string'表示可以指定多个字符。

fields terminated by 'string'指定字段分隔符;enclosed by 'char'指定所有字段都使用char符号包围,如果指定了optionally则只用在字符串和日期数据类型等字段上,默认未指定;escaped by 'char'指定转义符。

lines starting by 'string'指定行开始符,如每行开始记录前空一个制表符;lines terminated by 'string'为行分隔符。

要注意,在几种情况下需要使用转义符:数据中含有转义符本身或者字段分隔符。当指定了字段引用符enclosed by时,如果数据中含有字段引用符,则也需要转义,若未指定enclosed by,则默认不使用字段引用符,所以无需转义。

以下为它们的默认值:

fileds terminated by 't' enclosed by '' escaped by '\'
lines terminated by 'n' starting by ''

看上去语法还挺复杂的,使用示例来说明就很清晰易懂了。

给定如下表结构和数据。

create or replace table t(id int primary key,sex char(3),name char(20),ins_day date);

insert into t values(1,'nan','longshuai1','2010-04-19'),
                    (2,'nan','longshuai2','2011-04-19'),
                    (3,'nv','xiaofang1','2012-04-19'),
                    (4,'nv','xiaofang2','2013-04-19'),
                    (5,'nv','xiaofang3','2014-04-19'),
                    (6,'nv','xiaofang4','2015-04-19'),
                    (7,'nv','tun'er','2016-04-19'),
                    (8,'nan','longshuai3','2017-04-19');

MariaDB/MySQL备份恢复系列:
备份和恢复(一):mysqldump工具用法详述
备份和恢复(二):导入、导出表数据
备份和恢复(三):xtrabackup用法和原理详述

2.3 mysqldump导出数据

select into outfile功能类似的语句还有:此方法导出的数据中还包含了列名。

mysql -uroot -p123456 -e "select * from test.t">/tmp/t_data2.sql

cat /tmp/t_data2.sql
id      sex     name    ins_day
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

虽说select ... into outfile导出数据后可修改性和加载性非常强,但是毕竟没有导出结构。要导出结构,可以使用mysqldump的"--tab"选项,它既会导出表的结构定义语句到同表名的.sql文件中,还会导出数据到同表名的.txt文件中。

mysqldump -uroot -p123456 --tab /data test t;

ls -l /data/t.*
-rw-r--r-- 1 root  root  1408 Apr 19 14:46 /data/t.sql   # test.t表定义语句
-rw-rw-rw- 1 mysql mysql  211 Apr 19 14:46 /data/t.txt   # test.t表内数据

mysqldump的"--tab"选项同样可以指定各种分隔符。如"--fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by=..."。以下是指定字段分隔符为","。

mysqldump -uroot -p123456 --tab /data --fields-terminated-by=',' test t;

cat /data/t.txt
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-19

1.1 select into outfile导出数据

使用默认设置:

select * from t into outfile '/data/t_data.sql';

! cat /data/t_data.sql
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

指定字段分隔符",",使用单引号包围各字段,每行前加上制表符。

select * from t into outfile '/data/t_data1.sql' fields terminated by ',' enclosed by ''' lines starting by 't' terminated by 'n';

! cat /data/t_data1.sql
        '1','nan','longshuai1','2010-04-19'
        '2','nan','longshuai2','2011-04-19'
        '3','nv','xiaofang1','2012-04-19'
        '4','nv','xiaofang2','2013-04-19'
        '5','nv','xiaofang3','2014-04-19'
        '6','nv','xiaofang4','2015-04-19'
        '7','nv','tun'er','2016-04-19'
        '8','nan','longshuai3','2017-04-19'

3.如何快速插入大量数据

见我翻译的官方手册:https://mariadb.com/kb/zh-cn/how-to-quickly-insert-data-into-mariadb/

大致说几点:

(1).禁用非唯一性索引,以及约束检查。

对于插入海量数据操作而言,该行为带来的效率提升是最大的。

ALTER TABLE table_name DISABLE KEYS;

BEGIN;
   INSERT_statement
# or
   LOAD DATA INFILE...
COMMIT;

ALTER TABLE table_name ENABLE KEYS;

多数存储引擎(至少MyISAM和Aria是如此)的ENABLE KEYS会扫描表中的行并收集索引键值,然后对它们排序,最后创建索引块。因此,先DISABLE KEYS,插入数据后启用KEY的整体速度比每行都更新一次索引的速度至少要快一个数量级,并且所需要的buffer也更少。

一般来说,DISABLE KEYS操作的时间极快,insert操作消耗的时间则要长很多,但消耗的最长时间还是在ENABLE KEYS操作上。

需要注意的是,当INSERT和LOAD DATA INFILE的目标是一张空表时,存储引擎会自动先DISABLE KEYS,然后插入数据,插入结束后再自动ENABLE KEYS

当插入海量数据时,花在完整性检查上的时间也会很长。可以通过设置系统变量unique_checksforeign_key_checks来禁用UNIQUE索引以及foreign keys约束对数据的检查:

SET @@session.unique_checks = 0;
SET @@session.foreign_key_checks = 0;

对于XtraDB/InnoDB表,可以临时将AUTO_INCREMENT的锁模式变量innodb_autoinc_lock_mode设置为2(见官方手册),如下:

SET @@global.innodb_autoinc_lock_mode = 2;

该变量默认值为1,当执行大批量插入语句(如insert...selectload data infile)时,这些语句会持有表锁直到插入语句执行完毕,而单个的insert语句则会持有粒度更细、更轻量级的互斥锁而非表锁。当该变量设置为0时,表示任意插入语句包括单个insert语句都会持有表锁,直到语句执行完毕。当该变量设置为2时,表示任意语句都不会持有表锁,而总是持有轻量级互斥锁。

此外,如果表上有INSERT触发器,或者PERSISTENT字段(具有确定性的计算列,每次插入数据时都会计算),可以先删除它们,在数据插入完成之后再重建它们。

# 查看表上有哪些insert触发器
select EVENT_OBJECT_TABLE,
        ACTION_TIMING,
        EVENT_MANIPULATION
from information_schema.triggers
where event_object_table='emp' and event_manipulation='INSERT';

# 查看表中是否有persistent字段
DESCRIBE table1;
+-------+-------------+------+-----+---------+------------+
| Field | Type        | Null | Key | Default | Extra      |
+-------+-------------+------+-----+---------+------------+
| a     | int(11)     | NO   |     | NULL    |            |
| b     | varchar(32) | YES  |     | NULL    |            |
| c     | int(11)     | YES  |     | NULL    | VIRTUAL    |
| d     | varchar(5)  | YES  |     | NULL    | PERSISTENT |
+-------+-------------+------+-----+---------+------------+

SHOW CREATE TABLE table1;
| table1 | CREATE TABLE `table1` (
  `a` int(11) NOT NULL,
  `b` varchar(32) DEFAULT NULL,
  `c` int(11) AS (a mod 10) VIRTUAL,
  `d` varchar(5) AS (left(b,5)) PERSISTENT
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

(2).使用load data [local] infile或者mysqlimport从文本文件中导入数据。

这是最快的数据插入方式。从客户端上读取文件的速度比直接在服务端上读取文件的速度要慢一些,但差距不会很大。

LOAD DATA INFILE之所以很快,是因为:

  1. 不需要解析SQL语句。

  2. 读取数据时可以一次读取很多个数据块。

  3. 如果load data之前表是空的,则所有的非唯一索引都会先禁用,插入数据成功后才启用。

4. 它会告诉存储引擎先缓存一些行的数据,达到一定数量后再一次性插入到表中(至少MyISAM和Aria支持该行为)。

5. 对于空表,某些事务性引擎(如Aria)不会记录数据插入的事务日志,因为我们可以随时通过truncate操作来回滚。

(3).将多个insert操作放进一个事务中。所以可以设置autocommit=0或者手动开启一个事务。

插入数据的时候使用大事务能极大提升效率,因为每次事务结束都会将内存中的数据sync到磁盘上。而刷盘操作所消耗的时间相对其他操作而言要久的多。

start transaction
insert ...
insert ...
commit
# 或者:
set @@session.autocommit=0;
insert ...
insert ...
set @@session.autocommit=1;

(4).尽量使用insert语句多值插入语法来插入数据。

即下面的第一条语句代替后3条语句:

insert into t values(),(),();
insert into t values();
insert into t values();
insert into t values();

(5).插入MyISAM表或Aria表时,使用INSERT DELAYED异步插入。

DELAYED关键字表示INSERT语句发送给服务端后,当语法检查正确后,服务端会立即返回OK给客户端表示插入成功。但实际上数据还未插入,只是放在服务端的内存队列中。

对于正在访问的表,使用DELAYED关键字后效率比一般的INSERT语句要高。但如果表目前是完全空闲的,则一般的INSERT语句效率比INSERT DELAYED效率要高,因为需要消耗一些资源去维护这个队列。

(6).修改几个系统变量。

图片 2

1.3 mysqldump导出数据

select into outfile功能类似的语句还有:此方法导出的数据中还包含了列名。

mysql -uroot -p123456 -e "select * from test.t">/tmp/t_data2.sql

cat /tmp/t_data2.sql
id      sex     name    ins_day
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

虽说select ... into outfile导出数据后可修改性和加载性非常强,但是毕竟没有导出结构。要导出结构,可以使用mysqldump的"--tab"选项,它既会导出表的结构定义语句到同表名的.sql文件中,还会导出数据到同表名的.txt文件中。

mysqldump -uroot -p123456 --tab /data test t;

ls -l /data/t.*
-rw-r--r-- 1 root  root  1408 Apr 19 14:46 /data/t.sql   # test.t表定义语句
-rw-rw-rw- 1 mysql mysql  211 Apr 19 14:46 /data/t.txt   # test.t表内数据

mysqldump的"--tab"选项同样可以指定各种分隔符。如"--fields-terminated-by=...,--fields-enclosed-by=...,--fields-optionally-enclosed-by=...,--fields-escaped-by=..."。以下是指定字段分隔符为","。

mysqldump -uroot -p123456 --tab /data --fields-terminated-by=',' test t;

cat /data/t.txt
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-19

1.4 create table select

创建表并插入数据两种语法:

create table tbl_name select_statement
create table tbl_name as select_statement

这些语句检索数据,并按照检索目标字段新建一张表,表必须不能已经存在,除非使用or replace或者if not exists子句。

# 创建新表,并插入几条数据
create or replace table t20 select user,host,password from mysql.user where user='root';

只创建表结构,不插入数据:

create table tbl_name1 like tbl_name2      # 创建完全相同的表结构
create table tbl_name1 select col1,col2,col3 from tbl_name2 where 1=0;  # where false。可以筛选部分字段作为新表的结构

例如:

create table t10 like mysql.user;   # 以mysql.user表为模板创建t10表
create table t11 select user,host,password from mysql.user where false; # 选出3个字段创建新表
create table t12(col1 char(20),col2 char(20),col3 char(50)) as  # 选出3个字段,但自定义新表的字段名称
       select user,host,password from mysql.user where 1=0;

需要注意:

create table [as] select_statement创建表的时候,只会创建几个字段并插入一些数据,不会复制模板表字段的属性,例如索引、默认值、auto_increment等。

create table like创建表的时候,新表和模板表的结构会完全相同,包括字段的属性(如default属性、auto_increment属性、索引等)。实际上,它是根据模板表的".frm"文件新建的。

1.导出、导入数据

load data infileselect into outfile语句是配套的。select into outfile语句是将检索出来的数据按格式导出到文件中,数据迁移跨数据库系统时,该选项很有用,因为它可以指定分隔符。load data infile是将带有格式的数据文件导入到表中。

导出、导入数据时需要指定格式(如不指定,则使用默认)。格式涉及几个方面:字段分隔符、行分隔符、引用符号、转义符号。

还需注意一点,默认情况下(MySQL 5.6.34之后)这两个语句无法执行成功,因为全局变量secure_file_priv的默认值为null,它表示禁用这两种语句的导入导出。

图片 3

所以应该将其设置为空(不指定任何值)或者指定一个目录,将来该目录中的所有文件都可以进行mysql file类的交互。当然,变量指定的目录必须已经存在,且mysql系统用户和组必须对该目录有读写权限。

mkdir /data
chown -R mysql.mysql /data

这个变量是全局静态变量,只能在mysqld实例未启动的时候才能修改。所以将其写入配置文件。

[mysqld]
secure-file-priv=/data
# 或者
# secure-file-priv=

查看变量。

select @@global.secure_file_priv;
+---------------------------+
| @@global.secure_file_priv |
+---------------------------+
| /data/                    |
+---------------------------+

再看这两个语句的语法:

SELECT ... INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        [export_options]

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
        [export_options]
    [IGNORE number {LINES|ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]


export_options:
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]

其中'char'表示只能使用一个字符,'string'表示可以指定多个字符。

fields terminated by 'string'指定字段分隔符;enclosed by 'char'指定所有字段都使用char符号包围,如果指定了optionally则只用在字符串和日期数据类型等字段上,默认未指定;escaped by 'char'指定转义符。

lines starting by 'string'指定行开始符,如每行开始记录前空一个制表符;lines terminated by 'string'为行分隔符。

要注意,在几种情况下需要使用转义符:数据中含有转义符本身或者字段分隔符。当指定了字段引用符enclosed by时,如果数据中含有字段引用符,则也需要转义,若未指定enclosed by,则默认不使用字段引用符,所以无需转义。

以下为它们的默认值:

fileds terminated by 't' enclosed by '' escaped by '\'
lines terminated by 'n' starting by ''

看上去语法还挺复杂的,使用示例来说明就很清晰易懂了。

给定如下表结构和数据。

create or replace table t(id int primary key,sex char(3),name char(20),ins_day date);

insert into t values(1,'nan','longshuai1','2010-04-19'),
                    (2,'nan','longshuai2','2011-04-19'),
                    (3,'nv','xiaofang1','2012-04-19'),
                    (4,'nv','xiaofang2','2013-04-19'),
                    (5,'nv','xiaofang3','2014-04-19'),
                    (6,'nv','xiaofang4','2015-04-19'),
                    (7,'nv','tun'er','2016-04-19'),
                    (8,'nan','longshuai3','2017-04-19');

2.4 mysqlimport导入数据

mysqlimport和load data infile的本质是一样的。mysqlimport在执行时会像服务端发送load data infile来加载数据,并且mysqlimport支持多进程并行导入多张表的数据。

mysqlimport的语法和load data infile基本一致。不同的是它在MySQL/MariaDB的外部执行,且可以一次性并行多线程导入多张表(并非并行导入一张表),所以能更快地导入所有数据。

mysqlimport [OPTIONS] database textfile...

注意:mysqlimport只能指定数据库名来导入,所以导入的文件名必须和数据库中的表名相对应(文件名后缀无所谓)。例如文件名为stu2.sql,而表名为student则无法导入,它会找stu2这个表。

例如,将以下格式的文件t.txt使用mysqlimport导入到test.t表中:

[root@xuexi ~]# cat /data/t.txt
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-19

[root@xuexi ~]# mysqlimport -uroot -p123456 --fields-terminated-by=',' test '/data/t.txt'

使用"--use-threads"选项可以指定导入线程数。

例如,下面指定两个线程,导入两张表到数据库test库中的t1和t2表中。

mysqlimport -uroot -p123456 --use-threads=2 --fields-terminated-by=',' test '/data/t1.txt' '/data/t2.txt'

1.6 完整的insert语法

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
 [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
 {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
 [ ON DUPLICATE KEY UPDATE
   col=expr
     [, col=expr] ... ]

或:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)]
    SET col={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] ... ]

或:

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [PARTITION (partition_list)] [(col,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col=expr
        [, col=expr] ... ]

其中on duplicate key update子句是在出现重复值冲突时使用的,前文已介绍。

IGNORE关键字的作用是忽略错误。当insert语句插入记录时,如果某行出现错误(例如不满足数据类型、重复值冲突等等),则INSERT语句不会停止,而是继续插入后面的记录。前文已介绍通过ignore解决键值重复冲突的问题。

DELAYED关键字设置INSERT的异步插入。当使用该关键字时,服务端立即返回OK给客户端,然后服务端将插入操作放进队列,等待没有其他进程访问该表的时候才会将队列中的数据插入到表中。在表被频繁访问的时候插入数据,使用DELAYED的效率比直接使用INSERT高,但如果没有任何进程访问表的时候,INSERT DELAYED比直接INSERT速度慢,因为MariaDB需要额外的资源来维护这个队列。如果某一时刻有进程访问该表,则多个客户端同时使用INSERT DELAYED插入数据的时候,这些数据会全部放进队列,效率比各客户端分开插入要高,因为队列中的数据是成批插入的。如果队列中的数据还没插入到表中,突然mysqld进程死掉了,则队列中的数据会丢失,即没有插入到表中。

LOW_PRIORITY和HIGH_PRIORITY关键字的意义在见(MariaDB/MySQL)MyISAM存储引擎读、写操作的优先级。

1.2 load data infile导入数据

要导入格式化后的纯数据,可以使用load data infile,加载纯数据的插入方式比直接执行insert插入至少快20多倍。但在内部,它们其实是等价行为,load data infile也会触发insert相关触发器。

其中可以使用local关键字表示从客户端主机读取文件,如果没有指定local则表示从服务端主机读取文件。

fields和lines的相关选项和select ... into outfile是一样的,只不过load data infile多了几个选项。其中ignore N lines|rows表示忽略前N行数据不导入,col_name_or_user_var表示按此处给定的字段和顺序来导入数据,set col_name=expr表示对列进行一些表达式运算,如给某数值字段加5,给某字符串列尾部加上@qq.com字符等。

例如要加载如下文件到test.t表中。

cat /data/t_data.txt
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

首先删除表中数据,再导入。

truncate test. t;
load data infile '/data/t_data.sql' into table test.t fields terminated by 't';

将如下包含字段分隔符",",字段引用符"'",转义符"",行前缀"t"的文件加载到test.t表中。

[root@xuexi ~]# cat /data/t_data1.sql
        '1','nan','longshuai1','2010-04-19'
        '2','nan','longshuai2','2011-04-19'
        '3','nv','xiaofang1','2012-04-19'
        '4','nv','xiaofang2','2013-04-19'
        '5','nv','xiaofang3','2014-04-19'
        '6','nv','xiaofang4','2015-04-19'
        '7','nv','tun'er','2016-04-19'
        '8','nan','longshuai3','2017-04-19'

首先删除表中数据,然后加载。

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by ''' escaped by '\' lines starting by 't' terminated by 'n';

若要忽略前两行,则:

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by ''' escaped by '\' lines starting by 't' terminated by 'n' ignore 2 rows;

如果想在id列值加上5,则:

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by ''' escaped by '\' lines starting by 't' terminated by 'n' set id=id+5;

如果想name列后加上"@qq.com"字符串,则:

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by ''' escaped by '\' lines starting by 't' terminated by 'n' set name=concat(name,'@qq.com');

如果想同时执行上面两个set,则:

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by ''' escaped by '\' lines starting by 't' terminated by 'n' set name=concat(name,'@qq.com'), id=id+5;

2.2 load data infile导入数据

要导入格式化后的纯数据,可以使用load data infile,加载纯数据的插入方式比直接执行insert插入至少快20多倍。但在内部,它们其实是等价行为,load data infile也会触发insert相关触发器。

其中可以使用local关键字表示从客户端主机读取文件,如果没有指定local则表示从服务端主机读取文件。

fields和lines的相关选项和select ... into outfile是一样的,只不过load data infile多了几个选项。其中ignore N lines|rows表示忽略前N行数据不导入,col_name_or_user_var表示按此处给定的字段和顺序来导入数据,set col_name=expr表示对列进行一些表达式运算,如给某数值字段加5,给某字符串列尾部加上@qq.com字符等。

例如要加载如下文件到test.t表中。

cat /data/t_data.txt
1       nan     longshuai1      2010-04-19
2       nan     longshuai2      2011-04-19
3       nv      xiaofang1       2012-04-19
4       nv      xiaofang2       2013-04-19
5       nv      xiaofang3       2014-04-19
6       nv      xiaofang4       2015-04-19
7       nv      tun'er  2016-04-19
8       nan     longshuai3      2017-04-19

首先删除表中数据,再导入。

truncate test. t;
load data infile '/data/t_data.sql' into table test.t fields terminated by 't';

将如下包含字段分隔符",",字段引用符"'",转义符"",行前缀"t"的文件加载到test.t表中。

[root@xuexi ~]# cat /data/t_data1.sql
        '1','nan','longshuai1','2010-04-19'
        '2','nan','longshuai2','2011-04-19'
        '3','nv','xiaofang1','2012-04-19'
        '4','nv','xiaofang2','2013-04-19'
        '5','nv','xiaofang3','2014-04-19'
        '6','nv','xiaofang4','2015-04-19'
        '7','nv','tun'er','2016-04-19'
        '8','nan','longshuai3','2017-04-19'

首先删除表中数据,然后加载。

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by ''' escaped by '\' lines starting by 't' terminated by 'n';

若要忽略前两行,则:

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by ''' escaped by '\' lines starting by 't' terminated by 'n' ignore 2 rows;

如果想在id列值加上5,则:

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by ''' escaped by '\' lines starting by 't' terminated by 'n' set id=id+5;

如果想name列后加上"@qq.com"字符串,则:

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by ''' escaped by '\' lines starting by 't' terminated by 'n' set name=concat(name,'@qq.com');

如果想同时执行上面两个set,则:

truncate test.t;
load data infile '/data/t_data1.sql' into table test.t fields terminated by ',' enclosed by ''' escaped by '\' lines starting by 't' terminated by 'n' set name=concat(name,'@qq.com'), id=id+5;

1.4 mysqlimport导入数据

mysqlimport和load data infile的本质是一样的。mysqlimport在执行时会像服务端发送load data infile来加载数据,并且mysqlimport支持多进程并行导入多张表的数据。

mysqlimport的语法和load data infile基本一致。不同的是它在MySQL/MariaDB的外部执行,且可以一次性并行多线程导入多张表(并非并行导入一张表),所以能更快地导入所有数据。

mysqlimport [OPTIONS] database textfile...

注意:mysqlimport只能指定数据库名来导入,所以导入的文件名必须和数据库中的表名相对应(文件名后缀无所谓)。例如文件名为stu2.sql,而表名为student则无法导入,它会找stu2这个表。

例如,将以下格式的文件t.txt使用mysqlimport导入到test.t表中:

[root@xuexi ~]# cat /data/t.txt
1,nan,longshuai1,2010-04-19
2,nan,longshuai2,2011-04-19
3,nv,xiaofang1,2012-04-19
4,nv,xiaofang2,2013-04-19
5,nv,xiaofang3,2014-04-19
6,nv,xiaofang4,2015-04-19
7,nv,tun'er,2016-04-19
8,nan,longshuai3,2017-04-19

[root@xuexi ~]# mysqlimport -uroot -p123456 --fields-terminated-by=',' test '/data/t.txt'

使用"--use-threads"选项可以指定导入线程数。

例如,下面指定两个线程,导入两张表到数据库test库中的t1和t2表中。

mysqlimport -uroot -p123456 --use-threads=2 --fields-terminated-by=',' test '/data/t1.txt' '/data/t2.txt'

郑重声明:本文版权归美高梅163888所有,转载文章仅为传播更多信息之目的,如作者信息标记有误,请第一时间联系我们修改或删除,多谢。