SQLAlchemy and MySQL TIMESTAMP

列的默认值
MySQL 的默认行为
调整默认行为的顺序
使用 default
MySQL 版本的限制

列的默认值sysvar_explicit_defaults_for_timestamp 。请关注下面的 updatetime Field ：

mysql> desc bonus;
+------------+------------+------+-----+-------------------+-----------------------------+
| Field      | Type       | Null | Key | Default           | Extra                       |
+------------+------------+------+-----+-------------------+-----------------------------+
| bid        | int(11)    | NO   | PRI | NULL              | auto_increment              |
| price      | int(11)    | NO   |     | 0                 |                             |
| share      | int(11)    | NO   |     | 0                 |                             |
| updatetime | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| createtime | timestamp  | NO   |     | CURRENT_TIMESTAMP |                             |
+------------+------------+------+-----+-------------------+-----------------------------+

然而，Extra 中包含 on update CURRENT_TIMESTAMP 的 Column，在每次更新该 Recored 的时候，updatetime 都会自动更新。

所以，如果需要给时间戳类型加入默认值，但不在每次更新的时候自动更新时间戳，可以这样做：

# 条目的更新时间。每次更新条目的时候，本字段会自动更新时间戳
updatetime = db.Column(db.TIMESTAMP(True), nullable=False)
# 条目的创建时间。每次更新条目的时候，本字段不会自动更新时间戳
createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
# 或者
createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('CURRENT_TIMESTAMP'))

调整默认行为的顺序SqlAlchemy TIMESTAMP ‘on update’ extra 中提到 on update CURRENT_TIMESTAMP 必须是第一个 TIMESTAMP 列。对这点我并不认同，经过测试，我的结论如下：

如果你希望通过设定非空让 MySQL 自动生成 on update CURRENT_TIMESTAMP ，则必须将该列作为第一个 TIMESTAMP 列。

class Bonus(db.Model):
    __tablename__ = 'bonus'
    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
    # 总充值
    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 总分红
    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 更新时间
    updatetime = db.Column(db.TIMESTAMP(True), nullable=False)
    # 创建时间
    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))

如果调换顺序如下：

class Bonus(db.Model):
    __tablename__ = 'bonus'
    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
    # 总充值
    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 总分红
    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 创建时间
    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
    # 更新时间
    updatetime = db.Column(db.TIMESTAMP(True), nullable=False)

会报错：

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1067, “Invalid default value for ‘updatetime’") [SQL: ‘\nCREATE TABLE bonus (\n\tbid INTEGER NOT NULL AUTO_INCREMENT, \n\tagent BIGINT NOT NULL, \n\tmaster BIGINT NOT NULL, \n\tprice INTEGER NOT NULL DEFAULT 0, \n\tshare INTEGER NOT NULL DEFAULT 0, \n\tcreatetime TIMESTAMP NOT NULL DEFAULT NOW(), \n\tupdatetime TIMESTAMP NOT NULL, \n\tPRIMARY KEY (bid), \n\tFOREIGN KEY(agent) REFERENCES account (gameuid), \n\tFOREIGN KEY(master) REFERENCES account (gameuid)\n)\n\n’]

如果你一定要把 updatetime 作为第二个 timestamp 列，可以这样做：

class Bonus(db.Model):
    __tablename__ = 'bonus'
    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
    # 总充值
    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 总分红
    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 创建时间
    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
    # 更新时间
    updatetime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'))

效果如下：

+------------+------------+------+-----+-------------------+-----------------------------+
| Field      | Type       | Null | Key | Default           | Extra                       |
+------------+------------+------+-----+-------------------+-----------------------------+
| bid        | int(11)    | NO   | PRI | NULL              | auto_increment              |
| agent      | bigint(20) | NO   | MUL | NULL              |                             |
| master     | bigint(20) | NO   | MUL | NULL              |                             |
| price      | int(11)    | NO   |     | 0                 |                             |
| share      | int(11)    | NO   |     | 0                 |                             |
| createtime | timestamp  | NO   |     | CURRENT_TIMESTAMP |                             |
| updatetime | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+------------+------------+------+-----+-------------------+-----------------------------+

使用 default

我们也可以把默认值设置为空，然后通过 SQLAlchemy Column 提供的 default 在 python 层面自动加入默认值：

class Bonus(db.Model):
    __tablename__ = 'bonus'
    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
    # 总充值
    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 总分红
    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 创建时间
    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))
    # 更新时间
    updatetime = db.Column(db.TIMESTAMP(True), nullable=True, default=func.utcnow())

效果如下。在这种情况下，MySQL 中没有设定 updatetime 的默认值，但是在给 Column 赋值的时候，python 会使用 utcnow 自动为其加入默认值。这是在 SQLAlchemy 层面实现的，并不是在 MySQL 中实现的。

+------------+------------+------+-----+-------------------+----------------+
| Field      | Type       | Null | Key | Default           | Extra          |
+------------+------------+------+-----+-------------------+----------------+
| bid        | int(11)    | NO   | PRI | NULL              | auto_increment |
| agent      | bigint(20) | NO   | MUL | NULL              |                |
| master     | bigint(20) | NO   | MUL | NULL              |                |
| price      | int(11)    | NO   |     | 0                 |                |
| share      | int(11)    | NO   |     | 0                 |                |
| createtime | timestamp  | NO   |     | CURRENT_TIMESTAMP |                |
| updatetime | timestamp  | YES  |     | NULL              |                |
+------------+------------+------+-----+-------------------+----------------+

MySQL 版本的限制

另外，很多文章提到了使用 server_default=text('0') 作为默认值。在 MySQL5.7上，这个默认值是不可用的：

class Bonus(db.Model):
    __tablename__ = 'bonus'
    bid = db.Column(db.INT, primary_key=True, autoincrement=True)
    # 总充值
    price = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 总分红
    share = db.Column(db.INTEGER, nullable=False, server_default=text('0'))
    # 更新时间
    updatetime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('0'))
    createtime = db.Column(db.TIMESTAMP(True), nullable=False, server_default=text('NOW()'))

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1067, “Invalid default value for ‘updatetime’") [SQL: ‘\nCREATE TABLE bonus (\n\tbid INTEGER NOT NULL AUTO_INCREMENT, \n\tagent BIGINT NOT NULL, \n\tmaster BIGINT NOT NULL, \n\tprice INTEGER NOT NULL DEFAULT 0, \n\tshare INTEGER NOT NULL DEFAULT 0, \n\tupdatetime TIMESTAMP NOT NULL DEFAULT 0, \n\tcreatetime TIMESTAMP NOT NULL DEFAULT NOW(), \n\tPRIMARY KEY (bid), \n\tFOREIGN KEY(agent) REFERENCES account (gameuid), \n\tFOREIGN KEY(master) REFERENCES account (gameuid)\n)\n\n’]

Python成为高手的100个模块

SQLAlchemy and MySQL TIMESTAMP

使用 default

MySQL 版本的限制

相关

标签