Jak zmienić nazwę kolumny w tabeli bazy danych SQLite?


296

Musiałbym zmienić nazwę kilku kolumn w niektórych tabelach w bazie danych SQLite. Wiem, że podobne pytanie zadawano wcześniej przy przepełnieniu stosu, ale ogólnie dotyczyło ono SQL, a sprawa SQLite nie została wspomniana.

Z dokumentacji SQLite dla ALTER TABLE wynika, że ​​nie jest możliwe zrobienie czegoś takiego „łatwo” (tj. Pojedyncza instrukcja ALTER TABLE).

Zastanawiałem się, czy ktoś wiedział o ogólnym sposobie zrobienia czegoś takiego z SQLite.


Możesz to zrobić za pomocą przeglądarki db dla sqlite dość łatwo
Matt G

1
Proszę rozważyć zaznaczenie tej odpowiedzi jako zaakceptowaną stackoverflow.com/a/52346199/124486
Evan Carroll

Odpowiedzi:


66

Zostało to naprawione za pomocą 15.09.2018 (3.25.0)

Ulepsza ALTER TABLEpolecenie:

  • Dodaj obsługę zmiany nazw kolumn w tabeli za pomocą ALTER TABLEtabeli RENAME COLUMN oldname TO newname.
  • Napraw funkcję zmiany nazwy tabeli, aby aktualizowała także odwołania do tabeli o zmienionej nazwie w wyzwalaczach i widokach.

Możesz znaleźć nową składnię udokumentowaną pod ALTER TABLE

RENAME COLUMN TOSkładnia zmienia nazwę kolumny stół-name na nową-nazwa-kolumny. Nazwa kolumny jest zmieniana zarówno w samej definicji tabeli, jak i we wszystkich indeksach, wyzwalaczach i widokach, które odwołują się do kolumny. Jeśli zmiana nazwy kolumny spowodowałaby niejednoznaczność semantyczną w wyzwalaczu lub widoku, RENAME COLUMNbłąd kończy się niepowodzeniem i żadne zmiany nie są stosowane.

wprowadź opis zdjęcia tutaj Źródło obrazu: https://www.sqlite.org/images/syntax/alter-table-stmt.gif

Przykład:

CREATE TABLE tab AS SELECT 1 AS c;

SELECT * FROM tab;

ALTER TABLE tab RENAME COLUMN c to c_new;

SELECT * FROM tab;

demo db-fiddle.com


Wsparcie dla systemu Android

W chwili pisania API Androida 27 używa pakietu SQLite w wersji 3.19 .

W oparciu o bieżącą wersję używaną przez Androida i tę aktualizację nadchodzi w wersji 3.25.0 SQLite, powiedziałbym, że masz trochę czasu (około API 33), zanim wsparcie dla tego zostanie dodane do Androida.

I nawet wtedy, jeśli potrzebujesz obsługiwać wersje starsze niż API 33, nie będziesz mógł tego użyć.


8
Wdrażam migrację Androida i niestety IntelliJ wyświetla ostrzeżenie, że to nie jest poprawne polecenie SQL. database.execSQL("ALTER TABLE content RENAME COLUMN archiveCount TO dismissCount"). COLUM jest podświetlone na czerwono i jest napisane TO oczekiwano, otrzymał „COLUMN” . Niestety Android wciąż działa na SQLite w wersji 3.19 i dlatego to nie działa dla mnie.
Adam Hurwitz

1
edytowane: znalazłem na system.data.sqlite.org/index.html/doc/trunk/www/faq.wiki#q1 , że 1.0.109.x) faktycznie używa SQLite 3.24 i System.Data.SQLite użycie SQLite 3.25 jest udostępnione w tym miesiącu.
rychlmoj

1
Do Twojej wiadomości, niestety, musi to zostać jeszcze zaimplementowane przez bibliotekę SQLite Androida . Mam nadzieję, że wkrótce się zaktualizują.
Adam Hurwitz

