[sql] SQLite-UPSERT * not * INSERT 또는 REPLACE

http://en.wikipedia.org/wiki/Upsert

SQL Server에 업데이트 저장 프로 시저 삽입

내가 생각하지 못한 SQLite 에서이 작업을 수행하는 영리한 방법이 있습니까?

기본적으로 레코드가 존재하면 4 개 열 중 3 개를 업데이트하고 싶습니다.없는 경우 4 번째 열의 기본 (NUL) 값으로 레코드를 삽입하고 싶습니다.

ID는 기본 키이므로 UPSERT에 대한 레코드는 하나뿐입니다.

(명확하게 업데이트하거나 삽입 해야하는 경우 결정하기 위해 SELECT의 오버 헤드를 피하려고합니다.)

제안?

TABLEite에 대한 SQLite 사이트의 구문을 확인할 수 없습니다. 테스트 할 데모를 만들지 않았지만 지원되지 않는 것 같습니다.

그렇다면 세 개의 열이 있으므로 실제로 다음과 같이 나타납니다.

CREATE TABLE table1(
    id INTEGER PRIMARY KEY ON CONFLICT REPLACE,
    Blob1 BLOB ON CONFLICT REPLACE,
    Blob2 BLOB ON CONFLICT REPLACE,
    Blob3 BLOB
);

그러나 처음 두 얼룩은 충돌을 일으키지 않으며 ID만이 가능하므로 Blob1을 asusme하고 Blob2는 (원하는대로) 교체하지 않습니다

바인딩 데이터가 완전한 트랜잭션 일 때 SQLite의 UPDATEs 업데이트 될 각 전송 된 행에는 다음이 필요합니다. 리셋 기능을 사용할 수있는 INSERT와 달리 Prepare / Bind / Step / Finalize 문

명령문 객체의 수명은 다음과 같습니다.

sqlite3_prepare_v2 ()를 사용하여 객체를 만듭니다.
sqlite3_bind_ 인터페이스를 사용하여 호스트 매개 변수에 값을 바인드하십시오.
sqlite3_step ()을 호출하여 SQL을 실행하십시오.
sqlite3_reset ()을 사용하여 명령문을 재설정 한 후 2 단계로 돌아가서 반복하십시오.
sqlite3_finalize ()를 사용하여 명령문 오브젝트를 제거하십시오.

업데이트 INSERT에 비해 느린 것으로 생각되지만 기본 키를 사용하여 SELECT와 어떻게 비교합니까?

아마도 select를 사용하여 4 번째 열 (Blob3)을 읽은 다음 REPLACE를 사용하여 원래 4 번째 열을 처음 3 열의 새 데이터와 혼합하는 새 레코드를 작성해야합니까?

답변

표에서 3 개의 열을 가정합니다 : ID, NAME, ROLE

BAD : 모든 열을 삽입하거나 ID = 1의 새로운 값으로 바꿉니다

INSERT OR REPLACE INTO Employee (id, name, role)
  VALUES (1, 'John Foo', 'CEO');

BAD : 열 2 개를 삽입하거나 교체합니다. NAME 열은 NULL 또는 기본값으로 설정됩니다.

INSERT OR REPLACE INTO Employee (id, role)
  VALUES (1, 'code monkey');

좋은 : SQLite에서 SQLite On 충돌 절 UPSERT 지원을 사용하십시오
! UPSERT 구문이 SQLite 버전 3.24.0에 추가되었습니다!

UPSERT는 INSERT가 고유성 제약 조건을 위반하는 경우 INSERT가 UPDATE 또는 no-op로 작동하게하는 INSERT에 추가 된 특수 구문입니다. UPSERT는 표준 SQL이 아닙니다. SQLite의 UPSERT는 PostgreSQL에서 설정 한 구문을 따릅니다.

양호하지만 부드럽습니다. 이렇게하면 2 개의 열이 업데이트됩니다. ID = 1이 존재하면 NAME은 영향을받지 않습니다. ID = 1이 없으면 이름이 기본값 (NULL)이됩니다.

