如何配置Django使其支持MySQL的bulk_create upsert功能？

问题背景 #

数据库表结构 #

CREATE TABLE `extras` (
  `date` date NOT NULL,
  `code` varchar(11) NOT NULL,
  `is_st` tinyint NOT NULL,
  UNIQUE KEY `fk_date_code` (`date`,`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

已安装依赖包 #

# pip list installed
Package                      Version
---------------------------- ------------
asgiref                      3.7.2
Django                       4.2.5
django-bulk-update-or-create 0.3.0
djangorestframework          3.14.0
pip                          21.2.3
PyMySQL                      1.1.0
pytz                         2023.3.post1
setuptools                   53.0.0
sqlparse                     0.4.4
typing_extensions            4.7.1

问题现象 #

手动执行MySQL UPSERT语句可正常运行：

INSERT INTO extras (`date`, `code`, `is_st`)
VALUES ('2013-01-04', '000001.XSHE', 0)
ON DUPLICATE KEY UPDATE
is_st = 1;

但调用Django的MyModel.objects.bulk_create()时触发错误：

File "env/lib64/python3.9/site-packages/django/db/models/manager.py", line 87, in manager_method
  return getattr(self.get_queryset(), name)(*args, **kwargs)
File "env/lib64/python3.9/site-packages/django/db/models/query.py", line 773, in bulk_create
  on_conflict = self._check_bulk_create_options(
File "env/lib64/python3.9/site-packages/django/db/models/query.py", line 696, in _check_bulk_create_options
  raise NotSupportedError(
django.db.utils.NotSupportedError: This database backend does not support updating conflicts with specifying unique fields that can trigger the upsert.

排查过程 #

通过搜索Django源码：

find env/lib64/python3.9/site-packages/django -type f | xargs grep "supports_update_conflicts_with_target"

结果显示PostgreSQL、SQLite的后端均配置了supports_update_conflicts_with_target属性，但MySQL的features.py中无此配置，默认继承base层的False值，导致Django判定MySQL不支持指定唯一键的UPSERT更新。

解决方案 #

方案1：使用已安装的第三方包django-bulk-update-or-create

该包专门解决Django批量UPSERT场景，用法示例：

from django_bulk_update_or_create import BulkUpdateOrCreateQuerySet
from django.db import models

class ExtrasModel(models.Model):
    date = models.DateField()
    code = models.CharField(max_length=11)
    is_st = models.BooleanField()

    class Meta:
        unique_together = ('date', 'code')
        managed = False
        db_table = 'extras'

    objects = BulkUpdateOrCreateQuerySet.as_manager()

# 批量操作示例
objs = [
    ExtrasModel(date='2013-01-04', code='000001.XSHE', is_st=0),
    # 其他待处理对象...
]
ExtrasModel.objects.bulk_update_or_create(
    objs, 
    unique_fields=['date', 'code'], 
    update_fields=['is_st']
)

方案2：临时修改Django MySQL后端配置（不推荐）

找到env/lib64/python3.9/site-packages/django/db/backends/mysql/features.py，在DatabaseFeatures类中添加属性：

class DatabaseFeatures(BaseDatabaseFeatures):
    # 保留原有属性...
    supports_update_conflicts_with_target = True

注意：此修改会在Django版本升级时被覆盖，仅适合临时测试场景。

方案3：执行原生批量SQL

手动构造批量UPSERT语句，通过Django cursor执行（推荐参数化查询避免注入）：

from django.db import connection

def bulk_upsert_extras(items):
    # items为包含(date, code, is_st)的列表
    placeholders = ", ".join(["(%s, %s, %s)"] * len(items))
    sql = """
        INSERT INTO extras (`date`, `code`, `is_st`)
        VALUES {placeholders}
        ON DUPLICATE KEY UPDATE
        is_st = VALUES(is_st);
    """.format(placeholders=placeholders)
    
    params = []
    for d, c, s in items:
        params.extend([d, c, s])
    
    with connection.cursor() as cursor:
        cursor.execute(sql, params)

# 使用示例
bulk_upsert_extras([
    ('2013-01-04', '000001.XSHE', 1),
    # 其他记录...
])

方案4：循环调用update_or_create（仅适合小数据量）

如果数据量较小，可逐个处理：

for item in items:
    ExtrasModel.objects.update_or_create(
        date=item['date'],
        code=item['code'],
        defaults={'is_st': item['is_st']}
    )

此方法会产生大量SQL请求，不适合大数据量场景。

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