3
Dodałem sekcję dotyczącą obsługi systemu Android, aby inni nie mogli pokładać nadziei. W oparciu o aktualne użycie SQLite 3.19 w systemie Android 27, będziemy musieli poczekać do około API 33, zanim ta funkcja zostanie dodana do Androida, a nawet wtedy będzie obsługiwana tylko w najnowszych wersjach. Westchnienie.
Joshua Pinter,

1
@JoshuaPinter Dzięki za rozszerzenie mojej odpowiedzi.
Łukasz Szozda

448

Załóżmy, że masz tabelę i musisz zmienić nazwę „colb” na „col_b”:

Najpierw zmień nazwę starej tabeli:

ALTER TABLE orig_table_name RENAME TO tmp_table_name;

Następnie utwórz nową tabelę na podstawie starej tabeli, ale ze zaktualizowaną nazwą kolumny:

CREATE TABLE orig_table_name (
  col_a INT
, col_b INT
);

Następnie skopiuj zawartość z oryginalnej tabeli.

INSERT INTO orig_table_name(col_a, col_b)
SELECT col_a, colb
FROM tmp_table_name;

Na koniec upuść stary stół.

DROP TABLE tmp_table_name;

Owinięcie tego wszystkiego w BEGIN TRANSACTION;i COMMIT;jest prawdopodobnie dobrym pomysłem.


51
I nie zapomnij swoich wskaźników.
Tom Mayfield

11
Bardzo ważne jest, że w powyższym przykładowym kodzie brakuje transakcji. Powinieneś zawinąć całość w BEGIN / END (lub ROLLBACK), aby upewnić się, że zmiana nazwy zakończy się pomyślnie lub wcale.
Roger Binns

4
Każdy, kto chce to zrobić w Androidzie,
bmaupin

7
W odpowiedzi w kodzie nic nie kopiuje indeksów. Utworzenie pustej tabeli i umieszczenie w niej danych tylko kopiuje strukturę i dane. Jeśli chcesz metadanych (indeksy, klucze obce, ograniczenia itp.), Musisz także wydać instrukcje, aby utworzyć je w zastąpionej tabeli.
Tom Mayfield

17
.schemaPolecenie SQLite jest przydatne do wyświetlania CREATE TABLEinstrukcji tworzących istniejącą tabelę. Możesz pobrać dane wyjściowe, zmodyfikować w razie potrzeby i wykonać je, aby utworzyć nową tabelę. To polecenie pokazuje także niezbędne CREATE INDEXpolecenia do utworzenia indeksów, które powinny obejmować obawy Thomasa. Oczywiście, pamiętaj, aby uruchomić to polecenie, zanim cokolwiek zmienisz.
Mike DeSimone

56

Przekopując się, znalazłem to wieloplatformowe (Linux | Mac | Windows) narzędzie graficzne o nazwie DB Browser dla SQLite, które pozwala na zmianę nazwy kolumn w bardzo przyjazny dla użytkownika sposób!

Edytuj | Zmień tabelę | Wybierz tabelę | Edytuj pole. Kliknij kliknij! Voila!

Jednakże, jeśli ktoś chce podzielić się programowym sposobem zrobienia tego, chętnie się dowiem!


1
Istnieje również dodatek do przeglądarki Firefox, który robi to samo. Kliknij prawym przyciskiem myszy kolumnę, której nazwę chcesz zmienić, i wybierz „Edytuj kolumnę”.
Jacob Hacker

1
Nawet w openSUSE jest dostępny jako pakiet: software.opensuse.org/package/sqlitebrowser

To dziwne, że ma tyle głosów. Mówimy tutaj o programowaniu (kod). Dlaczego w ogóle opublikowałeś tutaj tę odpowiedź?
użytkownik25

2
W moim pytaniu nie ma wzmianki o tym, jak to zrobić za pomocą kodu. Chciałem tylko wiedzieć, jak zmienić nazwę kolumny w SQLite DB.
joce

