Блог

Мы получаем данные от третьей стороны, и, к сожалению, эти данные содержат уникальные записи, но не содержат уникального ключа. Нам нужно импортировать эту информацию в базу данных MySQL, предпочтительно с использованием PHP, но нужно убедиться, что строки не дублируются.

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

Когда набор данных был меньше, мы просто обновляли всю базу данных, удаляя все данные и повторно импортируя все это снова. По мере увеличения размера набора данных это не является устойчивым решением, поскольку оно использует слишком много системных ресурсов и занимает все больше и больше времени.

Сначала мы помещаем импортированные данные во временную таблицу MySQL (исходные данные не имеют первичного ключа):

Структура таблицы: импортированные данные

     BalanceDate DATE NOT NULL COMMENT 'Date Balance Was Fetched From Bank',
    BalanceTime TIME NOT NULL COMMENT 'Time Balance Was Fetched from Bank',
    AccountName VARCHAR(100) DEFAULT NULL COMMENT 'Name of Account Downloaded from Source',
    AccountNo VARCHAR(50) DEFAULT NULL COMMENT 'Account Number Downloaded from Source',
    InstName VARCHAR(150) DEFAULT NULL COMMENT 'Financial Institution Name Downloaded from Source',
    Balance DECIMAL(10, 2) NOT NULL COMMENT 'Balance in Account',
    Type VARCHAR(50) NOT NULL COMMENT 'Type of Account',
    Class VARCHAR(50) NOT NULL COMMENT 'Class of Account',
    Index INT(11) DEFAULT NULL COMMENT 'Index Number Downloaded from Source',
  

Обратите внимание, что номер индекса кажется уникальным для каждого финансового учреждения, но не является уникальным для каждой записи.

Живая таблица содержит вышеуказанные поля плюс автоматически добавляемое поле уникального идентификатора. Мы также добавили несколько столбцов для внешних ключей, которые мы преобразуем с помощью простого оператора IF THEN .

Структура таблицы: дополнительные поля в живой таблице

     ID INT(11) NOT NULL AUTO_INCREMENT,
    AccountID INT(11) NOT NULL COMMENT 'Linked to Accounts table (Account at Financial Institution)',
    InstID INT(11) NOT NULL COMMENT 'Links to Institution table (Financial Institution)',
  

PHP

     // Accounts and Banks and prepopulated into the database in other tables.
    // This is a simplified example of the logic. Actual code a bit more complicated.
    if ($AccountName == "Whatever Account" AND $InstName == "Whatever Bank") {
       $AccountID = 1;
       $InstID = 1;
    }
  

Цель состоит в том, чтобы перенести данные в нашу базу данных и не допустить дублирования записей в процессе.

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

Добавьте виртуальный столбец в таблицу, содержащую хэш всех других столбцов, и укажите его UNIQUE

 HashCode CHAR(32) AS MD5(CONCAT_WS(',', BalanceDate, BalanceTime, AccountName, AccountNo, ...) UNIQUE
  

Затем, когда вы вставляете из временной таблицы, вы можете использовать INSERT IGNORE . Если какая-либо из входящих строк имеет хэш, совпадающий с хэшем одной из существующих строк, включение индекса HashCode приведет к его пропуску.

Это замедлит процесс импорта, поскольку каждая новая строка должна быть хэширована и проверена по индексу. Бесплатного обеда не бывает, и для этого не потребуется столько памяти, сколько для индексации всей строки.