快捷搜索:
来自 网络数据库 2020-01-01 13:17 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

数据导入,数据插入

1.insert和replace插入数据

先解释insert。

insert插入数据有两种语法,简洁版如下:

1.使用values()或value():insert into table_name values(),(),(),()...

2.使用set子句:insert into table_name set column_name=value,...

3.使用select子句:insert into table_name select_statement

其次种语法是MySQL/MariaDB对标准SQL insert语句的扩展。

玛丽亚DB/MySQL备份苏醒种类:
备份和重振旗鼓(风度翩翩卡塔尔国:mysqldump工具用法详述
备份和回复(二卡塔尔:导入、导出表数据
备份和还原(三卡塔尔国:xtrabackup用法和法规详述

1.1 insert into values()

加以如下表构造:

create or replace table t(
    id int primary key,
    sex char(3) default('nv'),
    name char(20)
);

其间字段'sex'含有默许值属性,其暗许值为'nv',name字段允许NULL,那也是该字段的私下认可值。

用insert插入几行数据:

insert into t values(1,'nan','longshuai1'); # 插入一行数据
insert into t values(2,'nan','longshuai2'),(3,'nv','xiaofang1'); # 一次性插入两行数据
insert into t values(4,DEFAULT,'xiaofang2'); # sex字段使用默认值
insert into t(id,name) values(5,'xiaofang3'); # 指定插入的字段
insert into t(id,sex,name) values(6,'nv','xiaofang4'); # 等价于insert into t values()
insert into t(name,id) values('xiaofang5',7); # 指定插入的字段,且改变字段顺序
insert into t value(8,'nan','longshuai3'); # 使用value,而非values

查阅插入的数目:

MariaDB [test]> select * from t;
 ---- ------ ------------ 
| id | sex  | name       |
 ---- ------ ------------ 
|  1 | nan  | longshuai1 |
|  2 | nan  | longshuai2 |
|  3 | nv   | xiaofang1  |
|  4 | nv   | xiaofang2  |
|  5 | nv   | xiaofang3  |
|  6 | nv   | xiaofang4  |
|  7 | nv   | xiaofang5  |
|  8 | nan  | longshuai3 |
 ---- ------ ------------ 

专一,MySQL/MariaDB中私下认可变量autocommit=1,那意味着InnoDB表每一次insert(以至其它的DML语句卡塔尔都会自动提交业务,提交业务意味着会将脏数据刷到磁盘,那很影响插入功效。

insert into t values(卡塔尔国,(卡塔尔(قطر‎,(卡塔尔一次插入四个值的频率比分多次奉行insert功用要高得多。不仅仅不要频频地开拓、关闭连接,还是可以将在插入的多少缓存起来,一准时间之后再二遍性刷到磁盘。

至于怎么样高效插入数据到玛丽亚DB/MySQL中,见后文:怎么着高效插入多量数额。


1.2 insert into set

insert into set语法是应用set子句为各种字段设置值。所以,字段的依次未有其他须求。

例如:

insert into t set name='longshuai4',id=9,sex='nan';

1.导出、导入数据

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');

1.3 insert into select_statement

insert into tbl_name select_statement语法是从别的表中检索数据,并将追寻到的多少插入到表tbl_name中。要求tbl_name必得已经存在。

例如:

insert into t select 10,'nan','longshuai5';
insert into t select 11,'nan','longshuai6' from dual;
insert into t select 12,'nv','xiaofang6' union select 13,'nan','longshuai7';

譬喻从任何表中检索数据,则select检索的字段须要和tbl_name表的字段对应。

create or replace table tx(user char(20),host char(20),password char(50));
insert into tx select user,host,password from mysql.user;

如果表tbl_name不存在,则insert into tbl_name select_statement语句会失败。那时候能够使用create table select的点子来创造表的同不平时间插入数据。

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'

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.2 load data infile导入数据

要导入格式化后的纯数据,能够利用load data infile,加载纯数据的插入方式比直接试行insert插入最少快20多倍。但在里面,它们其实是等价行为,load data infile也会触发insert相关触发器。

其间能够选拔local关键字表示从客商端主机读取文件,若无一些名local则代表从服务端主机读取文件。

田野s和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.5 管理键值重复的标题

当表中留存唯生机勃勃性索引(比如primary key,unique index卡塔尔时,插入的记录纵然键值重复,则插入操作会失利。MariaDB中有三种方式消逝重复值冲突的标题:

  1. 接受ignore关键字忽视全部错误行,使insert操作继续插入前边的多少。
  2. 使用insert ... on duplicate key update,将有重复值的行update为新的值。
  3. 选拔replace into语句代替insert into语句,将有重复值的行替换为新行。

举例,创制带有主键的表,并插入几行数据。

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

insert into t values
(1,'nan','longshuai1'),(2,'nan','longshuai2'),(3,'nv','xiaofang1'),
(4,'nv','xiaofang2'),(5,'nv','xiaofang3'),(6,'nv','xiaofang4'),
(7,'nv','xiaofang5'),(8,'nan','longshuai3'),(9,'nan','longshuai4');

当插入id=5的新记录,暗中认可会报错,假设是多值插入,全体的插入都会停业。

insert into t values(5,'nv','xiaofang33'),(10,'nan','longshuai5');
ERROR 1062 (23000): Duplicate entry '5' for key 'PRIMARY'

select * from t where id=5 or id=10;
 ---- ------ ----------- 
| id | sex  | name      |
 ---- ------ ----------- 
|  5 | nv   | xiaofang3 |
 ---- ------ ----------- 

以下是两种减轻重复值冲突的措施。

1.ignore第一字,忽略就要重复的记录。

整体语法见下文。此处只给采取示例。

insert ignore into t values(5,'nv','xiaofang33'),(10,'nan','longshuai5');
Query OK, 1 row affected, 1 warning (0.003 sec)
Records: 2  Duplicates: 1  Warnings: 1

从重回中能够看见:1 row affected, 1 warning。说明只成功插入了一站式记录,id=5的重复记录被忽略间接跳过了。

show warnings;
 --------- ------ --------------------------------------- 
| Level   | Code | Message                               |
 --------- ------ --------------------------------------- 
| Warning | 1062 | Duplicate entry '5' for key 'PRIMARY' |
 --------- ------ --------------------------------------- 

select * from t where id=5 or id=10;
 ---- ------ ------------ 
| id | sex  | name       |
 ---- ------ ------------ 
|  5 | nv   | xiaofang3  |
| 10 | nan  | longshuai5 |
 ---- ------ ------------ 

2.ON DUPLICATE KEY UPDATE。

能够在INSERT语句后边加上on duplicate key update子句来改革重复值记录。

那有三种景况:(1卡塔尔插入的记录未有键值重复冲突;(2卡塔尔(قطر‎插入的笔录有键值重复矛盾。

对于第后生可畏种景况,INSERT语句中包蕴on duplicate key update子句并从未任何分歧,它们都只是纯粹地insert数据。

对此第二种处境,INSERT语句中隐含on duplicate key update子句会更新表中原有的记录。

例如:

# 插入无键值重复冲突的记录
insert into t values(11,'nv','xiaofang4'),(12,'nan','longshuai6');

# 插入键值重复冲突的记录id=5,更新其name
insert into t values(5,'nv','xiaofang33'),(13,'nv','xiaofang5') on duplicate key update name='xiaofang333';
Query OK, 3 rows affected (0.002 sec)
Records: 2  Duplicates: 1  Warnings: 0

3行受到震慑,个中1行双重值。这里的3行记录中,有1行是插入id=13的记录,另两行是因为id=5重复而直面的熏陶,涉及的操作是将旧id=5行update为新id=5的行。

select * from t where id=5 or id=13;
 ---- ------ ------------- 
| id | sex  | name        |
 ---- ------ ------------- 
|  5 | nv   | xiaofang333 |
| 13 | nv   | xiaofang5   |
 ---- ------ ------------- 

可以在UPDATE子句中接纳VALUES(col_name)函数从INSERT...ON DUPLICATE KEY UPDATE讲话的INSERT部分引用列值。譬如:

insert into t values(5,'nv','xiaofang33') on duplicate key update name=concat('xiaofang',values(id));

select * from t where id=5;
 ---- ------ ----------- 
| id | sex  | name      |
 ---- ------ ----------- 
|  5 | nv   | xiaofang5 |
 ---- ------ ----------- 

VALUES(State of Qatar函数只在INSERT...ON DUPLICATE KEY UPDATE语句中有含义,其余时候会回来NULL。

INSERT...ON DUPLICATE KEY UPDATE语句的施行原理是:

(1State of Qatar.尝试插入新行,决断新行是还是不是和表中本来就有记录存在键值冲突。那时会触发before insert触发器。

(2卡塔尔国.若无冲突,就直接插入新行,那时会触发after insert触发器。

(3卡塔尔(قطر‎.如若有冲突,则更新旧行为新行。那个时候会先触发before update触发器,更新后触发after update触发器。

所以,当有重复值冲突的时候,将触发before insert、before update和after update触发器。详细内容见:on duplicate key update剖析触发器触发原理。

3.replace into语句,更新重复值所在的记录行。

replace into和insert into语法完全后生可畏致,能够完全代表insert into语句。它们中间只有存在键值重复冲突的时候才有分别。

当未有键值重复冲突时,replace into和insert into完全等价。

当键值重复矛盾时,replace into语句会将表中产生冲突的记录完全替换为新行。

replace into t values(5,'nv','xiaofang33');

select * from t where id=5;
 ---- ------ ------------ 
| id | sex  | name       |
 ---- ------ ------------ 
|  5 | nv   | xiaofang33 |
 ---- ------ ------------ 

replace into语句的实施原理是:

(1State of Qatar.尝试插入新行,判定新行是还是不是和表中本来就有记录存在键值冲突。那时候会触发before insert触发器。

(2State of Qatar.若无冲突,就直接插入新行,那时候会触发after insert触发器。

(3State of Qatar.要是有冲突,则删除旧行,并插入新行。当时会触发before delete触发器,删除后触发after delete触发器,最终插入数据后触发after insert触发器。详细内容见:replace into算法深入分析。

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.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_P景逸SUVIO中华VITY关键字的意义在见(MariaDB/MySQLState of QatarMyISAM存款和储蓄引擎读、写操作的先行级。

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'

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:数据导入,数据插入

关键词: