중복 키 + 자동 증분 문제 mysql

중복 키 + 자동 증분 문제 mysql

저는 이런 테이블 구조를 가지고 있습니다.

다음 쿼리를 사용하는 테이블에 행을 삽입할 때:

INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE id = id, material_item = data, ... hidden = data;

할 때ON DUPLICATE KEYid는 fine을 시킵니다.

하지만 그 때는ON DUPLICATE KEY트리거하고 새 행을 삽입합니다. ID가 이상하게 보입니다.

어떻게 유지할 수 있습니까?auto increment 시에도 합니다.ON DUPLICATE KEY?

이 동작은 다음과 같이 문서화되어 있습니다(괄호 안의 단락).

중복 키 업데이트를 지정하고 고유 인덱스 또는 기본 키에 중복 값을 발생시키는 행이 삽입된 경우 MySQL은 이전 행의 업데이트를 수행합니다.예를 들어, 열 a가 UNIKEY로 선언되고 값 1을 포함하는 경우 다음 두 문이 유사한 영향을 미칩니다.

    INSERT INTO table (a,b,c) VALUES (1,2,3)   ON DUPLICATE KEY UPDATE c=c+1;

    UPDATE table SET c=c+1 WHERE a=1;

InnoDB 테이블의 경우 효과가 동일하지 않습니다. 여기서 a는 자동 증분 열입니다.자동 증분 열을 사용하는 경우 INSERT 문은 자동 증분 값을 증가시키지만 UPDATE는 증가시키지 않습니다.)

여기 간단한 설명이 있습니다.MySQL이 먼저 삽입을 시도합니다.이때 ID가 자동으로 증가합니다.한 번 증가하면 유지됩니다.그러면 복제가 탐지되고 업데이트가 수행됩니다.하지만 그 가치는 놓칩니다.

은 에의해는안됩다니서에 는 안 .auto_increment한 경우 및 필요한 경우 업데이트 및 삽입에 대한 오버헤드가 훨씬 큽니다.기본적으로 전체 테이블을 잠그고 일반적으로 트리거를 사용하여 번호를 다시 지정해야 합니다.더 나은 해결책은 출력에서 증분 값을 계산하는 것입니다.

이 질문은 꽤 오래된 질문이지만, 자동 증가 문제를 해결하는 데 도움이 될 수 있습니다. 중복 업데이트 부분을 삽입/실행하기 전에 다음 코드를 사용하여 모두 실행하십시오.

SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
PREPARE NEWSQL FROM @ALTER_SQL;
EXECUTE NEWSQL; 

함께 그리고 하나의 진술에서 그것은 아래와 같은 것이어야 합니다:

SET @NEW_AI = (SELECT MAX(`the_id`)+1 FROM `table_blah`);
SET @ALTER_SQL = CONCAT('ALTER TABLE `table_blah` AUTO_INCREMENT =', @NEW_AI);
PREPARE NEWSQL FROM @ALTER_SQL;
EXECUTE NEWSQL; 
INSERT INTO `table_blah` (`the_col`) VALUES("the_value")
ON DUPLICATE KEY UPDATE `the_col` = "the_value";

데이터베이스 엔진을 InnoDB에서 MyIsam으로 변경하면 문제가 해결됩니다.

저도 자동차 증량에 있어서 같은 좌절감을 겪었지만, 그것을 피할 방법을 찾았습니다.

이전에 논의된 "오버헤드" 측면에서.제가 처음 DB 쿼리 코드를 작성했을 때, 그것은 너무 많은 개별 쿼리를 수행해서 5시간이 걸렸습니다."키 업데이트 복제 시"를 켜면 약 50초로 단축됩니다.최고야!어쨌든 제가 해결한 방법은 2개의 쿼리를 사용하는 것이었습니다.2분으로 시간이 두 배로 늘어나는데, 그래도 괜찮습니다.

처음에는 모든 데이터(업데이트 및 삽입)를 쓰기 위한 SQL 쿼리를 수행했지만 첫 번째 쿼리에 "IGNORE"를 포함했으므로 업데이트를 생략하고 새 항목만 삽입합니다.따라서 이전에 auto_increment에 공백이 없다고 가정하면 새 레코드만 있으므로 공백이 없습니다.저는 그 차이를 야기하는 것이 업데이트라고 생각합니다.삽입의 경우: "내 테이블 이름(stuff,stuff2) 값에 무시 삽입"

