Shulou(Shulou.com)06/01 Report--

The following brings you a simple way to import formatted data from MySQL. I believe you must have seen similar articles. What's the difference between what we bring to everyone? Let's take a look at the body. I'm sure you'll get something after reading MySQL's simple way to import formatted data.

In some scenarios, we need to import a batch of data in a specific format into the mysql database. There are many ways to use shell script or python. Today, let's introduce two more convenient commands, mysqlimport and load data.

Mysqlimport

Usage

Mysqlimport [options] db_name textfile1 [textfile2...] # db_name corresponds to the database name # textfile file name, and corresponds to the database table name to be inserted. For example, mysqlimport A8 campaign.txt will insert the data from campaign.txt into the campaign table in the A8 database.

Note: the campaign.txt file must be in the / usr/local/mysql/var/a8/ directory, where the database is located

If it is not in the corresponding directory, the following error is returned:

Mysqlimport-uroot-p123456-hlocalhost-P3306 a8 campaign.txt / usr/local/mysql/bin/mysqlimport: Error: File'/ usr/local/mysql/var/a8/campaign.txt' not found (Errcode: 2), when using table: campaign

Common option

-- the data table column name corresponding to columns=id,name,creator...#, and defines the database table column name corresponding to each column in the imported file-- what separates the fields-terminated-by=# file fields, the parameters are strings, and the default is what symbol to expand the\ t--fields-enclosed-by=# data field. The default is empty. Generally, it can be double quotation marks, parentheses, etc.-- what symbols can be used in fields-optionally-enclosed-by=# data fields, because only part of the data can be enclosed in these symbols-- fields-escaped-by=# escape characters, parameters are characters, default is\-- what ends the lines-terminated-by=# data line, and parameters are strings Windows defaults to\ r\ n--user=user_name or-u user_name--password= [password] or-p [password]-- host=host_name or-h hostname--port=port_num, or-P port_num# defines the user name, password, mysql CVM address and TCP/IP port number used to connect, and defaults to mysql default port 3306--ignore-lines=n# ignores the first n lines of the data file Because many data files are preceded by headers-delete-D# deletes the original data from the table before inserting the data in the file-local-L# specifies that the data file is read from the client computer, otherwise it is read from the server computer-lock-tables-l # locks all tables for writing before processing the text file Make sure all tables are synchronized on the server-the connection protocol used by protocol= {TCP | SOCKET | PIPE | MEMORY}-force-f # ignores errors. For example, if a table for a text file does not exist, continue to process other files without using-- force, and if the table does not exist, mysqlimport exits-- compress-C # compresses all information sent between the client and the server (if both support compression)-- silent,-s# silent mode, outputs only when an error occurs-- socket=path -S path# socket file (default host) used when connecting to localhost-- verbose,-v# verbose mode. Print out the details of the program operation. -- version,-V# displays version information and exits.

Load data

Usage

Mysql > 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] [(id,name,creator)]

Description:

The load data infile statement imports text data into the data table. Before using this command, the mysqld process (service) must already be running. Make sure you have read access to the file before using it

1. If you specify the keyword low_priority, MySQL will not insert the data until no one else reads the table. You can use the following command:

Mysql > load data low_priority infile "/ home/root/data.sql" into table campaign

2. If you specify the local keyword, it indicates that the file is read from the client host. If local is not specified, the file must be located on the cloud server.

3. The replace and ignore keywords control the duplicate processing of existing unique key records. If you specify replace, the new row replaces the existing row with the same unique key value. If you specify ignore, skip the input of duplicate lines of existing lines with a unique key. If you do not specify any of the options, an error occurs when a duplicate key is found, and the rest of the text file is ignored. For example:

Mysql > load data low_priority infile "/ home/root/data.sql" replace into table campaign

4. Delimiter

1) the fields keyword specifies the splitting format of the file field, and if this keyword is used, the MySQL profiler would like to see at least one of the following options:

Terminated by: delimiter, which character is used in the field as the delimiter enclosed by: the field encloses the character, for example: `Zhouli "," 10 "," learning well "`such a line, you need to write `ENCLOSED BY'"'``escaped by: escape character lines terminated by: describes the delimiter of the field. By default, it is the tab character (\ t) ignore number lines: the line used to ignore the beginning of the import file. For example: number=1, the first line of data in the imported file is ignored.

For example:

Mysql > load data infile "/ home/root/data.sql" replace into table campaign fields terminated by',' enclosed by' "'

2) the lines keyword specifies that the delimiter for each record defaults to'n', which is the newline character.

If both fields are specified, fields must precede lines. If you do not specify the default value of the fields keyword, it is the same as writing: fields terminated by'\ t 'enclosed by' escaped by'\\'

If you do not specify a lines clause, the default value is the same as written like this: lines terminated by'\ n'

For example:

Mysql > load data infile "/ root/load.txt" replace into table test fields terminated by', 'lines terminated by' / n'

5. Load data infile can import the file into the database according to the specified column. When we want to import part of the data, we need to add some columns (columns / fields / field) to the MySQL database to meet some additional needs. For example, when we want to upgrade from an Access database to a MySQL database

The following example shows how to import data into a specified column (field):

Mysql > load data infile "/ home/root/campaign.txt" into table campaign (id, name, creator)

6. When looking for files on the CVM, the CVM uses the following rules:

If ● gives an absolute pathname, the CVM uses that pathname.

If ● gives a relative pathname with one or more front parts, the CVM searches for files relative to the data directory of the CVM.

If ● gives a file name without a front part, the cloud server looks for the file in the database directory of the current database.

For example, / campaign.txt is read from the data directory of the cloud server, while campaign.txt is read from the database directory of the current database.

For the above simple method of importing formatted data into MySQL, do you think it is what you want? If you want to know more about it, you can continue to follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.