Help Center/ GaussDB(for MySQL)/ FAQs/ Database Usage/ How Do I Use LOAD DATA to Import Local Data?
Updated on 2024-09-05 GMT+08:00
View PDF
Share

How Do I Use LOAD DATA to Import Local Data?

You can use LOAD DATA to import local data to GaussDB(for MySQL).

Syntax

LOAD DATA LOCAL
    INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
    ]
    [LINES
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var
        [, col_name_or_user_var] ...)]

Parameters

  • file_name: path of the local file to be imported.
  • REPLACE | IGNORE: whether to replace or ignore duplicate records.
  • tbl_name: name of the table to be imported.
  • CHARACTER SET charset_name: file encoding format. You are advised to use the encoding format of the GaussDB(for MySQL) instances to avoid garbled characters.
  • FIELDS TERMINATED BY 'string': separator between columns. The default value is \t.
  • [OPTIONALLY] ENCLOSED BY 'char': used to ignore symbols in data source fields.
  • LINES TERMINATED BY'string??: newline character between lines. The default value is \n.

    On some hosts running the Windows servers, the newline characters of text files may be \r\n, which is invisible.

  • IGNORE number LINES: used to ignore lines at the start of the file.
  • (column_name_or_user_var, ...): columns to be imported. If this parameter is not configured, data is imported based on the column sequence by default.
  • For other parameters, see the load data infile on the MySQL official website. The sequence of other parameters must be correct. For sequence details, visit the MySQL official website.

Standard Example

Prerequisites
  • The local_infile parameter must be enabled on the server. Click the instance name to go to the Basic Information page. On the Parameters page, change the value of this parameter to ON.
  • The local-infile parameter must be enabled on the client. Configure local-infile in the my.cnf file or use the --local-infile=1 option to connect to the database. 
    [mysql]
local-infile
  1. Import the data in the local file qq.txt to the test table. The qq.txt file contains five rows of data. The column separator is ',' and the row separator is '\n'. 
    1,a
2,b
3,c
4,d
5,"e"
  2. Create the test table. 
    CREATE TABLE test (
`id` int NOT NULL,
`a` varchar(4) NOT NULL, 
PRIMARY KEY (`id`) 
);
  3. On the client, run the LOAD DATA statement to import data in the qq.txt file to the test table, set the character set to utf8, and ignore the double quotation marks in the data source field. 
    mysql> LOAD DATA LOCAL INFILE '/data/qq.txt' IGNORE INTO TABLE test CHARACTER SET 'utf8' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';
Query OK, 5 rows affected, 1 warning (0.00 sec)
Records: 5  Deleted: 0  Skipped: 0  Warnings: 1

mysql> select * from test;
+----+---+
| id | a |
+----+---+
|  1 | a |
|  2 | b |
|  3 | c |
|  4 | d |
|  5 | e |
+----+---+
5 rows in set (0.00 sec)
  1. Importing data affects performance of GaussDB(for MySQL) instances. Import data during off-peak hours.
  2. Do not to initiate multiple LOAD DATA requests at the same time. When multiple LOAD DATA requests are initiated, SQL transactions may time out due to highly concurrent data write operations, table locking, and system I/O occupation, resulting in failure of all LOAD DATA requests.
Parent topic: Database Usage