Loading UTF-8 Encoded Text into MySQL Tables

When importing data into MySQL tables containing non-English characters encoded in UTF-8, users may encounter issues with garbled characters despite setting the table's column character set to UTF-8.

To address this, it is necessary to ensure proper encoding of the data before loading it into the table. In Python, using the LOAD DATA LOCAL INFILE command to load a UTF-8 encoded CSV file requires including the CHARACTER SET UTF8 clause.

The following code snippet demonstrates how to load UTF-8 encoded data into a MySQL table using Python:

import mysql.connector

# Establish connection to MySQL
conn = mysql.connector.connect(...)

# Execute LOAD DATA query
query = "LOAD DATA INFILE 'file.csv' IGNORE INTO TABLE table CHARACTER SET UTF8 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\n'"
cursor = conn.cursor()
cursor.execute(query)
cursor.close()

# Commit changes
conn.commit()

By following these steps, users can successfully load UTF-8 encoded data into MySQL tables and preserve the non-English characters in their original form.