@joce Kocham cię !!! (jak brat) zmusił mnie do zmiany pola, voila. Wyeksportowałem tabelę MS Access do SQLite, a jedno z nich miało przed sobą cyfrę: 3YearLetterSent. Program Visual Studio utworzył klasę ze stołu, ale zadusił się na cyfrze „3” z przodu nazwy pola. Wiem to, po prostu nie oglądałem.
JustJohn

53

Chociaż prawdą jest, że nie ma ZMIEŃ KOLUMNĘ, jeśli chcesz tylko zmienić nazwę kolumny, usunąć ograniczenie NOT NULL lub zmienić typ danych, możesz użyć następującego zestawu poleceń:

Uwaga: te polecenia mogą uszkodzić bazę danych, więc upewnij się, że masz kopię zapasową

PRAGMA writable_schema = 1;
UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE BOOKS ( title TEXT NOT NULL, publication_date TEXT)' WHERE NAME = 'BOOKS';
PRAGMA writable_schema = 0;

Będziesz musiał zamknąć i ponownie otworzyć połączenie lub odkurzyć bazę danych, aby ponownie załadować zmiany do schematu.

Na przykład:

Y:\> sqlite3 booktest  
SQLite version 3.7.4  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> create table BOOKS ( title TEXT NOT NULL, publication_date TEXT NOT NULL);  
sqlite> insert into BOOKS VALUES ("NULLTEST",null);  
Error: BOOKS.publication_date may not be NULL  
sqlite> PRAGMA writable_schema = 1; 
sqlite> UPDATE SQLITE_MASTER SET SQL = 'CREATE TABLE BOOKS ( title TEXT NOT NULL, publication_date TEXT)' WHERE NAME = 'BOOKS';  
sqlite> PRAGMA writable_schema = 0;  
sqlite> .q  

Y:\> sqlite3 booktest  
SQLite version 3.7.4  
Enter ".help" for instructions  
Enter SQL statements terminated with a ";"  
sqlite> insert into BOOKS VALUES ("NULLTEST",null);  
sqlite> .q  

REFERENCJE OBSERWUJĄ:


pragma writable_schema
Gdy ta pragma jest włączona, tabele SQLITE_MASTER, w których bazę danych można zmieniać za pomocą zwykłych instrukcji UPDATE, INSERT i DELETE. Ostrzeżenie: niewłaściwe użycie tej pragmy może łatwo doprowadzić do uszkodzenia pliku bazy danych.

tabela zmian
SQLite obsługuje ograniczony podzbiór ALTER TABLE. Polecenie ALTER TABLE w SQLite pozwala użytkownikowi zmienić nazwę tabeli lub dodać nową kolumnę do istniejącej tabeli. Nie można zmienić nazwy kolumny, usunąć kolumny ani dodać lub usunąć ograniczeń z tabeli.

ZMIENIĆ SKŁADNIK TABELI


3
Niebezpieczna, ale nadal prawdopodobnie najprostsza odpowiedź imo.
Tek

2
Tak, bardzo szybko - Niebezpieczne oznacza tylko „Najpierw upewnij się, że masz kopię zapasową”
Noah,

6
Format pliku sqlite jest bardzo prosty i dlatego ta operacja jest poprawna. Format pliku zawiera tylko dwa zestawy informacji o tabeli: rzeczywiste polecenie CREATE TABLE w postaci zwykłego tekstu oraz wiersze, których wartości są wyświetlane w kolejności pól w poleceniu CREATE. Co oznacza, że ​​kod sqlite otwiera bazę danych, analizuje każde polecenie CREATE i dynamicznie buduje informacje o kolumnie w pamięci. Tak więc każde polecenie, które zmienia polecenie UTWÓRZ w taki sposób, że kończy się na takiej samej liczbie kolumn, będzie działać, nawet jeśli zmienisz ich typ lub ograniczenia.
Thomas Tempelmann,

3
@ThomasTempelmann Jednak dodanie ograniczeń, które nie są spełnione przez zestaw danych, spowoduje problemy, ponieważ planista zapytań zakłada, że ​​ograniczenia się utrzymują.
fuz 26.04.16

