面试题：SQLite修改表结构时的数据迁移处理

Question

在SQLite中，若要将一张名为'user_info'的表的'phone'列的数据类型从TEXT改为INTEGER，且该表中有大量数据。请描述整个修改表结构的步骤，包括如何处理数据迁移以确保数据的完整性和正确性，同时写出对应的SQL语句。

Answer 1

1. 创建临时表：
   • 创建一个与user_info表结构类似的临时表，除了phone列的数据类型为INTEGER。

   CREATE TABLE user_info_temp (
       -- 假设表中还有其他列，如id和name，这里一并列出
       id INTEGER PRIMARY KEY,
       name TEXT,
       phone INTEGER
   );
   

2. 迁移数据：
   • 从原user_info表中读取数据，转换phone列的数据类型后插入到临时表中。这里假设phone列中的数据都是合法的整数形式的字符串，如果有非数字字符串可能需要额外处理。

   INSERT INTO user_info_temp (id, name, phone)
   SELECT id, name, CAST(phone AS INTEGER)
   FROM user_info;
   

3. 删除原表：

   DROP TABLE user_info;
   

4. 重命名临时表：

   ALTER TABLE user_info_temp RENAME TO user_info;
   

在上述步骤中，通过创建临时表并迁移数据，确保了在修改phone列数据类型时数据的完整性和正确性。但要注意，CAST(phone AS INTEGER)前提是phone列中的数据都可以正确转换为INTEGER类型，如果存在不能转换的情况，需要先进行数据清理或特殊处理。