将行复制到另一个表时，删除或转换复制的值

Question

我正在迁移一个数据库。我的一个列类型是从TEXT更改为INTEGER。

我的策略是重命名旧表并创建一个新表，然后将旧表的行插入到新表中。

假设我的新桌子是这样的：

CREATE TABLE Item (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
count INTEGER NOT NULL DEFAULT '0'
)

我传输数据的代码可能如下所示：

INSERT INTO Item (id, count)
SELECT id, count FROM old_Item

我最初将计数定义为TEXT，而我可能应该将其定义为INTEGER。由于动态键入，只要count条目可以作为整数进行解析，我希望这是可以的，但如果不花大量时间查看十年前的许多旧版本的应用程序，我无法保证在count列中输入了整数以外的内容。

当旧表中的列具有无效的TEXT值时，是否有方法将该列替换为某个默认整数值？

我知道，如果我在语句中使用ON CONFLICT REPLACE(或者是UPSERT ON CONFLICT REPLACE？(，NULL值可以自动替换为默认值，但我不知道这会如何影响不匹配的类型。

还是我需要用困难的方法，而不是使用SELECT，查询旧表，用Java/Kotlin手动解析返回的Cursor的每一行，然后逐行插入它们？

Answer 1

您可以使用CAST函数

SELECT id, CAST(count AS int) FROM old_Item  

如果值为文本，则CAST的结果将为0。如果值为实数，则将转换为整数

例如

INSERT INTO old_Item (count) values ('2');
INSERT INTO old_Item (count) values ('5.03');
INSERT INTO old_Item (count) values ('tXt22');
INSERT INTO old_Item (count) values (' 5.8');
INSERT INTO old_Item (count) values ('0x533');

SELECT CAST(count as INT) from old_Item的结果将是

2
5
0
5
0

所以，在你的情况下，你可以

进行查询

INSERT INTO Item SELECT id, CAST(count as int) FROM old_Item

如果无法将其识别为int，则值将为0。如果你想将0替换为某个值，你需要使这个查询

select id, 
(case when count_int = 0 then your_default_value else count_int end) 
from (select id, cast(count as integer) count_int from old_Item);

CAST函数如何在sqllite中工作的完整解释您可以在这里找到

https://sqlite.org/lang_expr.html#castexpr

类型名称转换处理中的文本-INTEGER