尝试在mysql数据库中设置枚举数据类型时出现问题

Question

我想做什么？

Django不支持在mysql数据库中设置枚举数据类型。使用下面的代码，我尝试设置枚举数据类型

错误详细信息

_mysql.connection.query(self, query) django.db.utils.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL, created_at datetime(6) NOT NULL, user_id bigint NOT NULL)' at line 1")

我遗漏了什么吗？

包含所有选项的枚举类

class enumTokenTypes(models.TextChoices):
    Registration = "Registration"
    ForgotPassword = "Forgot Password"

模型中的用户令牌类

class tblusertokens(models.Model):
    token_id = AutoField(primary_key=True)
    token_type = EnumField(max_length=20, choices=enumTokenTypes.choices)
    created_at = DateTimeField(auto_now_add=True, blank=True)
    user = ForeignKey(tblusers, on_delete = models.CASCADE)    

迁移中的用户令牌创建模型

class EnumField(CharField):
    def db_type(self, connection):
        return "enum"


migrations.CreateModel(
    name='tblusertokens',
    fields=[
        ('token_id', models.AutoField(primary_key=True, serialize=False)),
        ('token_type', clientauth.models.EnumField(choices=[('Registration', 'Registration'), ('Forgot Password', 'Forgotpassword')], max_length=20)),
        ('created_at', models.DateTimeField(auto_now_add=True)),
        ('user', models.ForeignKey(on_delete=django.db.models.deletion.CASCADE, to='clientauth.tblusers')),
    ],
)

悬赏问题

为函数设置2个参数，以传递逗号分隔的值和默认值

Answer 1

数据类型应该是enum('Registration', 'Forgot Password')，而不仅仅是enum

class EnumField(CharField):

    def db_type(self, connection):
        if connection.vendor == 'mysql':
            return 'enum({0})'.format(','.join("'%s'" % value for value, label in self.choices))
        return super().db_type(connection)

参考：https://dev.mysql.com/doc/refman/8.0/en/enum.html

数据库默认值

虽然上面的MySQL 8.0文档中没有明确提到，但您也可以指定一个DB默认值

class EnumField(CharField):

    def __init__(self,  *args, **kwargs):
        self.db_default = kwargs.pop('db_default', None)
        super().__init__(*args, **kwargs)

    def db_type(self, connection):
        if connection.vendor == 'mysql':
            if self.db_default is not None:
                return "enum({0}) DEFAULT '{1}'".format(','.join("'%s'" % value for value, label in self.choices), self.db_default)
            return 'enum({0})'.format(','.join("'%s'" % value for value, label in self.choices))
        return super().db_type(connection)

    def deconstruct(self):
        name, path, args, kwargs = super().deconstruct()
        if self.db_default:
            kwargs['db_default'] = self.db_default
        return name, path, args, kwargs

用法：

token_type = EnumField(max_length=20, choices=enumTokenTypes.choices, db_default=enumTokenTypes.ForgotPassword)

关于deconstruct()方法

从https://docs.djangoproject.com/en/3.2/howto/custom-model-fields/#field-deconstruction：

The counterpoint to writing your __init__() method is writing the deconstruct() method. It’s used during model migrations to tell Django how to take an instance of your new field and reduce it to a serialized form - in particular, what arguments to pass to __init__() to re-create it.

If you add a new keyword argument, you need to write code in deconstruct() that puts its value into kwargs yourself.

Answer 2

对悬赏问题的回应

用于设置默认值：在EnumField中添加默认参数。在下面的示例中，我将enumTokenTypes注册设置为其默认值。通过示例查看Django documentation的enum实现

     class tblusertokens(models.Model):
          token_id = AutoField(primary_key=True)
          token_type = EnumField(max_length=20, choices=enumTokenTypes.choices, default=enumTokenTypes.Registration )
          created_at = DateTimeField(auto_now_add=True, blank=True)
          user = ForeignKey(tblusers, on_delete = models.CASCADE)    

Answer 3

您可以打印出该迁移的sql以查看具体的错误，但是定义db_type返回"enum"肯定不是正确的方法

    ('token_type', CharField(choices=enumTokenTypes.choices, max_length=22)),

由于某种原因，Enumeration types上的文档中推荐的语法对您不起作用吗