2
@ThomasTempelmann Usuwanie ograniczeń jest zawsze w porządku. Dodawanie wiązań jest w porządku, jeśli ograniczenie jest spełnione przez wszystkie wiersze, ale z pewnością trzeba to sprawdzić.
fuz

18

Ostatnio musiałem to zrobić w SQLite3 z tabelą o nazwie points z colunms id, lon, lat . Błędnie, gdy tabela została zaimportowana, wartości szerokości geograficznej były przechowywane w kolumnie lon i viceversa, więc oczywistą poprawką byłaby zmiana nazw tych kolumn. Więc sztuczka polegała na:

create table points_tmp as select id, lon as lat, lat as lon from points;
drop table points;
alter table points_tmp rename to points;

Mam nadzieję, że to ci się przyda!


Ta metoda nie kopiuje odpowiednio wartości PK i automatycznie tworzy ukrytą kolumnę rowid. Niekoniecznie problem, ale chciałem to podkreślić, ponieważ stał się dla mnie problemem.
TPoschel

4
Czy nie byłoby łatwiej zrobić „UPDATE points SET lon = lat, lat = lon;”?
kstep

1
Ta odpowiedź wykonuje prawidłowy ZAMÓWIENIE. Najpierw utwórz tabelę tymczasową i wypełnij ją, a następnie zniszcz oryginał .
Xeoncross

14

Cytując dokumentację sqlite :

SQLite obsługuje ograniczony podzbiór ALTER TABLE. Polecenie ALTER TABLE w SQLite pozwala użytkownikowi zmienić nazwę tabeli lub dodać nową kolumnę do istniejącej tabeli.Zmiana nazwy kolumny, usunięcie kolumny, dodanie lub usunięcie ograniczeń z tabeli nie jest możliwe.

Możesz oczywiście utworzyć nową tabelę z nowym układem SELECT * FROM old_tablei wypełnić nową tabelę wartościami, które otrzymasz.


7

Po pierwsze, jest to jedna z tych rzeczy, które uderzają mnie z zaskoczenia: zmiana nazwy kolumny wymaga utworzenia zupełnie nowej tabeli i skopiowania danych ze starej tabeli do nowej tabeli ...

GUI, na którym wylądowałem, aby wykonywać operacje SQLite, to Base . Ma ładne okno dziennika, które pokazuje wszystkie polecenia, które zostały wykonane. Wykonanie zmiany nazwy kolumny za pomocą bazy powoduje wypełnienie okna dziennika niezbędnymi komendami:

Okno dziennika podstawowego

Można je następnie łatwo skopiować i wkleić w razie potrzeby. Dla mnie to dotyczy ActiveAndroid plik migracji . Miłym akcentem jest również to, że skopiowane dane zawierają tylko polecenia SQLite, a nie znaczniki czasu itp.

Mam nadzieję, że zaoszczędzi to niektórym ludziom czas.


FYI, jeśli za pomocą ActiveAndroid można pominąć BEGIN TRANSACTION;i COMMIT;linii, jak uchwyty ActiveAndroid że sama.
Joshua Pinter

6

PRZYPADEK 1: SQLite 3.25.0+

Tylko wersja 3.25.0 SQLite obsługuje zmianę nazw kolumn. Jeśli twoje urządzenie spełnia ten wymóg, wszystko jest dość proste. Poniższe zapytanie rozwiązałoby problem:

ALTER TABLE "MyTable" RENAME COLUMN "OldColumn" TO "NewColumn";

PRZYPADEK: Starsze wersje SQLite

Musisz zastosować inne podejście, aby uzyskać wynik, który może być nieco trudny

Na przykład, jeśli masz taką tabelę:

CREATE TABLE student(Name TEXT, Department TEXT, Location TEXT)

A jeśli chcesz zmienić nazwę kolumny Location

Krok 1: Zmień nazwę oryginalnej tabeli:

ALTER TABLE student RENAME TO student_temp;

