ios - 如何更改iOS中sql列的数据类型？

Question

在我的 iOS 应用中，我想更改数据库中的列数据类型。

ALTER TABLE XXX ALTER COLUMN myColumn INT.

我总是得到“接近 ALTER 语法错误”如何解决问题？感谢您的帮助。

Answer 1

您无法更改列类型。您可以创建一个新表，这次使用正确的列数据类型，然后从旧表中选择数据并将其插入到新表中。 ALTER TABLE documentation 中概述了完整的程序:

1. Remember the format of all indexes and triggers associated with table X. This information will be needed in step 7 below. One way to do this is to run a query like the following: SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.

2. Use CREATE TABLE to construct a new table "new_X" that is in the desired revised format of table X. Make sure that the name "new_X" does not collide with any existing table name, of course.

3. Transfer content from X into new_X using a statement like: INSERT INTO new_X SELECT ... FROM X.

4. If foreign key constraints are enabled, disable them using PRAGMA foreign_keys=OFF.

5. Drop the old table X: DROP TABLE X.

6. Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.

7. Use CREATE INDEX and CREATE TRIGGER to reconstruct indexes and triggers associated with table X. Perhaps use the old format of the triggers and indexes saved from step 1 above as a guide, making changes as appropriate for the alteration.

8. If foreign key constraints were originally enabled (prior to step 4) then run PRAGMA foreign_key_check to verify that the schema change did not break any foreign key constraints, and run PRAGMA foreign_keys=ON to re-enable foreign key constraints.

9. If any views refer to table X in a way that is affected by the schema change, then drop those views using DROP VIEW and recreate them with whatever changes are necessary to accommodate the schema change using CREATE VIEW.

注意，SQLite 使用 type affinity (列定义不会改变您插入表中的数据类型)。因此，如果您更改数据类型，您也需要更改数据。