如何在不替换原有代码或新增分类的情况下更新SQL mapping表？

表mapping结构及数据如下：

尝试执行以下代码更新时，会直接覆盖原有所有代码，仅保留新值：

conn = sqlite3.connect('category_mapping.db')
c = conn.cursor()

query = f"UPDATE mapping SET codes = 9999 WHERE category = 'Bread'"
c.execute(query)

conn.commit()
conn.close()

改用INSERT语句时，会新增重复的Bread分类：

conn = sqlite3.connect('category_mapping.db')
c = conn.cursor()

query = f"INSERT INTO mapping (category, codes) VALUES ('Bread', 9999)"
c.execute(query)

conn.commit()
conn.close()

需要实现不删除现有代码、不新增分类的前提下，给指定分类追加新代码。

方法1：适配现有表结构追加代码 #

利用SQLite的字符串连接运算符||，在原有codes字段值后追加新代码，同时处理字段为空的边界情况：

conn = sqlite3.connect('category_mapping.db')
c = conn.cursor()

query = """
UPDATE mapping 
SET codes = CASE 
              WHEN codes IS NULL OR codes = '' THEN '9999'
              ELSE codes || ', 9999'
           END 
WHERE category = 'Bread'
"""
c.execute(query)

conn.commit()
conn.close()

执行后，Bread的codes会变为1234, 4453, 9999。

方法2：优化表结构（推荐） #

把多代码存于单个字段的设计拆分，新建关联表category_codes，结构如下：

这种结构下，新增代码只需执行INSERT，不会产生重复分类：

conn = sqlite3.connect('category_mapping.db')
c = conn.cursor()

query = "INSERT INTO category_codes (category, code) VALUES ('Bread', 9999)"
c.execute(query)

conn.commit()
conn.close()

后续查询分类下的所有代码时，用SELECT code FROM category_codes WHERE category = 'Bread'即可，数据维护更灵活。

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