그런 다음 해당 sql 쿼리의 "ON DUPLICATE KEY UPDATE" 변형을 수행했습니다.업데이트되는 모든 기록에는 이미 ID가 있기 때문에 ID가 그대로 유지됩니다.유일하게 중단되는 것은 새 레코드를 추가(또는 업데이트)할 때 증가하는 auto_increment 값입니다.따라서 업데이트를 적용한 후 이 auto_increment 값을 이전 값으로 다시 패치하는 것이 해결책입니다.

자동 증분 값을 패치하려면 php에서 "ALTER TABLE mytable name AUTO_INCREMENT = . ($TableCount + 1)" SQL을 사용합니다.

업데이트를 수행할 때 레코드의 양이 증가하지 않기 때문에 이 기능이 작동합니다.따라서 테이블 수를 사용하여 다음 ID가 무엇인지 알 수 있습니다.테이블 카운트에 $TableCount를 설정한 다음 1을 추가하면 다음 자동 증분 번호가 됩니다.

이것은 싸고 더럽지만 효과가 있는 것 같습니다.하지만 다른 것이 DB에 쓰는 동안 이것을 사용하는 것은 좋지 않을 수 있습니다.

저는 종종 임시 테이블을 만들고, 레코드가 새 것인지 여부를 임시 테이블에 기록하고, 새 것이 아닌 행에 대해서만 업데이트를 수행하고, 새 행에 대해 INSERT를 수행하여 이 문제를 해결합니다.다음은 완전한 예입니다.

## THE SETUP

# This is the table we're trying to insert into
DROP TABLE IF EXISTS items;
CREATE TABLE items (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) UNIQUE,
  price INT
);

# Put a few rows into the table
INSERT INTO items (name, price) VALUES
  ("Bike", 200),
  ("Basketball", 10),
  ("Fishing rod", 25)
;

## THE INSERT/UPDATE

# Create a temporary table to help with the update
DROP TEMPORARY TABLE IF EXISTS itemUpdates;
CREATE TEMPORARY TABLE itemUpdates (
  name VARCHAR(100) UNIQUE,
  price INT,
  isNew BOOLEAN DEFAULT(true)
);

# Change the price of the Bike and Basketball and add a new Tent item
INSERT INTO itemUpdates (name, price) VALUES
  ("Bike", 150),
  ("Basketball", 8),
  ("Tent", 100)
;

# For items that already exist, set isNew false
UPDATE itemUpdates
JOIN items
ON items.name = itemUpdates.name
SET isNew = false;

# UPDATE the already-existing items
UPDATE items
JOIN itemUpdates
ON items.name = itemUpdates.name
SET items.price = itemUpdates.price
WHERE itemUpdates.isNew = false;

# INSERT the new items
INSERT INTO items (name, price)
SELECT name, price
FROM itemUpdates
WHERE itemUpdates.isNew = true;

# Check the results
SELECT * FROM items;
# Results:
# ID | Name        | Price
# 1  | Bike        | 150
# 2  | Basketball  | 8
# 3  | Fishing rod | 25
# 4  | Tent        | 100
        

그INSERT IGNORE INTO접근법이 더 간단하지만, 어떤 오류도 무시합니다. 제가 원하는 것은 아닙니다.그리고 저는 이것이 MySQL의 이상한 행동이라는 것에 동의하지만 이것이 우리가 해야 할 일입니다.

저는 제 문제에 대한 답을 찾으려고 노력하고 있었기 때문에 덧붙이자고 생각했습니다.중복 경고를 멈출 수 없었고 TINYINT로 설정했기 때문에 127개 항목만 허용하고 SMALL/MED/BIGINT로 변경하면 더 많은 항목이 허용됩니다.

간단한 솔루션은 다음을 설정하는 것일 수 있습니다.id사용max(id)테이블의

INSERT INTO my_table (id, value) VALUES (
 (SELECT MAX(id) FROM my_table x) + 1, 
 'some unique value'
) 
ON DUPLICATE KEY UPDATE email = VALUE(email)

이것은 MySQL 5.6의 문제가 아니라고 생각합니다.이 예를 참조하십시오.

중복 키 업데이트 시 ID=LAST_INSERT_ID(ID)

INSERT INTO table_blah ( material_item, ... hidden ) VALUES ( data, ... data ) ON DUPLICATE KEY UPDATE material_item = data, ... hidden = data

예 = ID를 제거합니다.기본 키 = 기본 키... 위치를 자동으로 추가하기 때문에 ID입니다.

언급URL : https://stackoverflow.com/questions/23516958/on-duplicate-key-auto-increment-issue-mysql