SQLAlchemy and 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 。 請關注下面的 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’]
浙公網安備 33010602011771號