How to Fix \'Incorrect String Value\' Errors When Saving Unicode in Django and MySQL?

Incorrect String Value Error When Saving Unicode Characters in MySQL with Django

When attempting to save unicode strings to the auth_user model in Django, you may encounter the following error message:

Incorrect string value: '\xC4\x8Dius' for column 'last_name' at row 104

Potential Causes

This error is typically caused by the following:

• Character set limitations: MySQL has a 3-byte limit for UTF-8 characters.
• Database and table settings: The database, table, and columns must use the utf8mb4 character set to support 4-byte unicode characters.

Solution

To resolve this issue, follow these steps:

1. Change the database, table, and columns to use the utf8mb4 character set:

   • Run the following query to create a new database with the utf8mb4 character set:

   CREATE DATABASE db_name CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';

   • Use the ALTER TABLE statement to modify the existing tables and columns:

   ALTER TABLE auth_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

2. Specify the character set in Django settings:

   In the Django settings file (settings.py), add the following line:

   DATABASES = {
       'default': {
           'ENGINE':'django.db.backends.mysql',
           ...
           'OPTIONS': {'charset': 'utf8mb4'},
       }
   }

Additional Considerations

• If you have existing CharField columns with a max length of 255 and an index on them, you will need to reduce the max length to 191 to avoid the "Specified key was too long" error.
• Alternatively, you can edit your MySQL configuration to remove the character length restriction. However, this requires additional modifications to Django.
• If possible, consider switching to PostgreSQL as it supports up to 4-byte unicode characters in UTF-8 without these restrictions.

The above is the detailed content of How to Fix \'Incorrect String Value\' Errors When Saving Unicode in Django and MySQL?. For more information, please follow other related articles on the PHP Chinese website!