Q & A 스타일

글쎄, 몇 시간 동안 문제를 조사하고 해결 한 후, 테이블의 구조와 무결성을 유지하기 위해 활성화 된 외래 키 제한이 있는지 여부에 따라이를 수행하는 두 가지 방법이 있음을 발견했습니다. 제 상황에있는 사람들에게 시간을 절약하기 위해 이것을 깨끗한 형식으로 공유하고 싶습니다.

옵션 1 : 행을 삭제할 수 있습니다.

즉, 외래 키가 없거나, 가지고있는 경우 무결성 예외가 없도록 SQLite 엔진이 구성됩니다. 갈 길은 INSERT OR REPLACE 입니다. ID가 이미 존재하는 플레이어를 삽입 / 업데이트하려는 경우 SQLite 엔진은 해당 행을 삭제하고 제공하는 데이터를 삽입합니다. 이제 질문이 있습니다. 이전 ID를 연결하려면 어떻게해야합니까?

user_name = ‘steven’및 age = 32 데이터 로 UPSERT 를 원한다고 가정 해 보겠습니다 .

이 코드를보십시오 :

INSERT INTO players (id, name, age)

VALUES (
    coalesce((select id from players where user_name='steven'),
             (select max(id) from drawings) + 1),
    32)

트릭은 통합에 있습니다. 사용자 ‘steven’의 ID가 있으면 반환하고, 그렇지 않으면 새로운 새 ID를 반환합니다.

옵션 2 : 행을 삭제할 여유가 없습니다.

이전 솔루션으로 돌아 다니면서이 ID가 다른 테이블의 외래 키로 작동하기 때문에 제 경우에는 데이터가 파괴 될 수 있음을 깨달았습니다. 게다가 ON DELETE CASCADE 절로 테이블을 만들었는데 , 이는 데이터를 자동으로 삭제한다는 의미입니다. 위험한.

그래서 처음에는 IF 절을 생각했지만 SQLite에는 CASE 만 있습니다 . 그리고이 CASE 는 EXISTS (user_name = ‘steven’인 플레이어에서 id 선택) 인 경우 하나의 UPDATE 쿼리 를 수행 하고 그렇지 않은 경우 INSERT 를 수행하는 데 사용할 수 없습니다 (또는 적어도 관리 하지 않았습니다). 안돼.

그리고 마침내 나는 성공적으로 무차별 대입을 사용했습니다. 논리는 수행하려는 각 UPSERT 에 대해 먼저 INSERT OR IGNORE 를 실행하여 사용자와 함께 행이 있는지 확인한 다음 삽입하려는 것과 정확히 동일한 데이터 로 UPDATE 쿼리 를 실행하는 것 입니다.

이전과 동일한 데이터 : user_name = ‘steven’및 age = 32

-- make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32);

-- make sure it has the right data
UPDATE players SET user_name='steven', age=32 WHERE user_name='steven'; 

그리고 그게 전부입니다!

편집하다

Andy가 언급했듯이 먼저 삽입 한 다음 업데이트를 시도하면 예상보다 자주 트리거가 실행될 수 있습니다. 이것은 제 생각에는 데이터 안전 문제는 아니지만 불필요한 이벤트를 발생시키는 것이별로 의미가 없다는 것은 사실입니다. 따라서 개선 된 솔루션은 다음과 같습니다.

-- Try to update any existing row
UPDATE players SET age=32 WHERE user_name='steven';

-- Make sure it exists
INSERT OR IGNORE INTO players (user_name, age) VALUES ('steven', 32); 

답변

-- Try to update any existing row
UPDATE players
SET age=32
WHERE user_name='steven';

-- If no update happened (i.e. the row didn't exist) then insert one
INSERT INTO players (user_name, age)
SELECT 'steven', 32
WHERE (Select Changes() = 0);

INSERT OR IGNORE ...
UPDATE ...

UPDATE OR IGNORE ...
INSERT OR IGNORE ...

UPDATE players SET user_name="gil", age=32 WHERE user_name='george';
SELECT changes();

INSERT INTO players (user_name, age) VALUES ('gil', 32); 

-- Table structure
CREATE TABLE players (
    id        INTEGER       PRIMARY KEY AUTOINCREMENT,
    user_name VARCHAR (255) NOT NULL
                            UNIQUE,
    age       INTEGER       NOT NULL
);

-- Insert if NOT exists
INSERT INTO players (user_name, age)
SELECT 'johnny', 20
WHERE NOT EXISTS (SELECT 1 FROM players WHERE user_name='johnny' AND age=20);

-- Update (will affect row, only if found)
-- no point to update user_name to 'johnny' since it's unique, and we filter by it as well
UPDATE players
SET age=20
WHERE user_name='johnny';

-- Table structure
CREATE TABLE players (
    id        INTEGER       PRIMARY KEY AUTOINCREMENT,
    user_name VARCHAR (255) NOT NULL
                            UNIQUE,
    age       INTEGER       NOT NULL
);

-- Single command to insert or update
INSERT OR REPLACE INTO players
(id, user_name, age)
VALUES ((SELECT id from players WHERE user_name='johnny' AND age=20),
        'johnny',
        20);