比较两个SQLite表的差异

-- 获取两个表的schema
PRAGMA table_info(table1);
PRAGMA table_info(table2);

-- 比较schema
SELECT * FROM pragma_table_info(table1) EXCEPT SELECT * FROM pragma_table_info(table2);
SELECT * FROM pragma_table_info(table2) EXCEPT SELECT * FROM pragma_table_info(table1);

-- 使用EXCEPT操作符比较数据
SELECT * FROM table1 EXCEPT SELECT * FROM table2;
SELECT * FROM table2 EXCEPT SELECT * FROM table1;

-- 使用JOIN操作符比较数据
SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL OR t1.column <> t2.column;

-- 获取两个表的索引信息
SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='table1';
SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='table2';

-- 比较索引
SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='table1' EXCEPT SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='table2';
SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='table2' EXCEPT SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='table1';