What to do if mysql load data is garbled?

Mysql load data garbled solution: 1. Find the garbled SQL statement; 2. Modify the statement to "LOAD DATA LOCAL INFILE "employee.txt" INTO TABLE EMPLOYEE character set utf8;".

The operating environment of this tutorial: Windows 10 system, MySQL version 5.7, Dell G3 computer.

What should I do if mysql load data is garbled?

Solve the problem of garbled Chinese data imported by MySQL using LOAD

Assume that the data in the text employee.txt is imported into the table EMPLOYEE

Garbled SQL statements appear

LOAD DATA LOCAL INFILE "employee.txt" INTO TABLE EMPLOYEE;

Solution, add character set utf8

LOAD DATA LOCAL INFILE "employee.txt" INTO TABLE EMPLOYEE character set utf8;

Related extensions:

1. Use the load statement to import data

Here is the load command Simple summary.

After entering MySQL, use the command:

load data local infile 'S:/tab_user.txt' 
into table tab_user lines 
terminated by '\r\n' ignore 1 lines;

The syntax of this statement is as follows:

Basic syntax:

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,   )]

Explanation of the syntax part:

The load data infile statement reads from a text file into a table at a very high speed. Before using this command, the mysqld process (service) must be running.

For security reasons, when reading a text file located on the server, the file must be in the database directory or be readable by everyone.

In addition, in order to use load data infile on the file on the server, you must have file permissions on the server host.

(1) If you specify the keyword low_priority, then MySQL will wait until no one else reads this table before inserting data. You can use the following command:

load data low_priority infile "S:/tab_user.txt" into table tab_user;

2), if you specify the local keyword, it means reading the file from the client host . If local is not specified, the file must be located on the server.

(3), replace and ignore keywords control the duplicate processing of existing unique key records. If you specify replace, the new row will replace the existing row with the same unique key value. If you specify ignore, the input of duplicate rows for existing rows with unique keys is skipped. If you do not specify either option, an error occurs when a duplicate key is found, and the remainder of the text file is ignored. For example:

load data low_priority infile "S:/tab_user.txt" replace

into table tab_user;

(4), separator

( a) The fields keyword specifies the splitting format of file segments. If this keyword is used, the MySQL parser hopes to see at least one of the following options:

terminated by delimiter: what character does it mean? as delimiter

enclosed by field-enclosed character

escaped by escape character

terminated by delimiter describing the field, by default the tab character (\t)

enclosed by describes the bracketing characters of the field.

escaped by the escape character described. The default is backslash (backslash: \)

For example: load data infile "S:/tab_user.txt" replace into table tab_user (id, name) terminated by',' ignore 1 lines;(, As delimiter)

(b)lines keyword specifies the delimiter of each record. The default is '\n', which is the newline character

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

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

For example: load data infile "s:/tab_user.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/fields) 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):

load data infile "s:/ tab_user.txt" into table tab_user(id, name);

(6). When looking for files on the server host, the server uses the following rules:

(a) If a Absolute pathname used by the server.

(b) If a relative pathname with one or more preceding components is given, the server searches for the file relative to the server's data directory.

(c) If a file name without a prefix is ​​given, the server looks for the file in the database directory of the current database.

For example: The file given as "/myfile txt" is read from the server's data directory, and a file given as "myfile txt" is read from the database directory of the current database.

Recommended learning: "MySQL Video Tutorial"

The above is the detailed content of What to do if mysql load data is garbled?. For more information, please follow other related articles on the PHP Chinese website!