django 1366, "Incorrect string value

在django settings.py文件中配置好了mariadb，在执行数据迁移时:

python3 manage.py makemigrations
python3 manage.py migrate

出现了如下报错:

Traceback (most recent call last):
  File "/Users/zzf/Documents/python_project/django/new_ssl_cert/venv/lib/python3.7/site-packages/django/db/backends/utils.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "/Users/zzf/Documents/python_project/django/new_ssl_cert/venv/lib/python3.7/site-packages/django/db/backends/mysql/base.py", line 71, in execute
    return self.cursor.execute(query, args)
  File "/Users/zzf/Documents/python_project/django/new_ssl_cert/venv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/Users/zzf/Documents/python_project/django/new_ssl_cert/venv/lib/python3.7/site-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/Users/zzf/Documents/python_project/django/new_ssl_cert/venv/lib/python3.7/site-packages/MySQLdb/connections.py", line 226, in query
    _mysql.connection.query(self, query)
MySQLdb._exceptions.OperationalError: (1366, "Incorrect string value: '\\xE5\\x9F\\x9F\\xE5\\x90\\x8D' for column `ssl_cert`.`auth_permission`.`name` at row 1")

原因:

mariadb数据库中字符编码的问题

进到maraidb中查看字符编码

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | latin1                     |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

可以看到现在使用的是latin1字符集，将其更改为: utf8mb4

因为是使用的docker启动的mariadb，所以重新创建一个新的mariadb容器即可。

docker run -d -e MYSQL_ROOT_PASSWORD=abcd123 -p 3306:3306 --name mariadb mariadb:10.4.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

再次查看字符编码:

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8mb4                    |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8mb4                    |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)

再次执行数据迁移

(venv) bash-3.2$ python3 manage.py makemigrations
Migrations for 'ssl_cert':
  apps/ssl_cert/migrations/0001_initial.py
    - Create model Domain
    - Create model SSLCertContent
    - Create model SubDomains
    - Create model SubSyncLimit
    - Add field ssl_cert_content to domain
Migrations for 'users':
  apps/users/migrations/0001_initial.py
    - Create model User
    - Create model ActivateCode
(venv) bash-3.2$ 
(venv) bash-3.2$ 
(venv) bash-3.2$ python3 manage.py migrate
Operations to perform:
  Apply all migrations: admin, auth, captcha, contenttypes, sessions, ssl_cert, users
Running migrations:
  Applying ssl_cert.0001_initial... OK
  Applying contenttypes.0001_initial... OK
  Applying contenttypes.0002_remove_content_type_name... OK
  Applying auth.0001_initial... OK
  Applying auth.0002_alter_permission_name_max_length... OK
  Applying auth.0003_alter_user_email_max_length... OK
  Applying auth.0004_alter_user_username_opts... OK
  Applying auth.0005_alter_user_last_login_null... OK
  Applying auth.0006_require_contenttypes_0002... OK
  Applying auth.0007_alter_validators_add_error_messages... OK
  Applying auth.0008_alter_user_username_max_length... OK
  Applying auth.0009_alter_user_last_name_max_length... OK
  Applying users.0001_initial... OK
  Applying admin.0001_initial... OK
  Applying admin.0002_logentry_remove_auto_add... OK
  Applying admin.0003_logentry_add_action_flag_choices... OK
  Applying captcha.0001_initial... OK
  Applying sessions.0001_initial... OK
(venv) bash-3.2$ 

命令执行正常，没有问题。

转载于:https://my.oschina.net/u/4153263/blog/3097866