场景:影子表数据一致,写入行为却变了
离线处理程序为商品表创建一张影子表,核对行数后让后续任务向影子表写入。复制语句很简单:CREATE TABLE product_copy AS SELECT * FROM product。原表拒绝重复商品编码,复制表却接受了重复值;省略库存字段时,原来的默认零也变成了 NULL。
这里的故障点在建表方式。行数相同只能证明某一时刻的数据数量相同,不能证明两个表具有相同的写入规则。
本文针对 SQLite 普通表,使用 Python 标准库 sqlite3 在内存数据库中复现,不访问生产库。适用于离线快照加工、测试数据准备、影子表构建等场景。
先检查建表语句,再检查复制数量
对照下面两条查询:
SELECT name, sql FROM sqlite_schema
WHERE type = 'table' AND name IN ('product', 'product_copy');
PRAGMA table_info(product_copy);
PRAGMA index_list(product_copy);
第一条查看数据库实际保存的 DDL,检查 PRIMARY KEY、UNIQUE、NOT NULL、CHECK 和 DEFAULT 是否存在。table_info 中的 notnull、dflt_value、pk 分别帮助检查非空、默认值和主键;index_list 用于查看索引。不要只依赖 table_info,它不能完整展示 CHECK 等规则。
SQLite CREATE TABLE 官方文档说明,CREATE TABLE ... AS SELECT 创建的表没有主键和任何约束,列默认值均为 NULL。它会依据查询结果表达式推导列名与声明类型,并非复制原表定义。独立索引和触发器也需要单独处理。
这种写法适合只关心结果集的临时分析表。如果目标继续承担业务写入,就必须明确建立它自己的数据契约。
完整复现及修复测试
将下面代码保存为 test_table_copy.py,使用 Python 3.10 或以上版本运行 python test_table_copy.py。示例中的 sku 是商品编码,stock 是非负整数库存;固定表名属于程序代码,写入数据全部通过参数绑定。
import sqlite3
import unittest
from contextlib import closing
SOURCE_DDL = """
CREATE TABLE product (
id INTEGER PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
stock INTEGER NOT NULL DEFAULT 0
CHECK (typeof(stock) = 'integer' AND stock >= 0)
)
"""
COPY_DDL = """
CREATE TABLE product_copy (
id INTEGER PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
stock INTEGER NOT NULL DEFAULT 0
CHECK (typeof(stock) = 'integer' AND stock >= 0)
)
"""
def copy_products(connection: sqlite3.Connection) -> None:
"""在调用方事务内建立具备业务约束的副本,不负责提交。"""
connection.execute(COPY_DDL)
connection.execute(
"INSERT INTO product_copy (id, sku, stock) "
"SELECT id, sku, stock FROM product"
)
class TableCopyTests(unittest.TestCase):
"""验证复制结果和复制后的写入规则。"""
def setUp(self) -> None:
self.connection = sqlite3.connect(":memory:")
self.addCleanup(self.connection.close)
self.connection.execute(SOURCE_DDL)
self.connection.execute(
"INSERT INTO product (id, sku, stock) VALUES (?, ?, ?)",
(1, "SKU-A", 8),
)
self.connection.commit()
def create_copy(self) -> None:
"""明确开启事务,使建表和复制具有共同回滚边界。"""
with self.connection:
self.connection.execute("BEGIN")
copy_products(self.connection)
def test_fault_reproduction(self) -> None:
self.connection.execute(
"CREATE TABLE product_copy AS SELECT * FROM product"
)
self.connection.execute(
"INSERT INTO product_copy (id, sku, stock) VALUES (?, ?, ?)",
(1, "SKU-A", -3),
)
self.connection.execute(
"INSERT INTO product_copy (id, sku) VALUES (?, ?)", (2, None)
)
rows = self.connection.execute(
"SELECT id, sku, stock FROM product_copy ORDER BY rowid"
).fetchall()
self.assertEqual(rows, [(1, "SKU-A", 8), (1, "SKU-A", -3), (2, None, None)])
def test_rows_preserved(self) -> None:
self.create_copy()
rows = self.connection.execute(
"SELECT id, sku, stock FROM product_copy ORDER BY id"
).fetchall()
self.assertEqual(rows, [(1, "SKU-A", 8)])
def test_duplicate_keys_rejected(self) -> None:
self.create_copy()
for row in [(1, "SKU-B", 0), (2, "SKU-A", 0)]:
with self.subTest(row=row):
with self.assertRaises(sqlite3.IntegrityError):
self.connection.execute(
"INSERT INTO product_copy VALUES (?, ?, ?)", row
)
def test_invalid_values_rejected(self) -> None:
self.create_copy()
for row in [(2, None, 0), (2, "SKU-B", None), (2, "SKU-B", -1),
(2, "SKU-B", 1.5)]:
with self.subTest(row=row):
with self.assertRaises(sqlite3.IntegrityError):
self.connection.execute(
"INSERT INTO product_copy VALUES (?, ?, ?)", row
)
def test_default_stock(self) -> None:
self.create_copy()
self.connection.execute(
"INSERT INTO product_copy (id, sku) VALUES (?, ?)", (2, "SKU-B")
)
stock = self.connection.execute(
"SELECT stock FROM product_copy WHERE id = ?", (2,)
).fetchone()[0]
self.assertEqual(stock, 0)
def test_failed_copy_rolls_back_table(self) -> None:
with closing(sqlite3.connect(":memory:")) as connection:
connection.execute("CREATE TABLE product (id, sku, stock)")
connection.executemany(
"INSERT INTO product VALUES (?, ?, ?)",
[(1, "SKU-A", 0), (2, "SKU-A", 1)],
)
connection.commit()
with self.assertRaises(sqlite3.IntegrityError):
with connection:
connection.execute("BEGIN")
copy_products(connection)
exists = connection.execute(
"SELECT 1 FROM sqlite_schema WHERE type = 'table' AND name = ?",
("product_copy",),
).fetchone()
self.assertIsNone(exists)
if __name__ == "__main__":
unittest.main(verbosity=2)
第一项测试故意保留错误建表方式,证明重复主键、重复编码、负库存、NULL 都能进入副本。后续测试则约束修复后的行为:原数据保留,主键与编码去重,非空与库存检查生效,省略库存时补零。
typeof(stock) 检查的是 SQLite 经过类型亲和性转换后的存储类型。例如整数形式的文本可能被转换为整数并通过检查;若接口要求拒绝所有字符串输入,仍需在应用入口按原始类型校验。
最后一项模拟旧数据含重复编码。复制必须报错,并回滚新建表,不能用 INSERT OR IGNORE 静默丢弃行来“修好”迁移。示例显式执行 BEGIN,因为不能假设 Python 的连接上下文管理器在执行 DDL 前已经启动事务。函数不调用 commit,事务由调用者统一控制;已有事务的调用场景需使用现有事务或单独设计 SAVEPOINT,不能照搬重复 BEGIN。
上线处理:把副本视为一张新业务表
如果副本已经接受写入,先暂停其写入并保留备份,核对哪些数据必须保留。仅重跑建表 SQL 无法自动清理重复值,也不能把已丢失的默认值补回历史数据。
建议在受控迁移窗口执行以下步骤:
- 从版本管理中的 DDL 明确目标结构,逐项确认约束、索引、触发器与外键,不用替换表名字串的方式自动改写任意 DDL。
- 检查现有数据中的重复编码、NULL 和非法库存。发现问题时输出脱敏统计与业务定位键,按业务规则修复或隔离,不任意保留第一行。
- 在明确的事务边界内建新表,显式列出复制字段,再核对主键集合及业务字段。行数只作为辅助证据。
- 对照 DDL 和索引清单,并运行一次正常写入、重复写入、缺省字段和非法值测试。故意失败的探测写入必须回滚。
- 单独设计切换与回退流程,评估依赖表、视图、触发器、外键及读写进程;保留切换前备份,确认新表写入后的回退数据合并办法。
本文没有执行表重命名或生产切换。带外键、触发器、生成列、复杂索引或并发写入的库,需要完整迁移方案和恢复演练,不能直接把本例包装成通用迁移工具。
验证范围与结论
本例用六项 unittest 检验数据复制和约束行为。回归时可把 copy_products 恢复为 CREATE TABLE product_copy AS SELECT * FROM product,重复值、非法值、默认值以及复制失败断言应当失败;若仍全部通过,测试没有真正覆盖故障。
验证范围限于本机 Python/SQLite 内存数据库,不包含磁盘断电恢复、锁竞争、线上 ORM 或大表复制耗时。实际发布前还需在目标版本与数据量下确认锁持有时间、可用空间和备份恢复耗时。
复制查询结果和复制表结构是两个不同操作。业务表继续接受写入时,应先显式定义数据规则,再复制和核验数据;不要让一次行数核对替代结构验收。
Discussion
评论