澳门新萄京官方网站-www.8455.com-澳门新萄京赌场网址

澳门新萄京官方网站:CSV文件导入导mysql数据库,

2019-06-29 作者:数据库网络   |   浏览(141)

1.导入

基本语法:
load data [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[character set gbk]
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name, )]

mysql导入数据loaddatainfile用法

mysql导入数据load data infile用法

基本语法: load data [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name, )]

load data infile语句从一个文本文件中以很高的速度读入一个表中。使用这个命令之前,mysqld进程(服务)必须已经在运行。为了安全原因,当读取位于服务器上的文本文件时,文件必须处于数据库目录或可被所有人读取。另外,为了对服务器上文件使用load data infile,在服务器主机上你必须有file的权限。 1 如果你指定关键词low_priority,那么MySQL将会等到没有其他人读这个表的时候,才把插入数据。可以使用如下的命令:
load data low_priority infile "/home/mark/data sql" into table Orders; 2 如果指定local关键词,则表明从客户主机读文件。如果local没指定,文件必须位于服务器上。 3 replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace,新行将代替有相同的唯一键值的现有行。如果你指定ignore,跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项,当找到重复键时,出现一个错误,并且文本文件的余下部分被忽略。例如: load data low_priority infile "/home/mark/data sql" replace into table Orders; 4 分隔符 (1) fields关键字指定了文件记段的分割格式,如果用到这个关键字,MySQL剖析器希望看到至少有下面的一个选项:
terminated by分隔符:意思是以什么字符作为分隔符
enclosed by字段括起字符
escaped by转义字符 terminated by描述字段的分隔符,默认情况下是tab字符(t)
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠(backslash: ) 例如:load data infile "/home/mark/Orders txt" replace into table Orders fields terminated by',' enclosed by '"'; (2)lines 关键字指定了每条记录的分隔符默认为'n'即为换行符 如果两个字段都指定了那fields必须在lines之前。如果不指定fields关键字缺省值与如果你这样写的相同: fields terminated by't' enclosed by ’ '' ‘ escaped by'\' 如果你不指定一个lines子句,缺省值与如果你这样写的相同: lines terminated by'n' 例如:load data infile "/jiaoben/load.txt" replace into table test fields terminated by ',' lines terminated by '/n'; 5 load data infile 可以按指定的列把文件导入到数据库中。 当我们要把数据的一部分内容导入的时候,,需要加入一些栏目(列/字段/field)到MySQL数据库中,以适应一些额外的需要。比方说,我们要从Access数据库升级到MySQL数据库的时候 下面的例子显示了如何向指定的栏目(field)中导入数据:
load data infile "/home/Order txt" into table Orders(Order_Number, Order_Date, Customer_ID); 6 当在服务器主机上寻找文件时,服务器使用下列规则:
(1)如果给出一个绝对路径名,服务器使用该路径名。
(2)如果给出一个有一个或多个前置部件的相对路径名,服务器相对服务器的数据目录搜索文件。
(3)如果给出一个没有前置部件的一个文件名,服务器在当前数据库的数据库目录寻找文件。
例如: /myfile txt”给出的文件是从服务器的数据目录读取,而作为“myfile txt”给出的一个文件是从当前数据库的数据库目录下读取。 注意:字段中的空值用N表示

mysql导入数据load data infile用法 基本语法:load data [low_priority]澳门新萄京官方网站:CSV文件导入导mysql数据库,mysql导入数据loaddatainfile用法。 [local] infile 'file_name txt' [replace | ignore] into table t...

1. select into outfile导出表数据

SELECT [列名] FROM table [WHERE 语句] INTO OUTFILE '目标文件' [OPTION];