Krok 2: Teraz utwórz nową tabelę studentz poprawną nazwą kolumny:

CREATE TABLE student(Name TEXT, Department TEXT, Address TEXT)

Krok 3: Skopiuj dane z oryginalnej tabeli do nowej tabeli:

INSERT INTO student(Name, Department, Address) SELECT Name, Department, Location FROM student_temp;

Uwaga: powyższe polecenie powinno składać się z jednej linii.

Krok 4: Upuść oryginalny stół:

DROP TABLE student_temp;

Za pomocą tych czterech kroków możesz ręcznie zmienić dowolną tabelę SQLite. Pamiętaj, że będziesz musiał także odtworzyć wszelkie indeksy, przeglądarki lub wyzwalacze w nowej tabeli.


1
Jak zaktualizować wersję bazy danych sqllite do wersji 3.29.0 w Android Studio, używam interfejsu API poziomu 28.
Nathani Software

Wersja SQLite jest definiowana przez urządzenie, na którym działa aplikacja. To zależy od urządzenia.
Febin Mathew

1
W przypadku osób korzystających ze starego sqlite cztery powyższe kroki są odradzane. Zobacz sekcję „Przestroga” na sqlite.org/lang_altertable.html .
Jeff

3

zmień kolumnę tabeli <id> na <_id>

 String LastId = "id";

    database.execSQL("ALTER TABLE " + PhraseContract.TABLE_NAME + " RENAME TO " + PhraseContract.TABLE_NAME + "old");
    database.execSQL("CREATE TABLE " + PhraseContract.TABLE_NAME
    +"("
            + PhraseContract.COLUMN_ID + " INTEGER PRIMARY KEY,"
            + PhraseContract.COLUMN_PHRASE + " text ,"
            + PhraseContract.COLUMN_ORDER  + " text ,"
            + PhraseContract.COLUMN_FROM_A_LANG + " text"
    +")"
    );
    database.execSQL("INSERT INTO " +
            PhraseContract.TABLE_NAME + "("+ PhraseContract.COLUMN_ID +" , "+ PhraseContract.COLUMN_PHRASE + " , "+ PhraseContract.COLUMN_ORDER +" , "+ PhraseContract.COLUMN_FROM_A_LANG +")" +
            " SELECT " + LastId +" , "+ PhraseContract.COLUMN_PHRASE + " , "+ PhraseContract.COLUMN_ORDER +" , "+ PhraseContract.COLUMN_FROM_A_LANG +
            " FROM " + PhraseContract.TABLE_NAME + "old");
    database.execSQL("DROP TABLE " + PhraseContract.TABLE_NAME + "old");

3

Utwórz nową kolumnę z żądaną nazwą kolumny: COLNew.

ALTER TABLE {tableName} ADD COLUMN COLNew {type};

Skopiuj zawartość starej kolumny COLOld do nowej kolumny COLNew.

INSERT INTO {tableName} (COLNew) SELECT {COLOld} FROM {tableName}

Uwaga: nawiasy klamrowe są konieczne w linii powyżej.


2

Jak wspomniano wcześniej, istnieje narzędzie SQLite Database Browser, które to robi. Lyckily to narzędzie prowadzi dziennik wszystkich operacji wykonywanych przez użytkownika lub aplikację. Robiąc to raz i patrząc na dziennik aplikacji, zobaczysz związany z tym kod. Skopiuj zapytanie i wklej zgodnie z wymaganiami. Pracował dla mnie. Mam nadzieję że to pomoże


2

Z oficjalnej dokumentacji

