MySQL BLOB列无默认值报错：插入图片提示‘storing字段无默认值’

我有一张cover表，想要向其中插入图片，但执行仅插入idx的INSERT语句时触发错误："Field 'storing' doesn't have a default value"。查看MySQL文档后得知BLOB类型不需要默认值，但问题依然存在。

表结构 #

mysql> show columns from cover;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| idx      | smallint     | NO   |     | NULL    |                |
| nomefile | varchar(255) | YES  |     | NULL    |                |
| sizefile | varchar(255) | YES  |     | NULL    |                |
| mimetype | varchar(255) | YES  |     | NULL    |                |
| storing  | blob         | NO   |     | NULL    |                |
| id       | smallint     | NO   | PRI | NULL    | auto_increment |
+----------+--------------+------+-----+---------+----------------+
6 rows in set (0,01 sec)

插入图片的Python代码 #

def add_image(self, idx):
    options = QFileDialog.Options()
    options |= QFileDialog.DontUseNativeDialog
    fileName, _ = QFileDialog.getOpenFileName(self, "Open Image", "/media/hdb1/", "Images (*.png *.xpm *.jpg)", options=options)
    query = "SELECT * FROM `cover` WHERE idx = %s " % (idx)
    if fileName:
        #This is just in case you need to manually delete the row in the table 'cover'
        conn, cursor = Functions.openDB(self, dbname)
        cursor.execute(query)
        nbrrows = cursor.rowcount
        if nbrrows == 0:
            query = "INSERT INTO `cover` (idx) VALUES (%s)" %(idx) # 错误触发位置
            cursor.execute(query)
            conn.commit()
        info = QFileInfo(fileName)
        size = info.size()
        base = info.completeBaseName()
        ext = info.completeSuffix()
        name = base + '.' +ext
        image = open(fileName, 'rb').read()  # open binary file in read mode
        conn, cursor = Functions.openDB(self, dbname)
        mime = 'image/jpeg'
        query = "UPDATE `cover` SET nomefile = %s, sizefile = %s, mimetype = %s, storing = %s WHERE idx = %s "
        
        try:
            cursor.execute(query,(fileName, size, mime, image, idx))
            conn.commit()
        except Error as e:
            Functions.handle_error(self, message=e)
            self.container.close()
            return
        Functions.message_handler(self,"Cover inserita correttamente")
        self.container.close()
    else:
        Functions.message_handler(self,"LEAVING EVERYTHING\nBYE BYE")
        self.container.close()

错误信息 #

Traceback (most recent call last):
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/connection_cext.py", line 661, in cmd_query
    self._cmysql.query(
_mysql_connector.MySQLInterfaceError: Field 'storing' doesn't have a default value

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/Projects/personal/library_read.py", line 259, in <lambda>
    image_btn.clicked.connect(lambda connect: self.add_image(idx))
  File "/Projects/personal/library_read.py", line 299, in add_image
    cursor.execute(query)
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/cursor_cext.py", line 374, in execute
    result = self._cnx.cmd_query(
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/opentelemetry/context_propagation.py", line 74, in wrapper
    return method(cnx, *args, **kwargs)
  File "/usr/local/lib/python3.10/dist-packages/mysql/connector/connection_cext.py", line 669, in cmd_query
    raise get_mysql_exception(
mysql.connector.errors.DatabaseError: 1364 (HY000): Field 'storing' doesn't have a default value

方案1：合并INSERT和UPDATE为单次INSERT操作 #

既然已经获取到图片数据，无需先插入空行再更新，直接一次性插入所有必填字段即可规避storing为空的问题。调整代码逻辑如下：

def add_image(self, idx):
    options = QFileDialog.Options()
    options |= QFileDialog.DontUseNativeDialog
    fileName, _ = QFileDialog.getOpenFileName(self, "Open Image", "/media/hdb1/", "Images (*.png *.xpm *.jpg)", options=options)
    if fileName:
        info = QFileInfo(fileName)
        size = info.size()
        image = open(fileName, 'rb').read()
        mime = 'image/jpeg'
        
        conn, cursor = Functions.openDB(self, dbname)
        query = "SELECT * FROM `cover` WHERE idx = %s " % (idx)
        cursor.execute(query)
        nbrrows = cursor.rowcount
        
        if nbrrows == 0:
            # 直接插入完整数据
            query = "INSERT INTO `cover` (idx, nomefile, sizefile, mimetype, storing) VALUES (%s, %s, %s, %s, %s)"
            cursor.execute(query,(idx, fileName, size, mime, image))
        else:
            # 已有数据则更新
            query = "UPDATE `cover` SET nomefile = %s, sizefile = %s, mimetype = %s, storing = %s WHERE idx = %s "
            cursor.execute(query,(fileName, size, mime, image, idx))
        
        conn.commit()
        Functions.message_handler(self,"Cover inserita correttamente")
        self.container.close()
    else:
        Functions.message_handler(self,"LEAVING EVERYTHING\nBYE BYE")
        self.container.close()

方案2：修改表结构，允许storing字段为NULL #

如果业务逻辑允许storing暂时为空，执行以下SQL修改表结构：

ALTER TABLE cover MODIFY COLUMN storing blob NULL;

修改后，先插入idx的操作可正常执行，后续再通过UPDATE填充storing字段。

方案3：给storing字段设置空BLOB默认值 #

手动给storing字段设置空BLOB作为默认值，避免INSERT时因字段为空报错：

ALTER TABLE cover MODIFY COLUMN storing blob NOT NULL DEFAULT '';

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