其中的option为可选参数,在缺省的情况下,默认的表现为:

  1. 在字段值间加上制表符。

  2. 不用任何引号字符封装字段。

  3. 使用 “” 转义出现在字段值中的定位符、换行符或`' 字符实例。

  4. 在行的结尾处加上换行符。

那么option的可选参数为fields子句和lines子句,在fields子句下有三个亚子句,分别为:

terminated by(用来指定字段值之间的符号),

enclosed by(用来指定包裹文件中字符值的符号),

escaped by.

如果指定了fields子句,那么这三个起码要指定一个.

在lines子句下有terminated by(指定一行结束的标志),starting by(设置每行数据开头的字符)

例子:

fields terminated by ',' //fields子句enclosed by '"'lines terminated by 'rn' //lines子句

下面来演示一遍缺省option选项的效果:

如今有这样的一个表,user_info

mysql> select * from user_info; ---- ---------- | id | nickname | ---- ---------- | 1 | 昵称1 || 2 | 昵称2 || 3 | 昵称3 | ---- ---------- 3 rows in set 

执行select into outfile语句:

mysql> select * from user_info into outfile '/home/xiaoma/file.txt';ERROR 1290 : The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

没想到的是mysql报错了,原来是mysql默认只能导出文件到一个指定的文件夹内,现在执行select @@secure_file_priv看看:

 mysql> select @@secure_file_priv; ----------------------- | @@secure_file_priv | ----------------------- | /var/lib/mysql-files/ | ----------------------- 1 row in set 

现在再改一下:

mysql> select * from user_info into outfile '/var/lib/mysql-files/file.txt';Query OK, 3 rows affected 

澳门新萄京官方网站 1

为什么会这样呢,原来啊如果使用 select ... into outfile ...方式导出数据,需要分为两种情况,windows系统下,可以随意指定对应的目录,如果是linux系统的话,只能导出到指定的目录,包括下面的load data infile语句的执行也同样如此处理

如果说导出数据到文件之后出现乱码的情况,那么可以指定字符集:

mysql> select * from user_info into outfile '/var/lib/mysql-files/file1.txt' character set gbk;Query OK, 3 rows affected 

我们常常导入数据!mysql有一个高效导入方法,那就是load data infile 下面来看案例说明

1语法

LOAD DATA [ LOW_PRIORITY | CONCURRENT ] [ LOCAL ] INFILE 'file_name.txt'

    [ REPLACE | IGNORE ]    

    INTO TABLE tbl_name

    [ FIELDS

      [ TERMINATED BY 'string' ]

      [ [OPTIONALLY] ENCLOSED BY 'char' ]

      [ ESCAPED BY 'char'  ]

    ]

    [ LINES 

      [ STARTING BY 'string' ]

      [ TERMINATED BY 'string' ]

    ]

   [ IGNORE number LINES ]

   [ (col_name_or_user_var,...) ]

澳门新萄京官方网站:CSV文件导入导mysql数据库,mysql导入数据loaddatainfile用法。   [ SET col_name = expr,...] ]

关键字解释
  
  LOAD DATA [ LOW_PRIORITY | CONCURRENT ] [ LOCAL ] INFILE 'file_name.txt'

    [ REPLACE | IGNORE ]    

    INTO TABLE tbl_name

  1LOW_PRIORITY:   加载数据被延迟,直道没有其他客户端从表中读取为止  
   CONCURRENT:   当加载的数据正在进行的时,如果有用户请求则开启新的线程来获取数据  

  2如果指定了LOCAL 关键字,它将对连接的客户端做出解释:

    • 如果指定了LOCAL,客户端主机上的客户端组件读取文件并发送到服务器。可以给出文件的完整路径以确定其精确位置。如果给出的是相对路径,则文件名是相对于客户端组件启动时所在的目录。
    • 如果没有指定LOCAL,文件是位于服务器的主机上,并且直接被服务器读取。
      当从服务器主机定位文件时,服务器使用下列规则:
        • 如果给定完整的路径,服务器使用该路径名。
        • 如果给定一个或多个前置构件的相对路径,服务器以相对服务器的数据目录搜索文件。
        • 如果给定没有前置构件的文件名,服务器从当前数据库的数据库目录搜寻文件。

  3REPLACE 和IGNORE 关键字处理那些与已存在的主键值重复的输入记录
    如果指定了REPLACE,输入行将会代替已存在的行(也就是说,主索引值相同的行将作为存在的行)。参考REPLACE 语法 。
    如果指定了IGNORE,与已存在行主键值重复的输入行将被跳过。如果不指定二者中的任一个,则操作行为将依赖是否指定了LOCAL关键字。
    没有指定LOCAL,则如果发现有重复的键值,将产生一个错误,并忽略文本文件的其余部分。
    如果指定了LOCAL,则缺省的操作行为将与指定了IGNORE 的相同;这是因为,在操作过程中,服务器没有办法终止文件的传送。

  [ FIELDS

      [ TERMINATED BY 'string' ]

      [ [OPTIONALLY] ENCLOSED BY 'char' ]   //enclosed by描述的是字段的括起字符。

      [ ESCAPED BY 'char'  ]          //escaped by描述的转义字符。默认的是反斜杠 (backslash: )  

  ]
  [ LINES 

      [ STARTING BY 'string' ]

      [ TERMINATED BY 'string' ]

    ]
  4
  数据格式:
  "中华之子","100","威武的不行了"
  "大米","63","我爱吃"
  "苹果","15","好吃" 
     load data infile 'D:/aa.txt' ignore into table 表名 character set gbk fields terminated by ',' enclosed by '"' lines terminated by 'rn' (表中的字段1,表中的字段2,表中的字段3);  
     terminated by ','  用什么风格字符
     enclosed by '"'    字符用什么包起来的
     lines terminated by 'rn'   行的末尾换行符是什么(liunx改为n)

  [ IGNORE number LINES ]

   [ (col_name_or_user_var,...) ]

   [ SET col_name = expr,...] ]

   5IGNORE number LINES选项可被用来忽略在文件的某一行

      假设数据文件如下:
 
      Book1.csv

        编号,名称,说明
        1,测试数据1,"测试CSV文件中,有逗号"
        2,测试数据2,"测试CSV文件中有""双引号"""
        3,测试数据3,"测试CSV文件中,有逗号和""双引号"""
        4,测试数据4,普通数据
 
        mysql> CREATE TABLE Test_Book1 (
            ->   id    int,
            ->   name  VARCHAR(10),
            ->   data  VARCHAR(100)
            -> );
        Query OK, 0 rows affected (0.05 sec)
         
         
        下面的 lines terminated by 'rn' 是 要求换行符号,为 windows的换行
        下面的 ignore 1 lines是 忽略第一行的标题行。
        mysql> LOAD DATA INFILE 'f:/Book1.csv'
            -> INTO TABLE Test_Book1
            -> FIELDS TERMINATED BY ','
            -> OPTIONALLY ENCLOSED BY '"'
            -> lines terminated by 'rn'
            -> ignore 1 lines
            -> (id, name, data);

        mysql> select * from test_book1;
        ------ ----------- --------------------------------
        | id   | name      | data                           |
        ------ ----------- --------------------------------
        |    1 | 测试数据1 | 测试CSV文件中,有逗号           |
        |    2 | 测试数据2 | 测试CSV文件中有"双引号"        |
        |    3 | 测试数据3 | 测试CSV文件中,有逗号和"双引号" |
        |    4 | 测试数据4 | 普通数据                       |
        ------ ----------- --------------------------------

        ignore 1 lines 忽略第一行的标题行。
  
  6@dummy的使用
    假设数据文件如下:
 
    Book1.csv

      1,测试数据1,"测试CSV文件中,有逗号"
      2,测试数据2,"测试CSV文件中有""双引号"""
      3,测试数据3,"测试CSV文件中,有逗号和""双引号"""
      4,测试数据4,普通数据

     mysql> CREATE TABLE Test_Book1 (
            ->   id    int,
            ->   name  VARCHAR(10),
            ->   data  VARCHAR(100)
            -> );
      Query OK, 0 rows affected (0.05 sec)
    我只需要第一,二列的数据的时候
      mysql> LOAD DATA INFILE 'f:/test_main.txt'
          -> INTO TABLE test_main6
          -> FIELDS TERMINATED BY ','
          -> OPTIONALLY ENCLOSED BY ''''
          -> (id,name, @dummy);

        mysql> select * from test_book1;
        ------ ----------- --------------------------------
        | id   | name      | data                           |
        ------ ----------- --------------------------------
        |    1 | 测试数据1 |                                |
        |    2 | 测试数据2 |                                |
        |    3 | 测试数据3 |                                |
        |    4 | 测试数据4 |                                |
        ------ ----------- --------------------------------

        这里的关键点, 在于那个   @dummy。
        对你而言, 如果文件有 10列, 你只导 2 , 7 的话, 那就是
          @dummy,列2,@dummy,@dummy,@dummy,@dummy,列7,@dummy,@dummy,@dummy

  7set子句
      SET 子句可以提供不是来源于输入文件的值。下面的语句将column3 设置为当前的日期和时间:
          LOAD DATA INFILE 'file.txt'  
          INTO TABLE t1  
          (column1, column2)  
          SET column3 = CURRENT_TIMESTAMP;  
      可以让某一列的值参与一些操作
          LOAD DATA INFILE 'file.txt'  
          INTO TABLE t1  
          (column1, @var1)  
          SET column2 = @var1/100;  
      也可以让前后列的数据产生关系(假设第一列是一个日期格式的数据,我的第三列可以根据第一列的日期计算出星期信息然后存入数据库)
          LOAD DATA INFILE 'file.txt'  
          INTO TABLE t1  
          (column1, column2)  
          SET column3 = SET name=CASE WHEN DATE_FORMAT(date,'%w') =1 THEN "星期一" 
                                      WHEN DATE_FORMAT(date,'%w') =2 THEN "星期二" 
                                      end;

      使用列/变量列表和SET子句要受到一下限制:
        • SET 子句中的赋值列名应当只能在赋值操作符的左边。
        • 在SET 赋值语句中,可以使用子查询。此查询返回一个将被赋予列的值,它可能仅仅是一个标量查询。不能用子查询去查询将被导入的表。
        • 对于列/变量列表或SET 子句,由于使用IGNORE 子句而被忽略的行就不会被处理。
        • 由于用户变量没有显示宽度,当导入的数据是行固定格式时,将不能使用用户变量。

 

8设置存入时候使用的字符

gbk  utf8

 load data infile 'C:\Users\edgewalk\Desktop\test.txt' ignore into table test character set gbk fields terminated by ','
lines terminated by 'n' (
date,
money,
desctipt
)

  导入实例

1 load data infile 'csv文件路径\test.csv' 
2 replace into table 表名              
3 fields terminated by ',' 
4 optionally enclosed by '"' 
5 lines terminated by 'rn' 
6 ignore 1 lines(Id,@name,password);

  说明:

         第一行就是导入文件;

         第二行参看语法就会发现有两个词:replace 和 ignore 。replaceignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace,新行将代替有相同的唯一键值的现有行。如果你指定ignore,跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项,当找到重复键时,出现一个错误,并且文本文件的余下部分被忽略。

         第三~四行很简单就是每个具体字段内容之间是以逗号隔开的,那就以逗号分开。 erminated by描述字段的分隔符,默认情况下是tab字符(t) 。enclosed by描述的是字段的括起字符,就是说字段中如果有引号,就当做是字段的一部分。 语法中还有一个是 escaped by, 它描述的是转义字符。默认的是反斜杠(backslash: )

*        第五行 lines terminated by是对每行进行分割,这里要注意一个问题,如果csv文件是在windows下生成,那分割用 ‘rn’,linux下用 ‘n’。*

*        第六行中 ignore 1 lines 是忽略第一行,因为第一行往往是字段名,后边括号中有个字段很特别 @name,它是说如果csv文件中有个字段我不想插进去,那就把对应字段名变成@name.*

具体操作:

step1.准备CSV文件

      1.在数据库中建test数据表,表属性如下:

    澳门新萄京官方网站 2      2.csv文件的存储内容如下,命名为test1.csv,存储位置:“C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/test1.csv”:

澳门新萄京官方网站 3

 

         验证.csv编码格式是否正确,务必保证导入数据的编码格式是ANSI编码格式

step2.数据导入:

         1.查询已有数据库:show databases;

         2.使用这个数据库,使用命令:use flight_analysis;

         3.查询我们之前建立的表格test是否在test数据库中,使用命令:show tables;

         4.导入数据库:

          

#导入数据中不包含中文
1   load data infile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/test1.csv' --CSV文件存放路径
2 
3   into test student--要将数据导入的表名
4 
5   fields terminated by ',' optionally enclosed by '"' escaped by '"'
6 
7   lines terminated by 'rn';

 

#导入数据中包含中文

load data infile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/test1.csv' --CSV文件存放路径

into table test character set gb2312 --要将数据导入的表名

fields terminated by ',' optionally enclosed by '"' escaped by '"'

lines terminated by 'rn';

1 #忽略第一行
2 load data infile "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/datatest1.csv"
3 into table data_test
4 fields terminated by ',' optionally enclosed by '"' escaped by '"'
5 lines terminated by 'rn'
6 ignore 1 lines;

 

2. load data infile将文件数据导入到表中(select into outfile的反操作)

导入文件时要注意编码问题,也要加上同样的分隔限制语句

先看看此语句的结构:

load data [low_priority] [local] infile 'file_name txt' [replace | ignore]into table tbl_name[fields[terminated by't'][OPTIONALLY] enclosed by ''][escaped by'' ]][lines terminated by'n'][ignore number lines][(col_name, )]

由上面的结构图,先分析一下这个语句结构图吧:

若指定此关键词,MySQL将会等到没有其他人读这个表的时候,才把插入数据

如果指定local关键词,则表明从客户主机读文件。如果local没指定,文件必须位于服务器上

当插入数据的时候,对于重复插入现有的唯一键的行,在默认情况下,mysql会报错,然后中断插入数据,若指定replace关键词,新行将会替换原现有的行,若指定ignore关键词,就会直接跳过对于重复插入行的操作.

澳门新萄京官方网站,fields子句包含3个亚子句,若指定了此子句那么必须要至少指定一个亚子句,亚子句分别为:2.4.1 terminated by 描述字段的分隔符,默认情况下是tab字符2.4.2 enclosed by 描述的是字段的括起字符.2.4.3 escaped by 描述的转义字符.默认的是反斜杠

例如:

mysql> load data infile '/var/lib/mysql-files/loadInFile.txt' into table user_info fields terminated by ',';Query OK, 3 rows affected Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

lines子句只有一个亚子句,terminated by 指定一行结束的标志.

load data infile '/var/lib/mysql-files/loadInFile.txt' into table user_info character set gbk fields terminated by ',';

 

2使用load data infile导入导出备份数据

  如果要导出一个表中的部分字段或者部分符合条件的记录,需要用到了mysql的into outfile 和 load data infile 。 例如下面的mysql命令是把select的mytable表中的数据导出到C:\Users\7q\Desktop\test1.txt。
  select * from test into outfile 'C:\Users\7q\Desktop\test1.txt' fields terminated by ','  lines terminated by 'rn' ;

  假如要导入刚才备份的数据,可以使用load file方法,例如下面的mysql命令,把导出的数据导入了mytable_bak的表中:
  load data infile 'C:\Users\7q\Desktop\test1.txt' into table mytable_bak fields terminated by ',' lines terminated by 'rn';
  这种方法的好处是,导出的数据可以自己规定格式,并且导出的是纯数据,不存在建表信息,你可以直接导入另外一个同数据库的不同表中.

 

导出

1 select * from 表名
2 into outfile '导出路径\test.csv' 
3 fields terminated by ',' 
4 optionally enclosed by '"' 
5 escaped by '"' 
6 lines terminated by 'n';

 

 

 

         

 

3. mysqldump 命令备份数据库(会备份表结构和数据)

先来看看用此命令时的结构

mysqldump -uroot -p [database name] > [dump file] //其中root为mysql账号名

mysqldump -uroot -p test > test.sql

如果加上--opt参数则生成的dump文件中稍有不同: . 建表语句包含drop table if exists tableName . insert之前包含一个锁表语句lock tables tableName write,insert之后包含unlock tables

mysqldump -uroot -p --opt test > test.sql

mysqldump -uroot -p --databases test blog_test > /test.sql

mysqldump -uroot -p --no-data test > test.sqlmysqldump -uroot -p --no-data test > test.sql

mysqldump -uroot -p --all-databases > /all.sql

语法结构如下:

mysql -u root -p [dbname] < backup.sql

假如备份了所有的数据库,然后只恢复部分的数据库:

mysql -uroot -p test < all.sql

除了用这个语法结构,还可以用source命令恢复数据库,例如有一个test数据库,有一个test.sql的备份文件,进入mysql后,use test切换到test数据库底下,然后执行:

mysql>source /mysql.sql;

结合Linux的cron命令实现定时备份

比如需要在每天凌晨1:30备份某个主机上的所有数据库并压缩dump文件为gz格式,那么可在/etc/crontab配置文件中加入下面代码行:

30 1 * * * root mysqldump -u root -pPASSWORD --all-databases | gzip > /mnt/disk2/database_`date ' %m-%d-%Y'`.sql.gz前面5个参数分别表示分钟、小时、日、月、年,星号表示任意。date ' %m-%d-%Y'得到当前日期的MM-DD-YYYY格式。

基本语法:

load data  [low_priority] [local] infile 'file_name txt' [replace | ignore]
into table tbl_name
[fields
[terminated by't']
[OPTIONALLY] enclosed by '']
[escaped by'' ]]
[lines terminated by'n']
[ignore number lines]
[(col_name,   )]

load data infile语句从一个文本文件中以很高的速度读入一个表中。使用这个命令之前,mysqld进程(服务)必须已经在运行。为了安全原因,当读取位于服务器上的文本文件时,文件必须处于数据库目录或可被所有人读取。另外,为了对服务器上文件使用load data infile,在服务器主机上你必须有file的权限。

1  如果你指定关键词low_priority,那么MySQL将会等到没有其他人读这个表的时候,才把插入数据。可以使用如下的命令:
load data  low_priority infile "/home/mark/data sql" into table Orders;

 

2  如果指定local关键词,则表明从客户主机读文件。如果local没指定,文件必须位于服务器上。

 

3  replace和ignore关键词控制对现有的唯一键记录的重复的处理。如果你指定replace,新行将代替有相同的唯一键值的现有行。如果你指定ignore,跳过有唯一键的现有行的重复行的输入。如果你不指定任何一个选项,当找到重复键时,出现一个错误,并且文本文件的余下部分被忽略。例如:

load data  low_priority infile "/home/mark/data sql" replace into table Orders;

 

4 分隔符

(1) fields关键字指定了文件记段的分割格式,如果用到这个关键字,MySQL剖析器希望看到至少有下面的一个选项:
terminated by分隔符:意思是以什么字符作为分隔符
enclosed by字段括起字符
escaped by转义字符

terminated by描述字段的分隔符,默认情况下是tab字符(t)
enclosed by描述的是字段的括起字符。
escaped by描述的转义字符。默认的是反斜杠(backslash: )  

例如:load data infile "/home/mark/Orders txt" replace into table Orders fields terminated by',' enclosed by '"';

(2)lines 关键字指定了每条记录的分隔符默认为'n'即为换行符

如果两个字段都指定了那fields必须在lines之前。如果不指定fields关键字缺省值与如果你这样写的相同: fields terminated by't' enclosed by ’ '' ‘ escaped by'\'

如果你不指定一个lines子句,缺省值与如果你这样写的相同: lines terminated by'n'

例如:load data infile "/jiaoben/load.txt" replace into table test fields terminated by ',' lines terminated by '/n';

5  load data infile 可以按指定的列把文件导入到数据库中。 当我们要把数据的一部分内容导入的时候,,需要加入一些栏目(列/字段/field)到MySQL数据库中,以适应一些额外的需要。比方说,我们要从Access数据库升级到MySQL数据库的时候

下面的例子显示了如何向指定的栏目(field)中导入数据:
load data infile "/home/Order txt" into table Orders(Order_Number, Order_Date, Customer_ID);

6  当在服务器主机上寻找文件时,服务器使用下列规则:
(1)如果给出一个绝对路径名,服务器使用该路径名。
(2)如果给出一个有一个或多个前置部件的相对路径名,服务器相对服务器的数据目录搜索文件。  
(3)如果给出一个没有前置部件的一个文件名,服务器在当前数据库的数据库目录寻找文件。
例如: /myfile txt”给出的文件是从服务器的数据目录读取,而作为“myfile txt”给出的一个文件是从当前数据库的数据库目录下读取。

 

 

注意:字段中的空值用N表示

我的文章一般浅显易懂,不会搞那么深入让大家很难理解。(其实我水平也不咋样)

LOAD DATA INFILE 一直被认为是MySQL很强大的一个数据导入工具,因为他速度非常的快。
不过有几个问题一定要注意
1、编码。
2、灵活导入导出。

我来举两个例子说明一下。
一、关于编码
我们的示例文本文件:
"我爱你","20","相貌平常,经常耍流氓!哈哈"
"李奎","21","相貌平常,经常耍流氓!哈哈"
"王二米","20","相貌平常,经常耍流氓!哈哈"
"老三","24","很强"
"老四","34","XXXXX"
"老五","52","***%*¥*¥*¥*¥"
"小猫","45","中间省略。。。"
"小狗","12","就会叫"
"小妹","21","PP的很"
"小坏蛋","52","表里不一"
"上帝他爷","96","非常英俊"
"MM来了","10","。。。"
"歌颂党","20","社会主义好"
"人民好","20","的确是好"
"老高","10","学习很好"
"斜三","60","眼睛斜了"
"中华之子","100","威武的不行了"
"大米","63","我爱吃"
"苹果","15","好吃"
我们的示例表结构:

------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Create Table                                                                                                                                                                                                                                                          |
------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| t0    | CREATE TABLE `t0` (
  `id` bigint(20) unsigned NOT NULL auto_increment,
  `name` char(20) NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `idx_name` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

我们把这个文本文件从WINDOWS 下COPY到LINUX下看看

mysql> load data infile '/tmp/t0.txt' ignore into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by 'n' (`name`,`age`,`description`);
Query OK, 19 rows affected (0.01 sec)
Records: 19  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t0;
---- ---------- ----- ----------------------------
| id | name     | age | description                |
---- ---------- ----- ----------------------------
|  1 | 我爱你   |  20 | 相貌平常,经常耍流氓!哈哈 |
|  2 | 李奎     |  21 | 相貌平常,经常耍流氓!哈哈 |
|  3 | 王二米   |  20 | 相貌平常,经常耍流氓!哈哈 |
|  4 | 老三     |  24 | 很强                       |
|  5 | 老四     |  34 | XXXXX                      |
|  6 | 老五     |  52 | ***%*¥*¥*¥*¥           |
|  7 | 小猫     |  45 | 中间省略。。。             |
|  8 | 小狗     |  12 | 就会叫                     |
|  9 | 小妹     |  21 | PP的很                     |
| 10 | 小坏蛋   |  52 | 表里不一                   |
| 11 | 上帝他爷 |  96 | 非常英俊                   |
| 12 | MM来了   |  10 | 。。。                     |
| 13 | 歌颂党   |  20 | 社会主义好                 |
| 14 | 人民好   |  20 | 的确是好                   |
| 15 | 老高     |  10 | 学习很好                   |
| 16 | 斜三     |  60 | 眼睛斜了                   |
| 17 | 中华之子 | 100 | 威武的不行了               |
| 18 | 大米     |  63 | 我爱吃                     |
| 19 | 苹果     |  15 | 好吃                       |
---- ---------- ----- ----------------------------
19 rows in set (0.00 sec)

我来说明一下相关的参数
关于我的导入语句,我现在直说两个,其他的参考手册。
character set gbk;

这个字符集一定要写,要不然就会乱码或者只导入一部分数据。

ignore into table

因为name 列加了唯一索引,加这个是为了避免重复数据插入报错。

加入我们再次运行这个导入语句就会发现
Query OK, 0 rows affected (0.00 sec)
Records: 19  Deleted: 0  Skipped: 19  Warnings: 0
没有任何值导入,因为里面已经有了相同的值。

这里也可以用replace into table
MySQL会把相同的先干掉,再插入新的值。
mysql> load data infile '/tmp/t0.txt' replace into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by 'n' (`name`,`age`,`description`);
Query OK, 38 rows affected (0.00 sec)
Records: 19  Deleted: 19  Skipped: 0  Warnings: 0

mysql> select * from t0;
---- ---------- ----- ----------------------------
| id | name     | age | description                |
---- ---------- ----- ----------------------------
| 20 | 我爱你   |  20 | 相貌平常,经常耍流氓!哈哈 |
| 21 | 李奎     |  21 | 相貌平常,经常耍流氓!哈哈 |
| 22 | 王二米   |  20 | 相貌平常,经常耍流氓!哈哈 |
| 23 | 老三     |  24 | 很强                       |
| 24 | 老四     |  34 | XXXXX                      |
| 25 | 老五     |  52 | ***%*¥*¥*¥*¥           |
| 26 | 小猫     |  45 | 中间省略。。。             |
| 27 | 小狗     |  12 | 就会叫                     |
| 28 | 小妹     |  21 | PP的很                     |
| 29 | 小坏蛋   |  52 | 表里不一                   |
| 30 | 上帝他爷 |  96 | 非常英俊                   |
| 31 | MM来了   |  10 | 。。。                     |
| 32 | 歌颂党   |  20 | 社会主义好                 |
| 33 | 人民好   |  20 | 的确是好                   |
| 34 | 老高     |  10 | 学习很好                   |
| 35 | 斜三     |  60 | 眼睛斜了                   |
| 36 | 中华之子 | 100 | 威武的不行了               |
| 37 | 大米     |  63 | 我爱吃                     |
| 38 | 苹果     |  15 | 好吃                       |
---- ---------- ----- ----------------------------
19 rows in set (0.00 sec)

 (`name`,`age`,`description`);

这些也就是具体的表属性了,指明这个就可以导入想要的数据。
2、关于灵活性,其实也就是一个记录功能
如果想在导入的时候记录一下导入的具体时间怎么办?
我们来看看
先加一个时间属性记录导入时间。
mysql> alter table t0 add update_time timestamp not null;
Query OK, 19 rows affected (0.00 sec)
Records: 19  Duplicates: 0  Warnings: 0
干掉唯一索引
mysql> alter table t0 drop index idx_name;
Query OK, 19 rows affected (0.00 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> load data infile '/tmp/t0.txt' into table t0 character set gbk fields terminated by ',' enclosed by '"' lines terminated by 'n' (`name`,`age`,`description`) set update_time=current_timestamp;
Query OK, 19 rows affected (0.00 sec)
Records: 19  Deleted: 0  Skipped: 0  Warnings: 0

mysql> select * from t0;
---- ---------- ----- ---------------------------- ---------------------
| id | name     | age | description                | update_time         |
---- ---------- ----- ---------------------------- ---------------------
| 20 | 我爱你   |  20 | 相貌平常,经常耍流氓!哈哈 | 0000-00-00 00:00:00 |
…………
| 24 | 老四     |  34 | XXXXX                      | 0000-00-00 00:00:00 |
| 25 | 老五     |  52 | ***%*¥*¥*¥*¥           | 0000-00-00 00:00:00 |
…………
| 35 | 斜三     |  60 | 眼睛斜了                   | 0000-00-00 00:00:00 |
| 36 | 中华之子 | 100 | 威武的不行了               | 0000-00-00 00:00:00 |
…………
| 60 | 王二米   |  20 | 相貌平常,经常耍流氓!哈哈 | 2008-06-30 14:58:37 |
…………
| 68 | 上帝他爷 |  96 | 非常英俊                   | 2008-06-30 14:58:37 |
| 69 | MM来了   |  10 | 。。。                     | 2008-06-30 14:58:37 |
…………
| 75 | 大米     |  63 | 我爱吃                     | 2008-06-30 14:58:37 |
| 76 | 苹果     |  15 | 好吃                       | 2008-06-30 14:58:37 |
---- ---------- ----- ---------------------------- ---------------------
38 rows in set (0.00 sec)

新导入的19条记录时间被记录了下来。
只是之前的数据库没有记录,不过现在不需要这些重复数据了。

干掉他就可以了

mysql> alter table t0 order by id desc;
Query OK, 38 rows affected (0.01 sec)
Records: 38  Duplicates: 0  Warnings: 0

mysql> alter ignore table t0 add unique index idx_name (`name`);
Query OK, 38 rows affected (0.00 sec)
Records: 38  Duplicates: 19  Warnings: 0

mysql> alter table t0 order by id asc;
Query OK, 19 rows affected (0.01 sec)
Records: 19  Duplicates: 0  Warnings: 0

mysql> select * from t0;
---- ---------- ----- ---------------------------- ---------------------
| id | name     | age | description                | update_time         |
---- ---------- ----- ---------------------------- ---------------------
| 58 | 我爱你   |  20 | 相貌平常,经常耍流氓!哈哈 | 2008-06-30 14:58:37 |
| 59 | 李奎     |  21 | 相貌平常,经常耍流氓!哈哈 | 2008-06-30 14:58:37 |
| 60 | 王二米   |  20 | 相貌平常,经常耍流氓!哈哈 | 2008-06-30 14:58:37 |
| 61 | 老三     |  24 | 很强                       | 2008-06-30 14:58:37 |
| 62 | 老四     |  34 | XXXXX                      | 2008-06-30 14:58:37 |
| 63 | 老五     |  52 | ***%*¥*¥*¥*¥           | 2008-06-30 14:58:37 |
| 64 | 小猫     |  45 | 中间省略。。。             | 2008-06-30 14:58:37 |
| 65 | 小狗     |  12 | 就会叫                     | 2008-06-30 14:58:37 |
| 66 | 小妹     |  21 | PP的很                     | 2008-06-30 14:58:37 |
| 67 | 小坏蛋   |  52 | 表里不一                   | 2008-06-30 14:58:37 |
| 68 | 上帝他爷 |  96 | 非常英俊                   | 2008-06-30 14:58:37 |
| 69 | MM来了   |  10 | 。。。                     | 2008-06-30 14:58:37 |
| 70 | 歌颂党   |  20 | 社会主义好                 | 2008-06-30 14:58:37 |
| 71 | 人民好   |  20 | 的确是好                   | 2008-06-30 14:58:37 |
| 72 | 老高     |  10 | 学习很好                   | 2008-06-30 14:58:37 |
| 73 | 斜三     |  60 | 眼睛斜了                   | 2008-06-30 14:58:37 |
| 74 | 中华之子 | 100 | 威武的不行了               | 2008-06-30 14:58:37 |
| 75 | 大米     |  63 | 我爱吃                     | 2008-06-30 14:58:37 |
| 76 | 苹果     |  15 | 好吃                       | 2008-06-30 14:58:37 |
---- ---------- ----- ---------------------------- ---------------------
19 rows in set (0.00 sec)

现在是达到了目的了,为啥中途要干掉唯一索引呢?因为set 语法 再有IGNORE 的时候会忽略掉。

本文由澳门新萄京官方网站发布于数据库网络,转载请注明出处:澳门新萄京官方网站:CSV文件导入导mysql数据库,

关键词: