如何重命名 SQLite 数据库表中的列?

2021-11-27 00:00:00 sqlite alter-table

我需要重命名 SQLite 数据库中某些表中的几列.我知道一个 图片来源:https://www.sqlite.org/images/syntax/alter-table-stmt.gif

示例:

CREATE TABLE tab AS SELECT 1 AS c;选择 * 从选项卡;ALTER TABLE 选项卡将 COLUMN c 重命名为 c_new;选择 * 从选项卡;

db-fiddle.com 演示

<小时>

安卓支持

在撰写本文时,Android 的 API 27 正在使用 SQLite 包3.19 版.

基于 Android 正在使用的当前版本以及此更新将在 SQLite 3.25.0 版中推出,我想说您需要等待一段时间(大约 API 33)才能将此支持添加到 Android.

而且,即便如此,如果您需要支持 API 33 之前的任何版本,您将无法使用它.

I would need to rename a few columns in some tables in a SQLite database. I know that a similar question has been asked on stackoverflow previously, but it was for SQL in general, and the case of SQLite was not mentioned.

From the SQLite documentation for ALTER TABLE, I gather that it's not possible to do such a thing "easily" (i.e. a single ALTER TABLE statement).

I was wondering someone knew of a generic SQL way of doing such a thing with SQLite.

解决方案

This was just fixed with 2018-09-15 (3.25.0)

Enhancements the ALTER TABLE command:

  • Add support for renaming columns within a table using ALTER TABLE table RENAME COLUMN oldname TO newname.
  • Fix table rename feature so that it also updates references to the renamed table in triggers and views.

You can find the new syntax documented under ALTER TABLE

The RENAME COLUMN TO syntax changes the column-name of table table-name into new-column-name. The column name is changed both within the table definition itself and also within all indexes, triggers, and views that reference the column. If the column name change would result in a semantic ambiguity in a trigger or view, then the RENAME COLUMN fails with an error and no changes are applied.

Image source: https://www.sqlite.org/images/syntax/alter-table-stmt.gif

Example:

CREATE TABLE tab AS SELECT 1 AS c;

SELECT * FROM tab;

ALTER TABLE tab RENAME COLUMN c to c_new;

SELECT * FROM tab;

db-fiddle.com demo


Android Support

As of writing, Android's API 27 is using SQLite package version 3.19.

Based on the current version that Android is using and that this update is coming in version 3.25.0 of SQLite, I would say you have bit of a wait (approximately API 33) before support for this is added to Android.

And, even then, if you need to support any versions older than the API 33, you will not be able to use this.

相关文章