How to Load Unicode Text Encoded as UTF-8 into a MySQL Table?

Loading Unicode Text Encoded as UTF-8 into a MySQL Table

To import data from a CSV file containing non-English characters encoded in UTF-8 into a MySQL table, specific steps must be taken to ensure proper character handling.

Character Set Configuration:

Before loading the data, you must set the character set of the corresponding column in the table to utf8. This ensures that the column can store UTF-8 encoded data.

Data Loading with LOAD DATA INFILE:

Use the following syntax for the LOAD DATA INFILE command to load the data:

<code class="sql">LOAD DATA INFILE 'file'
IGNORE INTO TABLE table
CHARACTER SET UTF8
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'</code>

• CHARACTER SET UTF8: Specifies that the data is encoded in UTF-8
• IGNORE: Skips any rows that fail to load properly due to character encoding issues
• FIELDS TERMINATED BY ';': Change this to the appropriate delimiter if your file uses a different one for separating columns
• OPTIONALLY ENCLOSED BY '"': Use this if your data contains special characters that need to be quoted
• LINES TERMINATED BY 'n': Modify to reflect the line-ending character in your CSV file

Example:

<code class="python">import MySQLdb

conn = MySQLdb.connect(host="localhost", user="user", passwd="password", db="database")
cursor = conn.cursor()

# Set the character set of the column
cursor.execute("ALTER TABLE table MODIFY COLUMN column_name VARCHAR(255) CHARACTER SET UTF8")

# Load the data using LOAD DATA INFILE
cursor.execute(
    """
    LOAD DATA INFILE '/path/to/file.csv'
    IGNORE INTO TABLE table
    CHARACTER SET UTF8
    FIELDS TERMINATED BY ';'
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    """
)

conn.commit()</code>

The above is the detailed content of How to Load Unicode Text Encoded as UTF-8 into a MySQL Table?. For more information, please follow other related articles on the PHP Chinese website!