Пытаясь выполнить LOAD DATA INFILE с ЗАМЕНИТЬЮ и AUTO_INCREMENT

Я пытаюсь загрузить файл в базу данных MySQL, имея первичный ключ auto_incremented, и я бы хотел, чтобы данные обновлялись, если я нахожу любые повторяющиеся строки. Однако ключевые слова REPLACE работают только на первичный ключ, который автоматически генерируется, поэтому я застрял.

как иметь возможность иметь таблицу с идентификатором, который автоматически указывает и в то же время иметь возможность вставлять/обновлять данные из файла с помощью LOAD DATA INFILE?

Вот таблица

CREATE TABLE  `oxygen_domain`.`TEST` (
`TEST_ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
`VALUE` varchar(255) DEFAULT NULL,
PRIMARY KEY (`TEST_ID`,`NAME`,`VALUE`)
)

и вот команда

LOAD DATA LOCAL INFILE 'C:/testData.txt'
REPLACE
INTO TABLE TEST
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);

и вот примеры данных

ignored name, ignored value
name1,value1
name2,value2
name3,value3

Требуемый конечный результат после многократного выполнения команды с указанными выше данными

|TEST_ID |NAME |VALUE|
1, 'name1', 'value1'
2, 'name2', 'value2'
3, 'name3', 'value3'

Ответ 1

ЗАМЕЧАНИЕ № 1

Вы не должны делать REPLACE, потому что это механический DELETE и INSERT.

Как Документация MySQL говорит о REPLACE

Пункт 2

REPLACE - это расширение MySQL для стандарта SQL. Он либо вставляет, либо удаляет и вставляет. Для другого расширения MySQL для стандартного SQL, которое либо вставляет, либо обновляет - см. Раздел 13.2.5.3, "ВСТАВИТЬ... НА ПОДКЛЮЧЕНИЕ КЛЮЧА КЛЮЧА".

Пункт 5

Чтобы использовать REPLACE, у вас должны быть как привилегии INSERT, так и DELETE для таблицы.

Использование REPLACE отбросит установленные значения для TEST_ID, которые нельзя будет повторно использовать повторно.

ЗАМЕЧАНИЕ # 2

Макет таблицы не поддерживает захват дубликатов клавиш

Если имя уникально, таблица должна быть выложена следующим образом

LAYOUT # 1

CREATE TABLE  `oxygen_domain`.`TEST` (
`TEST_ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
`VALUE` varchar(255) DEFAULT NULL,
PRIMARY KEY (`TEST_ID`),
KEY (`NAME`)
)

Если имя допускает несколько значений, таблица должна быть выложена следующим образом

LAYOUT # 2

CREATE TABLE  `oxygen_domain`.`TEST` (
`TEST_ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) NOT NULL,
`VALUE` varchar(255) DEFAULT NULL,
PRIMARY KEY (`TEST_ID`),
KEY (`NAME`,`VALUE`)
)

ПРЕДЛАГАЕМОЕ РЕШЕНИЕ

Используйте временную таблицу, чтобы поймать все. Затем выполните большой INSERT из таблицы temp на основе макета

LAYOUT # 1

Замените VALUE для дубликата NAME

USE oxygen_domain
DROP TABLE IF EXISTS `TESTLOAD`;

CREATE TABLE `TESTLOAD` SELECT NAME,VALUE FROM TEST WHERE 1=2;

LOAD DATA LOCAL INFILE 'C:/testData.txt'
INSERT INTO TABLE `TESTLOAD`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);

INSERT INTO `TEST` (NAME, VALUE)
SELECT NAME, VALUE FROM `TESTLOAD`
ON DUPLICATE KEY UPDATE VALUE = VALUES(VALUE);

DROP TABLE `TESTLOAD`;

LAYOUT # 2

Игнорировать повторяющиеся строки (NAME,VALUE)

USE oxygen_domain
DROP TABLE IF EXISTS `TESTLOAD`;

CREATE TABLE `TESTLOAD` SELECT NAME,VALUE FROM TEST WHERE 1=2;

LOAD DATA LOCAL INFILE 'C:/testData.txt'
INSERT INTO TABLE `TESTLOAD`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);

INSERT IGNORE INTO `TEST` (NAME, VALUE)
SELECT NAME, VALUE FROM `TESTLOAD`;

DROP TABLE `TESTLOAD`;

Ответ 2

Создайте уникальный индекс для NAME и VALUE и используйте IGNORE вместо REPLACE:

LOAD DATA LOCAL INFILE 'C:/testData.txt'
IGNORE
INTO TABLE `TEST`
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);

Ответ 3

Просто удалите REPLACE из LOAD DATA, выполните его дважды, и вы получите желаемый результат:

LOAD DATA LOCAL INFILE 'C:/testData.txt'
INTO TABLE TEST
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(NAME, VALUE);

Я полагаю, вы действительно не хотите, чтобы желаемый результат был опубликован, не так ли?