SQLAlchemy and MySQL TIMESTAMP使用 SQLAlchemy 处理 MySQL 上的 TIMESTAMP 类型的一点小技巧

忘是亡心i 2022-10-07 05:59 154阅读 0赞

[SQLAlchemy][] 是一个功能强大的 [ORM][] 。本篇介绍使用 SQLAlchemy 处理 MySQL 上的 TIMESTAMP 类型的一点小技巧，很冷门的知识点哦。

## 列的默认值 ##

注意，在使用 `db.create_all()` 进行初始化创建表的时候，如果为 Column 指定了 default 的值，并不会影响创建的表中的对应列的默认值。这些 default 的值仅仅是在使用 SQLAlchemy 系统插入值的时候会提供默认值。如果你希望影响 MySQL 中 Column 的默认值，必须使用 `server_default` 来指定。

例如要设置一个 Colmun 默认值为0 ，则需要设定 `server_default=text('0')` 。

## MySQL 的默认行为 ##

使用下面的代码创建一个默认值不为空的 TIMESTAMP Column ：

updatetime = db.Column(db.TIMESTAMP(True), nullable=False)

如果对一个 TIMESTAMP Column 使用 `nullable=False` ，MySQL 会自动加入`on update CURRENT_TIMESTAMP` 。这是 MySQL 的默认行为：[sysvar\_explicit\_defaults\_for\_timestamp][sysvar_explicit_defaults_for_timestamp] 。 请关注下面的 updatetime Field ：

mysql> desc bonus;

+------------+------------+------+-----+-------------------+-----------------------------+

+------------+------------+------+-----+-------------------+-----------------------------+

| bid        | int(11)    | NO   | PRI | NULL              | auto_increment              |

| price      | int(11)    | NO   |     | 0                 |                             |

| share      | int(11)    | NO   |     | 0                 |                             |

+------------+------------+------+-----+-------------------+-----------------------------+

...

然而，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][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()'))

...

效果和 [上面提到的][Link 1] 相同。

如果调换顺序如下：

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'))

...

效果如下：

+------------+------------+------+-----+-------------------+-----------------------------+

+------------+------------+------+-----+-------------------+-----------------------------+

| bid        | int(11)    | NO   | PRI | NULL              | auto_increment              |

| price      | int(11)    | NO   |     | 0                 |                             |

| share      | int(11)    | NO   |     | 0                 |                             |

+------------+------------+------+-----+-------------------+-----------------------------+

...

## 使用 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 中实现的。

+------------+------------+------+-----+-------------------+----------------+

+------------+------------+------+-----+-------------------+----------------+

| bid        | int(11)    | NO   | PRI | NULL              | auto_increment |

| price      | int(11)    | NO   |     | 0                 |                |

| share      | int(11)    | NO   |     | 0                 |                |

+------------+------------+------+-----+-------------------+----------------+

...

## 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'\]

## 参考文章 ##

*  [How do you get SQLAlchemy to override MySQL “on update CURRENT\_TIMESTAMP][How do you get SQLAlchemy to override MySQL _on update CURRENT_TIMESTAMP]
 *  [SqlAlchemy TIMESTAMP 'on update' extra][SqlAlchemy TIMESTAMP _on update_ extra]
 *  [sysvar\_explicit\_defaults\_for\_timestamp][sysvar_explicit_defaults_for_timestamp]

*  **文章ID：**2628
 *  **原文作者：**[zrong][]
 *  **原文链接：**[https://blog.zengrong.net/post/salqlchemy-and-timestamp/][https_blog.zengrong.net_post_salqlchemy-and-timestamp]
 *  **版权声明：**本作品采用 [署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0)][-_-_ 4.0 _ _CC BY-NC-SA 4.0] 进行许可，非商业转载请注明出处（原文作者，原文链接），商业转载请联系作者获得授权。

[SQLAlchemy]: http://www.sqlalchemy.org/
[ORM]: https://en.wikipedia.org/wiki/Object-relational_mapping
[sysvar_explicit_defaults_for_timestamp]: https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_explicit_defaults_for_timestamp
[SqlAlchemy TIMESTAMP _on update_ extra]: http://stackoverflow.com/a/33744478/1542345
[Link 1]: https://blog.zengrong.net/post/salqlchemy-and-timestamp/#descbonus
[How do you get SQLAlchemy to override MySQL _on update CURRENT_TIMESTAMP]: http://stackoverflow.com/a/2454848/1542345
[zrong]: https://blog.zengrong.net/
[https_blog.zengrong.net_post_salqlchemy-and-timestamp]: https://blog.zengrong.net/post/salqlchemy-and-timestamp/
[-_-_ 4.0 _ _CC BY-NC-SA 4.0]: https://creativecommons.org/licenses/by-nc-sa/4.0/deed.zh