SQLite修改列
我需要修改 SQLite 数据库中的列，但由于数据库已经投入生产，所以我必须以编程方式执行此操作。根据我的研究，我发现为了做到这一点，我必须执行以下操作。
- 使用新模式创建新表
- 将数据从旧表复制到新表
- 删除旧表 将
- 新表重命名为旧表名称
对于应该相对容易的事情来说，这似乎是一个荒谬的工作量。难道就没有更简单的方法吗？我需要做的就是更改现有列的约束并为其指定默认值。
I need to modify a column in a SQLite database but I have to do it programatically due to the database already being in production. From my research I have found that in order to do this I must do the following.
- Create a new table with new schema
- Copy data from old table to new table
- Drop old table
- Rename new table to old tables name
That seems like a ridiculous amount of work for something that should be relatively easy. Is there not an easier way? All I need to do is change a constraint on a existing column and give it a default value.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
这是 SQLite 众所周知的缺点之一（
ALTER TABLE不支持MODIFY COLUMN），但它位于 SQLite 未实现的 SQL 功能列表。编辑：删除了提到它可能在未来版本中受到支持的位，因为页面已更新以表明情况不再如此
That's one of the better-known drawbacks of SQLite (no
MODIFY COLUMNsupport onALTER TABLE), but it's on the list of SQL features that SQLite does not implement.edit: Removed bit that mentioned it may being supported in a future release as the page was updated to indicate that is no longer the case
如果修改不是太大（例如更改varchar的长度），您可以转储db，手动编辑数据库定义并再次导入回来：
然后用文本编辑器打开文件，搜索您想要的定义修改然后：
If the modification is not too big (e.g. change the length of a varchar), you can dump the db, manually edit the database definition and import it back again:
then open the file with a text editor, search for the definition you want to modify and then:
正如此处所述，SQLite 并未实现这些功能。
作为旁注，您可以通过使用 select 创建表来完成前两个步骤：
As said here, these kind of features are not implemented by SQLite.
As a side note, you could make your two first steps with a create table with select:
当我运行“CREATE TABLE tmp_table AS SELECT id, name FROM src_table”时，我丢失了所有列类型格式（例如，时间字段变成整数字段
正如最初所述，似乎应该更容易，但这是我所做的修复我遇到了这个问题，因为我想更改列中的 Not Null 字段，而 Sqlite 并没有真正帮助我
使用“SQLite Manager”Firefox 插件浏览器（我通过复制创建了新表）。旧的创建语句，进行修改并执行它，然后为了复制数据，我只需突出显示这些行，右键单击“将行复制为 SQL”，将“someTable”替换为我的表名称，然后执行了SQL。
When I ran "CREATE TABLE tmp_table AS SELECT id, name FROM src_table", I lost all the column type formatting (e.g., time field turned into a integer field
As initially stated seems like it should be easier, but here is what I did to fix. I had this problem b/c I wanted to change the Not Null field in a column and Sqlite doesnt really help there.
Using the 'SQLite Manager' Firefox addon browser (use what you like). I created the new table by copying the old create statement, made my modification, and executed it. Then to get the data copied over, I just highlighted the rows, R-click 'Copy Row(s) as SQL', replaced "someTable" with my table name, and executed the SQL.
对于这个问题已经给出了各种好的答案，但我还建议您查看 sqlite.org 页面 < code>ALTER TABLE 详细介绍了这个问题：可以对列进行哪些（少数）更改（
RENAME|ADD|DROP），还有制作其他类型的表部分中其他操作的详细解决方法为什么 ALTER TABLE 对于 SQLite 来说是一个问题和背景信息>。特别是，解决方法指出了使用更复杂的表时的一些陷阱，并解释了如何安全地进行更改。Various good answers already given to this question, but I also suggest taking a look at the sqlite.org page on
ALTER TABLEwhich covers this issue in some detail: What (few) changes are possible to columns (RENAME|ADD|DROP) but also detailed workarounds for other operations in the section Making Other Kinds Of Table Schema Changes and background info in Why ALTER TABLE is such a problem for SQLite. In particular the workarounds point out some pitfalls when working with more complex tables and explain how to make changes safely.