테이블에 아직 없는 값을 삽입합니다.

테이블에 아직 없는 값을 삽입합니다.

가능한 한 간결하게 테이블을 만들고 몇 가지 값으로 초기화합니다.

단, 이 스크립트는 앱이 시작될 때마다 실행되므로 이전에 추가되지 않은 항목에만 삽입해야 합니다.

예기치 않은 오류를 무시하고 싶지 않기 때문에 'INSERT IGNORE INTO'에서 IGNORE 디렉티브를 사용하지 않습니다.

어떤 이유로 인해 INSERT INTO가 실패하고 "SQL 오류(1136):열 개수가 행 1"의 값 개수와 일치하지 않습니다.다만, 다음의 선택 항목에 추가할 필요가 있는 값이 표시됩니다.

다음은 실패한 코드입니다.

START TRANSACTION;

CREATE TABLE IF NOT EXISTS `privileges` (
    `id` TINYINT NOT NULL AUTO_INCREMENT,
    `label` VARCHAR(25) UNIQUE,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `privileges` (`label`)
SELECT `label` FROM (
SELECT NULL AS `label`
UNION VALUES
    ('item1'),
    ('item2')
) X
WHERE `label` IS NOT NULL
AND `label` NOT IN (SELECT `label` FROM `privileges`)

COMMIT;

현재는 우선 임시 테이블에 값을 삽입하고 그 값을 선택하여 해결하고 있습니다.그런데 왜 위의 내용이 작동하지 않고 제가 하려는 일을 보다 간결하게 할 수 있는 방법이 있을까요?

MariaDB 10.3.9를 사용 중이고 누락된 UNIQUIRE 제약 조건이 추가되었습니다.

편집 2: 오류가 AUTO_INCREMENT와 관련되어 있음을 LukStorms가 알아냈기 때문에 AUTO_INCREMENT 컬럼에 NULL을 전달하면 다음과 같이 문제가 해결되는 것 같습니다.

INSERT INTO `privileges` (id, label)
WITH ITEMS(label) AS (VALUES
    ('users:read'),('users:create'),
    ('clients:read'),('clients:write'),
    ('catalog:read'),('catalog:write'),
    ('cart:read'),('cart:write'),
    ('orders:read'),('orders:write'), ('test1')
) SELECT NULL, label FROM ITEMS i
WHERE label NOT IN (SELECT label FROM `privileges`);

MariaDb 10.3+에서는 VALUES 식과 함께 CTE를 사용하면 열 이름을 할당할 수 있습니다.

with ITEMS(label) as
(VALUES 
 ('item1')
,('item2'))
select i.label 
from ITEMS i
where not exists (select 1 from privileges p where p.label = i.label)

그러나 AUTO_INCREMENT가 있는 필드가 있는 테이블에 삽입할 때 오류가 발생합니다.저한테는 벌레 같아요.

단, NULL을 AUTO_INCREMENT 필드에 삽입하면 NULL은 무시됩니다.하지만 당신은 그 행동을 스스로 발견했어요.

이 방법은 다음과 같습니다.

INSERT INTO privileges (id, label)
WITH ITEMS(label) as (
 VALUES ('item1'), ('item2')
)
SELECT null, i.label
FROM ITEMS i
WHERE NOT EXISTS (SELECT 1 FROM privileges p WHERE p.label = i.label);

여기서 db <> fiddle 테스트

단, 조합된 선택을 사용하는 것도 효과적입니다.

INSERT INTO privileges (label)
SELECT label
FROM (
 SELECT 'item1' as label UNION ALL 
 SELECT 'item2'
) i
WHERE NOT EXISTS (SELECT 1 FROM privileges p WHERE p.label = i.label);

db <>여기에 추가

또 다른 방법은 임시 테이블을 사용하는 것입니다(세션이 만료되면 사라집니다.

CREATE TEMPORARY TABLE tmp_items (label VARCHAR(25) NOT NULL PRIMARY KEY);

INSERT INTO tmp_items (label) VALUES 
 ('item1')
,('item2');

INSERT INTO privileges (label)
SELECT label
FROM tmp_items i
WHERE label NOT IN (SELECT DISTINCT label FROM privileges);

여기서 db <> fiddle 테스트

첫째, 응용 프로그램에서 값을 이중으로 삽입하려고 합니다.아마 그렇게 해서는 안 될 것입니다(유효한 사용 사례는 몇 가지 생각할 수 있습니다).이전에 이미 추가된 데이터를 추가하려고 하지 않도록 하는 것이 좋습니다.인스턴스 간 상태에 쉽게 액세스할 수 없는 경우 시작할 때 데이터베이스에서 현재 목록을 꺼낸 후 삽입할 항목을 결정하십시오.

둘째, 라벨을 고유하게 하려면 왜 고유 키가 존재하지 않는가?label이 은요?INSERT IGNORE스키마에는 중복된 라벨 값을 방지하는 기능이 없기 때문에 동작하지 않습니다..ID왜 : 왜그.........label라이머리 로로?? ????

또한 SQL 계층에서 이 중복 삭제를 수행해야 할 경우 를 사용하여 기존 프라이머리 키의 용장 삽입을 흡수할 수 있습니다.

INSERT INTO `privileges` (`label`)
VALUES
    ('item1'),
    ('item2')
)
ON DUPLICATE KEY UPDATE `label` = `label`

이 솔루션은 자동 증가 ID 키로는 구현하기 어렵습니다. 응용 프로그램이 ID를 인식하지 못할 수 있기 때문입니다.폐기하는 것을 고려하는 또 다른 이유.

아쉽게도 없습니다.

하지 않는 이유로 「」를 참조해 주세요.INSERT IGNORE꽤 솔직하게 말하는 것이 최선의 선택입니다. 하지만 당신은 여전히 최소한 에 대한 독특한 열쇠가 필요합니다.label네, 네, 네.

언급URL : https://stackoverflow.com/questions/53952040/insert-values-not-already-in-table