W przypadku niektórych zmian, które w żaden sposób nie wpływają na zawartość dysku, można opcjonalnie zastosować prostszą i szybszą procedurę . Poniższa prostsza procedura jest odpowiednia do usuwania ograniczeń CHECK lub FOREIGN KEY lub NOT NULL, zmiany nazw kolumn lub dodawania lub usuwania lub zmiany wartości domyślnych w kolumnie.

  1. Rozpocznij transakcję.

  2. Uruchom PRAGMA wersja_schematu, aby ustalić numer bieżącej wersji schematu. Numer ten będzie potrzebny w kroku 6 poniżej.

  3. Aktywuj edycję schematu za pomocą PRAGMA writable_schema = ON.

  4. Uruchom instrukcję UPDATE, aby zmienić definicję tabeli X w tabeli sqlite_master: UPDATE sqlite_master SET sql = ... GDZIE type = 'table' AND name = 'X';

    Uwaga: Dokonanie zmiany w tabeli sqlite_master w ten sposób spowoduje, że baza danych będzie uszkodzona i nieczytelna, jeśli zmiana zawiera błąd składniowy. Zaleca się dokładne przetestowanie instrukcji UPDATE w osobnej pustej bazie danych przed użyciem jej w bazie danych zawierającej ważne dane.

  5. Jeśli zmiana w tabeli X wpływa również na inne tabele lub indeksy lub wyzwalacze są widokami w schemacie, uruchom instrukcje UPDATE, aby zmodyfikować również inne indeksy tabel i widoki. Na przykład, jeśli zmienia się nazwa kolumny, wszystkie ograniczenia, wyzwalacze, indeksy i widoki KLUCZA OBCYCH, które odnoszą się do tej kolumny, muszą zostać zmodyfikowane.

    Uwaga: Ponowne wprowadzenie takich zmian w tabeli sqlite_master spowoduje, że baza danych będzie uszkodzona i nieczytelna, jeśli zmiana zawiera błąd. Dokładnie przetestuj całą procedurę w osobnej testowej bazie danych przed użyciem jej w bazie danych zawierającej ważne dane i / lub wykonaj kopie zapasowe ważnych baz danych przed uruchomieniem tej procedury.

  6. Zwiększ numer wersji schematu za pomocą PRAGMA schema_version = X, gdzie X jest o jeden więcej niż stary numer wersji schematu znaleziony w kroku 2 powyżej.

  7. Wyłącz edycję schematu za pomocą PRAGMA writable_schema = OFF.

  8. (Opcjonalnie) Uruchom PRAGMA integrity_check, aby sprawdzić, czy zmiany schematu nie spowodowały uszkodzenia bazy danych.

  9. Zatwierdź transakcję rozpoczętą w kroku 1 powyżej.


PRAGMA integrity_check nie wykrywa żadnych błędów w schemacie.
Graymatter

i na czym polega problem?
Mohammad Yahia

1

Jedną z opcji, jeśli trzeba to zrobić w mgnieniu oka, a jeśli początkowa kolumna została utworzona domyślnie, to utworzyć nową kolumnę, którą chcesz, skopiować zawartość do niej i po prostu „porzucić” starą kolumnę (pozostaje obecny, ale po prostu go nie używasz / nie aktualizujesz itp.)

dawny:

alter table TABLE_NAME ADD COLUMN new_column_name TYPE NOT NULL DEFAULT '';
update TABLE_NAME set new_column_name = old_column_name;
update TABLE_NAME set old_column_name = ''; -- abandon old column, basically

Pozostawia to za sobą kolumnę (a jeśli została utworzona z opcją NOT NULL, ale bez wartości domyślnej, przyszłe wstawki, które ją zignorują, mogą się nie powieść), ale jeśli jest to zwykła tabela, kompromisy mogą być do zaakceptowania. W przeciwnym razie skorzystaj z jednej z innych wymienionych tutaj odpowiedzi lub innej bazy danych, która pozwala na zmianę nazw kolumn.



-3

sqlite3 twodb .dump> /tmp/db.txt
edytuj /tmp/db.txt zmień nazwę kolumny w Utwórz wiersz
sqlite2 yourdb2 </tmp/db.txt
mv / move yourdb2 yourdb


3
twoja odpowiedź nie zawiera żadnych informacji, kilka kodów / instrukcji wypluwa się bez żadnych dodatkowych informacji o tym, dlaczego uważasz, że to zadziała lub co się stanie, jeśli je uruchomisz
RGLSV
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.