INSERT OR REPLACE INTO Employee (id, role, name)
  VALUES (  1,
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

열 2 개가 업데이트됩니다. ID = 1이 존재하면 ROLE은 영향을받지 않습니다. ID = 1이 없으면 역할이 기본값 대신 ‘Benchwarmer’로 설정됩니다.

INSERT OR REPLACE INTO Employee (id, name, role)
  VALUES (  1,
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

답변

INSERT OR REPLACE는 “UPSERT”와 동일 하지 않습니다 .

id, name 및 role 필드가있는 Employee 테이블이 있다고 가정하십시오.

INSERT OR REPLACE INTO Employee ("id", "name", "role") VALUES (1, "John Foo", "CEO")
INSERT OR REPLACE INTO Employee ("id", "role") VALUES (1, "code monkey")

붐, 직원 번호 1의 이름을 잃어 버렸습니다. SQLite가 기본 값으로 바꿨습니다.

UPSERT의 예상 결과는 역할을 변경하고 이름을 유지하는 것입니다.

답변

기존 행에서 하나 또는 두 개의 열만 보존하려는 경우 Eric B의 대답 은 정상입니다. 많은 열을 유지하려면 너무 번거로워집니다.

다음은 양쪽의 열에 맞게 확장 할 수있는 방법입니다. 이를 설명하기 위해 다음 스키마를 가정합니다.

 CREATE TABLE page (
     id      INTEGER PRIMARY KEY,
     name    TEXT UNIQUE,
     title   TEXT,
     content TEXT,
     author  INTEGER NOT NULL REFERENCES user (id),
     ts      TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 );

특히 이는 name행의 자연 키입니다. id외래 키에만 사용되므로 새 행을 삽입 할 때 SQLite가 ID 값 자체를 선택하는 것이 중요합니다. 그러나의 기존 행을 업데이트 할 때 name이전 ID 값을 계속 유지하고 싶습니다 (분명히!).

나는 UPSERT다음과 같은 구성 으로 사실 을 달성합니다 .

 WITH new (name, title, author) AS ( VALUES('about', 'About this site', 42) )
 INSERT OR REPLACE INTO page (id, name, title, content, author)
 SELECT old.id, new.name, new.title, old.content, new.author
 FROM new LEFT JOIN page AS old ON new.name = old.name;

이 쿼리의 정확한 형식은 약간 다를 수 있습니다. 핵심은 INSERT SELECT기존 행을 새 값에 결합하기 위해 왼쪽 외부 결합을 사용하는 것입니다 .

여기서 행이 이전에 존재하지 않으면 old.idwill이 NULL있고 SQLite는 자동으로 ID를 할당하지만 이미 그러한 행 old.id이 있으면 실제 값을 가지므로 재사용됩니다. 정확히 내가 원하는 것입니다.

실제로 이것은 매우 유연합니다. ts열이 모든 측면에서 완전히 누락 된 방법에 유의하십시오 . DEFAULT값 이 있기 때문에 SQLite는 어떤 경우에도 올바른 작업을 수행하므로 직접 처리 할 필요가 없습니다.

또한 양쪽에 열을 포함 할 수 있습니다 new및 old예를 들어, 사용 후 측면과 COALESCE(new.content, old.content)외부에서 SELECT당신이 고정 된 쿼리를 사용하고 새로운 바인딩하는 경우 예 – “그렇지 않으면 기존 내용을 유지, 어떤이 있다면 새로운 내용 삽입”말을 자리 표시 자와 함께 값.

답변

일반적으로 업데이트를 수행하는 경우 ..

거래 시작
업데이트를
행 개수 확인
0이면 삽입하십시오
범하다

일반적으로 인서트를하고 있다면

거래 시작
삽입을 시도하십시오
기본 키 위반 오류 확인
오류가 발생하면 업데이트를 수행하십시오
범하다

이렇게하면 선택을 피하고 Sqlite에서 거래 적으로 건전합니다.

답변

이 답변은 업데이트되었으므로 아래 주석은 더 이상 적용되지 않습니다.

2018-05-18 스톱 프레스.

SQLite에서 UPSERT 지원! UPSERT 구문이 버전 3.24.0 (보류 중) 인 SQLite에 추가되었습니다!

대안 적으로 :

이 작업을 수행하는 또 다른 완전히 다른 방법은 다음과 같습니다. 내 응용 프로그램에서 메모리에서 행을 만들 때 내 메모리 rowID를 long.MaxValue로 설정했습니다. (MaxValue는 ID로 사용되지 않으며 오래 살지 못할 것입니다 …. rowID가 그 값이 아닌 경우 이미 데이터베이스에 있어야하므로 MaxValue 인 경우 UPDATE가 필요하며 삽입이 필요합니다. 앱에서 rowID를 추적 할 수있는 경우에만 유용합니다.

답변

나는 이것이 오래된 스레드라는 것을 알고 있지만 늦게 sqlite3에서 작업하고 있으며 매개 변수가있는 쿼리를 동적으로 생성하는 데 필요한이 방법을 생각해 냈습니다.

insert or ignore into <table>(<primaryKey>, <column1>, <column2>, ...) values(<primaryKeyValue>, <value1>, <value2>, ...);
update <table> set <column1>=<value1>, <column2>=<value2>, ... where changes()=0 and <primaryKey>=<primaryKeyValue>;

여전히 업데이트에 where 절이있는 2 개의 쿼리이지만 트릭을 수행하는 것 같습니다. 또한 sqlite가 changes () 호출이 0보다 큰 경우 update 문을 완전히 최적화 할 수 있다는 비전을 가지고 있습니다. 그것이 실제로인지 아닌지는 내 지식을 넘어서는 것이지만 사람은 꿈을 꿀 수 없습니다. 😉

보너스 포인트의 경우이 행을 추가하면 새로 삽입 된 행이든 기존 행이든 행의 ID를 반환합니다.

select case changes() WHEN 0 THEN last_insert_rowid() else <primaryKeyValue> end;

답변

다음은 실제로 여러 상황에서 다르게 작동하는 INSERT 또는 REPLACE 대신 UPSERT (UPDATE 또는 INSERT) 솔루션입니다.

다음과 같이 작동합니다.
1. 동일한 ID를 가진 레코드가 존재하면 업데이트를 시도하십시오.
2. 업데이트가 행을 변경하지 않은 경우 ( NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0)) 레코드를 삽입하십시오.

따라서 기존 레코드가 업데이트되거나 삽입이 수행됩니다.

중요한 세부 사항은 changes () SQL 함수를 사용하여 업데이트 명령문이 기존 레코드에 도달했는지 확인하고 레코드에 도달하지 않은 경우에만 insert 문을 수행하는 것입니다.

한 가지 언급 할 사항은 changes () 함수는 하위 수준 트리거 ( http://sqlite.org/lang_corefunc.html#changes 참조 ) 가 수행 한 변경 사항을 반환하지 않으므로 반드시 고려해야합니다.

다음은 SQL입니다 …

테스트 업데이트 :

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY,
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 2;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 2, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;

테스트 인서트 :

--Create sample table and records (and drop the table if it already exists)
DROP TABLE IF EXISTS Contact;
CREATE TABLE [Contact] (
  [Id] INTEGER PRIMARY KEY,
  [Name] TEXT
);
INSERT INTO Contact (Id, Name) VALUES (1, 'Mike');
INSERT INTO Contact (Id, Name) VALUES (2, 'John');

-- Try to update an existing record
UPDATE Contact
SET Name = 'Bob'
WHERE Id = 3;

-- If no record was changed by the update (meaning no record with the same Id existed), insert the record
INSERT INTO Contact (Id, Name)
SELECT 3, 'Bob'
WHERE NOT EXISTS(SELECT changes() AS change FROM Contact WHERE change <> 0);

--See the result
SELECT * FROM Contact;