Удалить, усечь или удалить, чтобы очистить таблицу в MySQL
Я пытаюсь очистить таблицу, но не избавиться от фактической структуры таблицы. у меня есть id столбец с автоинкрементом; Мне не нужно сохранять идентификационный номер, но он мне нужен, чтобы сохранить его автоинкрементную характеристику. Я обнаружил удаление и усечение, но я боюсь, что одна из них полностью отбросит всю таблицу, делая будущие команды вставки бесполезными.
Как удалить все записи из таблицы, чтобы я мог вставить новые данные?
5 ответов
TRUNCATE сбросит ваше начальное значение с автоматическим приращением (по крайней мере, в таблицах InnoDB), хотя вы можете записать его значение перед усечением и, соответственно, заново установить его, используя alter table:
ALTER TABLE t2 AUTO_INCREMENT = value
drop table удалит всю таблицу с данными
delete * from table удалит данные, оставив значения автоинкремента в покое. Это также займет некоторое время, если в таблице много данных.
truncate table удалит данные, сбросит значения автоинкремента (но оставит их как столбцы автоинкремента, так что он будет начинаться с 1 и снова поднимется оттуда), и будет очень быстрым.
Drop сделает именно это.... отбросит соответствующую таблицу, если только таблица не является родительской для другой таблицы.
Удалить удалит все данные, которые соответствуют условию; если условие не указано, будут удалены все данные в таблице.
Усечение похоже на удаление; однако он сбрасывает счетчик auto_increment обратно на 1 (или начальное начальное значение). Однако лучше использовать усечение над удалением, потому что удаление удаляет данные по каждой строке, что приводит к снижению производительности, чем усечение. Однако truncate не будет работать с таблицами InnoDB, в которых применяется ссылочная целостность, если только она не отключена перед выполнением команды truncate.
Так что расслабься; если вы не введете команду удаления для таблицы, она не будет удалена.
Усеченная таблица - это то, что вы ищете http://www.1keydata.com/sql/sqltruncate.html
Другая возможность включает создание пустой копии таблицы, установку AUTO_INCREMENT (с некоторой возможной свободой для вставок во время неатомарной операции) и затем вращение обоих:
CREATE TABLE t2_new LIKE t2;
SELECT @newautoinc:=auto_increment /*+[leeway]*/
FROM information_schema.tables
WHERE table_name='t2';
SET @query = CONCAT("ALTER TABLE t2_new AUTO_INCREMENT = ", @newautoinc);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
RENAME TABLE t2 TO t2_old, t2_new TO t2;
И затем, у вас есть дополнительное преимущество в том, что вы все еще можете передумать перед удалением старой таблицы.
Если вы пересмотрите свое решение, вы все равно можете вернуть старые записи из таблицы перед операцией:
INSERT /*IGNORE*/ INTO t2 SELECT * FROM t2_old /*WHERE [condition]*/;
Когда вы хорошо, вы можете оставить старый стол:
DROP TABLE t2_old;
Я только что столкнулся с ситуацией, когда DELETE сильно влияет на производительность SELECT по сравнению с TRUNCATE в полнотекстовом запросе InnoDB.
Если я УДАЛЯЮ все строки, а затем повторно заполняю таблицу (1 миллион строк), для возврата типичного запроса требуется 1 с.
Если вместо этого я ОБРЕЗАТЬ таблицу и заново заполнить ее точно таким же образом, то для возврата типичного запроса потребуется 0,05 с.
YMMV, но по какой-то причине для меня в MariaDB 10.3.15-MariaDB-log DELETE, похоже, портит мой индекс.