MySQL数据导入和导出纯数据的方式有哪些

51次阅读
没有评论

共计 7872 个字符,预计需要花费 20 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 MySQL 数据导入和导出纯数据的方式有哪些,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!

MySQL 的数据导入和导出纯数据的方式,一般采用的是 load data file 、mysqlimport 、select into outfile、/ 重定向的方式,这里主要介绍 load data file 和 select into outfile 的方式。

一、MySQL 导入和导出数据:
  1、load data file
    简介:
   The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. LOAD DATA INFILE is the complement of SELECT …INTO OUTFILE.
    官方参考手册:http://dev.mysql.com/doc/refman/5.6/en/load-data.html
    语法格式:
   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,…]
   load data file 示例:
   1)直接导入文件
    LOAD DATA INFILE test_data.txt INTO TABLE dbtest.t1;
   2) 设置列分隔符和行分隔符
    LOAD DATA LOCAL INFILE test_data.txt INTO TABLE t1
    FIELDS TERMINATED BY ,
    OPTIONALLY ENCLOSED BY
    LINES TERMINATED BY \n
   3)导入到特定的列
    LOAD DATA LOCAL INFILE test_data.txt INTO TABLE  t1 (col1,col2,col3);
      参数说明:
   1)local 参数
      如果指定了 LOCAL,被认为与连接的客户端有关,则文件会被客户主机上的客户端读取,并被发送到服务器。文件会被给予一个完整的路径名称,以指定确切的位置。如果给定的是一个相对的路径名称,则此名称会被理解为相对于启动客户端时所在的目录。
      如果 LOCAL 没有被指定,则文件必须位于服务器主机上,并且被服务器直接读取。
    当在服务器主机上为文件定位时,服务器使用以下规则:
      如果给定了一个绝对的路径名称,则服务器使用此路径名称。
      如果给定了带有一个或多个引导组件的相对路径名称,则服务器会搜索相对于服务器数据目录的文件。
      如果给定了一个不带引导组件的文件名称,则服务器会在默认数据库的数据库目录中寻找文件。
    注意,这些规则意味着名为./myfile.txt 的文件会从服务器数据目录中被读取,而名为 myfile.txt 的同样的文件会从默认数据库的数据库目录中读取。
    从客户端使用绝对路径 load 数据:LOAD DATA LOCAL INFILE /import/test_data.txt INTO TABLE dbtest.t1;
    从服务器里使用相对路径 load 数据,下面的 LOAD DATA 语句会从 dbtest 数据库目录中读取文件 test_data.txt,因为 db1 是当前数据库。即使语句明确把文件载入到 db2 数据库中的表里,也会从 dbtest 目录中读取。
   USE dbtest;
   LOAD DATA INFILE test_data.txt INTO TABLE dbtest.t1;
    总而言之:如果指定 local 关键词,则表明从客户主机读文件。如果 local 没指定,文件必须位于服务器上。
   2)IGNORE number LINES 参数
   IGNORE number LINES 选项可以被用于在文件的开始处忽略行。可以使用 IGNORE 1 LINES 来跳过一个包含列名称的起始标题行:
   LOAD DATA INFILE /tmp/test.txt   INTO TABLE test IGNORE 1 LINES;
   3)REPLACE、IGNORE 参数
    如果您指定了 REPLACE,则输入行会替换原有行(与原有行一样,对一个主索引或唯一索引具有相同值的行)。
    如果您指定 IGNORE,则把原有行复制到唯一关键字值的输入行被跳过。
    如果您这两个选项都不指定,则运行情况根据 LOCAL 关键词是否被指定而定。不使用 LOCAL 时,当出现重复关键字值时,会发生错误,并且剩下的文本文件被忽略。使用 LOCAL 时,默认的运行情况和 IGNORE 被指定时的情况相同;这是因为在运行中间,服务器没有办法中止文件的传输。
   4)FIELDS 参数
    指定了文件字段的分割格式,列分隔符参数语法为      
   [{FIELDS | COLUMNS}
    [TERMINATED BY string]
    [[OPTIONALLY] ENCLOSED BY char ]
    [ESCAPED BY char]
    ]
   
   terminated by 描述字段的分隔符,默认情况下是 tab 字符(\t)
   enclosed by 描述的是字段的括起字符,如果您忽略了词语 OPTIONALLY,则所有的字段都被包含在 ENCLOSED BY 字符串中, 如果您指定了 OPTINALLY,则 ENCLOSED BY 字符只被用于包含具有字符串数据类型(比如 CHAR, BINARY, TEXT 或 ENUM)的列中的值.
   escaped by 描述的转义字符。默认的是反斜杠(backslash:\)
    如果您不指定 FIELDS 子句,则默认值为假设您写下如下语句时的值:
   FIELDS TERMINATED BY \t ENCLOSED BY ESCAPED BY \\

    当读取输入值时,默认值会使 LOAD DATA INFILE 按如下方式运行:
    在新行处寻找行的边界。
    不会跳过任何行前缀。
    在制表符处把行分解为字段。
    不希望字段被包含在任何引号字符之中。
    出现制表符、新行、或在 \ 前有 \ 时,理解为作为字段值一部分的文字字符。
    备注: 如果您已经在 Windows 系统中生成了文本文件,您可能必须使用 LINES TERMINATED BY  \r\n 来正确地读取文件,因为 Windows 程序通常使用两个字符作为一个行终止符。部分程序,当编写文件时,可能会使用 \r 作为行终止符。要读取这样的文件,应使用 LINES TERMINATED BY \r。要写入 FIELDS ESCAPED BY  \\,您必须为待读取的值指定两个反斜杠,作为一个单反斜杠使用。
   6)LINES 参数
    指定了每条记录的分隔符默认为’\n’即为换行分隔符,其语法为:
   [LINES
    [STARTING BY string]
    [TERMINATED BY string]
    ]
    如果您不指定 LINES 子句,则默认值为假设您写下如下语句时的值:
   LINES TERMINATED BY \n STARTING BY
    如果所有您希望读入的行都含有一个您希望忽略的共用前缀,则您可以使用 prefix_string 来跳过前缀(和前缀前的字符)。如果某行不包括前缀,则整个行被跳过。注释:prefix_string 会出现在一行的中间。

   ALTER TABLE…DISABLE KEYS
   ALTER TABLE…ENABLE KEYS

  2、select into outfile
    简介:
    官方参考手册:http://dev.mysql.com/doc/refman/5.6/en/select-into.html
    语法格式:
     SELECT … INTO var_list    ##selects column values and stores them into variables.
     SELECT … INTO OUTFILE    ##writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.
     SELECT … INTO DUMPFILE    ##writes a single row to a file without any formatting.

   select into outfile 示例:
    1)SELECT … INTO OUTFILE file_name
    2)SELECT a,b,a+b INTO OUTFILE /tmp/result.txt
       FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY
       LINES TERMINATED BY \n
       FROM test_table;
    3)select * from t1 into outfile /tools/databak/dbtest_t1.txt
     fields TERMINATED BY ,  
     lines TERMINATED BY \n
    参数说明:(参考 load data 参数)
   
  3、mysqlimport(不推荐使用)
   mysqlimport 是操作系统命令,和 load data 实现功能相同,具体使用方法如下:
    语法格式:
   mysqlimport  –defaults-file= –default-character-set=utf8  –columns=id,name –delete –fields-terminated-by= –fields-enclosed-by= –fields-optionally-enclosed-by= –fields-escaped-by= –force -h -i –ignore-lines –lines-terminated-by= -L -p -P -S -u  dbname

    使用示例:
   mysqlimport  -L -uroot dbtest /tools/databak/t1.txt  –fields-terminated-by= ,   –lines-terminated-by= \n
    参数说明:
   mysqlimport –help
   –print-defaults  Print the program argument list and exit.
   –no-defaults  Don t read default options from any option file,
    except for login file.
   –defaults-file=#  Only read default options from the given file #.
   –defaults-extra-file=# Read this file after the global files are read.
   –defaults-group-suffix=#
    Also read groups with concat(group, suffix)
   –login-path=#  Read this path from the login file.
    –bind-address=name IP address to bind to.
    –character-sets-dir=name
    Directory for character set files.
    –default-character-set=name
    Set the default character set.
    -c, –columns=name  Use only these columns to import the data to. Give the
    column names in a comma separated list. This is same as
    giving columns to LOAD DATA INFILE.
    -C, –compress  Use compression in server/client protocol.
    -#, –debug[=name]  Output debug log. Often this is d:t:o,filename .
    –debug-check  Check memory and open file usage at exit.
    –debug-info  Print some debug info at exit.
    –default-auth=name Default authentication client-side plugin to use.
    -d, –delete  First delete all rows from table.
    –enable-cleartext-plugin
    Enable/disable the clear text authentication plugin.
    –fields-terminated-by=name
    Fields in the input file are terminated by the given
    string.
    –fields-enclosed-by=name
    Fields in the import file are enclosed by the given
    character.
    –fields-optionally-enclosed-by=name
    Fields in the input file are optionally enclosed by the
    given character.
    –fields-escaped-by=name
    Fields in the input file are escaped by the given
    character.
    -f, –force  Continue even if we get an SQL error.
    -?, –help  Displays this help and exits.
    -h, –host=name  Connect to host.
    -i, –ignore  If duplicate unique key was found, keep old row.
    –ignore-lines=#  Ignore first n lines of data infile.
    –lines-terminated-by=name
    Lines in the input file are terminated by the given
    string.
    -L, –local  Read all files through the client.
    -l, –lock-tables  Lock all tables for write (this disables threads).
    –low-priority  Use LOW_PRIORITY when updating the table.
    -p, –password[=name]
    Password to use when connecting to server. If password is
    not given it s asked from the tty.
    –plugin-dir=name  Directory for client-side plugins.
    -P, –port=#  Port number to use for connection or 0 for default to, in
    order of preference, my.cnf, $MYSQL_TCP_PORT,
    /etc/services, built-in default (3306).
    –protocol=name  The protocol to use for connection (tcp, socket, pipe,
    memory).
    -r, –replace  If duplicate unique key was found, replace old row.
    –secure-auth  Refuse client connecting to server if it uses old
    (pre-4.1.1) protocol.
    (Defaults to on; use –skip-secure-auth to disable.)
    -s, –silent  Be more silent.
    -S, –socket=name  The socket file to use for connection.
    –ssl  Enable SSL for connection (automatically enabled with
    other flags).
    –ssl-ca=name  CA file in PEM format (check OpenSSL docs, implies
    –ssl).
    –ssl-capath=name  CA directory (check OpenSSL docs, implies –ssl).
    –ssl-cert=name  X509 cert in PEM format (implies –ssl).
    –ssl-cipher=name  SSL cipher to use (implies –ssl).
    –ssl-key=name  X509 key in PEM format (implies –ssl).
    –ssl-crl=name  Certificate revocation list (implies –ssl).
    –ssl-crlpath=name  Certificate revocation list path (implies –ssl).
    –ssl-verify-server-cert
    Verify server s Common Name in its cert against
    hostname used when connecting. This option is disabled by
    default.
    –ssl-mode=name  SSL connection mode.
    –use-threads=#  Load files in parallel. The argument is the number of
    threads to use for loading data.
    -u, –user=name  User for login if not current user.
    -v, –verbose  Print info about the various stages.
    -V, –version  Output version information and exit.

以上是“MySQL 数据导入和导出纯数据的方式有哪些”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注丸趣 TV 行业资讯频道!

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-27发表,共计7872字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)