Как лучше обновлять таблицу в базе?

Я каждый раз удалял таблицу и наполнял заново.

Достаточно удалять все данные: delete from my_table , заново создавать таблицу точно не надо.

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

Либо уникальные ключи (например uuid) либо блокировка. Блокировку не обязательно делать на уровне таблицы, можно просто выставлять флажок is_working в отдельную таблицу при запуске и проверять его до начала записи. Если флажок стоит то идет выполнение другой копии.

В предложенном виде ни один из вариантов не сработает.

возможно?

скрипт(ы) пусть пишет в хронологическую таблицу(т.е == выходной + поля делающую каждую запись уникальной и упорядоченной в некотором «темпоральном» смысле) + 1 жнец(на стороне хз где) который поедает таблицу от старого к новому перенося(обновляя добавляя удаляя) кортежу в "выходную " таблицу

Есть тяжелый sql запрос

Зная автора (а также глядя на выбор его вариантов), это может означать, как:

• действительно, SQL-запрос на получение данных из БД, который и нагружает движок
• или же, что процесс подготовки данных для вставки длительный и ресурсоемкий

В любом случае, не надо решать XY, но скорее всего автор не раскроет ни X и ни Y.

В предложенном виде ни один из вариантов не сработает.

И что же им помешает?

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

Я так понял этот скрипт по крону раз в час обновляет данные? Зачем его тогда запускать несколько раз одновременно?

Можно создать Materialized view, они сделаны как раз для таких случаев, когда долгий запрос надо регулярно обновлять

Упс, в mysql похоже нет встроенного механизма матвью, но вот тут https://fromdual.com/mysql-materialized-views есть пример процедуры, в транзакции делается truncate и insert, проблем с отсутствием данных или дублями не будет

DROP PROCEDURE refresh_mv_now;

DELIMITER $$

CREATE PROCEDURE refresh_mv_now (
    OUT rc INT
)
BEGIN

  TRUNCATE TABLE sales_mv;

  INSERT INTO sales_mv
  SELECT product_name
      , SUM(product_price), SUM(product_amount)
      , AVG(product_price), AVG(product_amount)
      , COUNT(*)
    FROM sales
  GROUP BY product_name;

  SET rc = 0;
END;
$$

DELIMITER ;

Да, наверное.

ЗЫ ЯННП

уникальные ID никак не предотвратят запись дубликатов, а is_working предлагается менять из двух разных транзакций, что просто приведёт к паузе при ожидании блокировки в одной из них.

Не надо давать плохих советов. Между truncate и insert может быть неопределенное время. Надо делать в темптэйбл, после чего атомарно обновлять ddl для вьюхи.

каждый раз удалял таблицу и наполнял заново

чтобы даже при нескольких запусках одновременно скрипта данные не перезатирались

ЯННП.

он сделал кнопку «посчитать» в вебне, которая запускает получасовую задачу безо всякой индикации выполнения. Девочке-буху надоедает ждать, и она периодически нажимает снова и снова, приближая технологическую сингулярность.

select for update skip locked поможет в случае is_working, но это выглядит как костыль при наличии advisory locks (или их аналогов в mysql, никогда им не пользовался).

Хотя, зная ТС, соглашусь с vvn_black, что тут скорее всего проблема XY, и задачу можно решить кардинально иначе без приседаний с удалением таблиц (и возможно без блокировок), но мы это уже никогда не узнаем.

Два ведра энтропии сему господину

Мы всё умрём что ли? Из-за ТСа.

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

2. сделай отдельный джоб который чистит старые данные с необходимой и достаточной интенсивностью