Django多语言文本MySQL存储编码问题及Admin显示求助

Hey there, let's tackle this multi-language text storage issue step by step—this is super common when working with OpenGraph and MySQL, so I’ve got you covered.

一、正确在MySQL中存储多语言文本（最优方案） #

Storing raw bytes should be your last resort—let's fix the root character encoding problem first, which will make everything work seamlessly in your Admin panel and views.

1. Ensure your database/table/fields use utf8mb4 #

MySQL's standard utf8 only supports a subset of Unicode (it can't handle emojis or some rare characters often found in OpenGraph content). utf8mb4 is the full Unicode-compliant charset you need:

• Create a new database with the right charset:

  CREATE DATABASE your_project_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

• Update an existing database:

  ALTER DATABASE your_project_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

• Convert your target table:

  ALTER TABLE your_opengraph_table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

• Verify/set individual fields:
  Make sure fields storing OpenGraph data (like og_title, og_description) are set to VARCHAR/TEXT with utf8mb4:

  ALTER TABLE your_opengraph_table MODIFY COLUMN og_title VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  ALTER TABLE your_opengraph_table MODIFY COLUMN og_description TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
  

2. Force your app to use utf8mb4 when connecting to MySQL #

No matter what framework/language you're using, configure your database connection to use utf8mb4 to avoid encoding mismatches:

• Example for Django (settings.py):

  DATABASES = {
      'default': {
          'ENGINE': 'django.db.backends.mysql',
          'NAME': 'your_project_db',
          'USER': 'db_user',
          'PASSWORD': 'db_pass',
          'HOST': 'localhost',
          'PORT': '3306',
          'OPTIONS': {
              'charset': 'utf8mb4',
              'use_unicode': True,
          },
      }
  }
  

• Example for Python's mysql-connector:

  import mysql.connector
  conn = mysql.connector.connect(
      host="localhost",
      user="db_user",
      password="db_pass",
      database="your_project_db",
      charset="utf8mb4"
  )
  

3. Store text directly—no manual encoding needed #

Once the above steps are done, you can save the raw multi-language text from OpenGraph straight into the database. Your Admin panel and views will display everything correctly without extra work.

二、If you must store bytes (not recommended, but here's how to decode) #

If you're stuck with byte storage for some reason, you'll need to decode the bytes back to text whenever you display them.

1. Decode in your Admin panel (Django example) #

Customize your ModelAdmin to automatically decode byte fields for display:

from django.contrib import admin
from .models import OpenGraphEntry

class OpenGraphEntryAdmin(admin.ModelAdmin):
    list_display = ('id', 'decoded_title', 'decoded_description')
    
    def decoded_title(self, obj):
        # Decode the stored bytes back to UTF-8 text
        return obj.og_title_bytes.decode('utf-8')
    decoded_title.short_description = 'OpenGraph Title'
    
    def decoded_description(self, obj):
        return obj.og_description_bytes.decode('utf-8')
    decoded_description.short_description = 'OpenGraph Description'

admin.site.register(OpenGraphEntry, OpenGraphEntryAdmin)

2. Decode in your views/templates #

When fetching data for your frontend, decode the bytes before passing them to the template:

def opengraph_detail(request, entry_id):
    entry = OpenGraphEntry.objects.get(id=entry_id)
    # Decode bytes to text
    og_title = entry.og_title_bytes.decode('utf-8')
    og_description = entry.og_description_bytes.decode('utf-8')
    return render(request, 'detail.html', {
        'og_title': og_title,
        'og_description': og_description
    })

Critical note #

Stick to a single encoding (UTF-8 is the only safe choice) for all operations. Mixing encodings will guarantee messy, unreadable text.

内容的提问来源于stack exchange，提问作者Hman