очистка db избыточных данных

locid country city 39409 US Aaronsburg 128426 US Aaronsburg 340356 US Aaronsburg 429373 US Aaronsburg 422717 US Abbeville 431344 US Abbeville 433062 US Abbeville 341726 US Abbeville 421248 US Abbeville 40779 US Abbeville 326718 US Abbeville 317654 US Abbeville 16707 US Abbeville 25771 US Abbeville 120301 US Abbeville 132115 US Abbeville 121770 US Abbeville 130397 US Abbeville 5585 US Abbeville 10227 US Abbeville 190173 US Abbeville 491120 US Abbeville 311174 US Abbeville 306532 US Abbeville 164271 US Abbot 465218 US Abbot 58452 US Abbotsford 359399 US Abbotsford 309116 US Abbotsford 8169 US Abbotsford 

может кто-нибудь дать мне sql-запрос, чтобы помочь мне очистить эту таблицу? после очистки объекта (индекс) необходимо сбросить, кстати, это город графства, используя этот запрос. SELECT locid, country, city FROM location WHERE country = 'US' ORDER BY city ASC . эти избыточные данные появились, когда я импортировал sql-текст много раз через импорт phpmyadmin, и это результат,

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

 ALTER IGNORE TABLE location ADD UNIQUE KEY ix1(country, city); 

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

но, как было предложено @AD7six в комментариях, это может не работать с MySQL версии 5.1.41,5.5.1-m2, 6.0 : см. здесь ошибку

или альтернативный безопасный способ удаления дубликатов с использованием запроса DELETE :

 DELETE a FROM location a LEFT JOIN ( SELECT locid FROM location GROUP BY country, city )b ON a.locid = b.locid WHERE b.locid IS NULL; 

для того, чтобы locid значения locid , вы можете просто locid primary key на locid и воссоздать его:

 ALTER TABLE location DROP column locid; ALTER TABLE location ADD COLUMN locid INT unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; 

или альтернативный способ переселения значений locid с помощью запроса UPDATE :

 SET var_locid = 0; UPDATE location SET locid = (@var_locid := @var_locid + 1) ORDER BY locid ASC; 

Вы можете сделать это несколькими способами – каждым простым шагом.

Резервное копирование исходной таблицы

Если вы еще не создали резервные копии исходных данных таблицы.

Создание временной таблицы

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

 CREATE TABLE temporary ( locid INTEGER(10) UNSIGNED NOT NULL AUTO_INCREMENT, country VARCHAR(255) DEFAULT '', city VARCHAR(255) DEFAULT '', PRIMARY KEY (locid), UNIQUE KEY (country, city) ); 

Схема должна быть почти такой же, как и в вашей существующей таблице. Заметные отличия:

  • Автоматический прирост первичного ключа
  • Уникальный индекс страны + города

Импортируйте свои старые данные

 INSERT IGNORE INTO temporary (country, city) SELECT country, city FROM original_table_name; 

Это заполнит вашу временную таблицу уникальными комбинациями страны и города. Каждой строке будет присвоено значение автоинкремента – т.е. оно начнется с 1.

Проверить результаты

Посмотрите на свои данные и убедитесь, что они выглядят так, как вы хотите:

 SELECT * FROM temporary; 

Если что-то не так – отбросьте таблицу, temporary настройте sql, который вы используете, и запустите снова.

Замените исходную таблицу на новую

Когда вы будете довольны тем, что вы видите во temporary таблице:

 DROP TABLE original_table_name; -- Or rename it to something else RENAME TABLE temporary TO original_table_name; 

Теперь у вас есть таблица с уникальными данными и последовательными идентификаторами, начиная с 1.

Другие варианты

Вы также можете просто применить уникальный индекс к стране + города, отбросить первичное ключевое поле, а затем повторно добавить его в качестве автоинкремента. Имейте в виду, что mysql может игнорировать флаг игнорирования при создании индексов , хотя для этого есть обходной путь .

Я бы сделал это лично, но если вы не уверены в том, что sql – делаете что-то по одному за раз и не уничтожаете исходные данные в процессе, можете сделать обновление вашей схемы менее тревожной задачей.

удалить эти записи

 select T2.* from ( select country city,max(locid)locid from <table> group by country city)T1 join select * from <table> T2 where T2.locid<>T1.locid 

Создайте новую таблицу с новым полем auto_increment и просто выберите их с GROUP BY в новую таблицу

Не проверено, но должно выглядеть так:

 INSERT INTO new_table(country, city) SELECT country, city FROM old_table GROUP BY country,city 

EDIT: вы можете удалить old_table и впоследствии переименовать new_table.

  1. Выберите уникальные записи и вставьте их в другую временную таблицу той же схемы.
  2. удалять все из своего стола
  3. Выберите и вставьте обратно из временной таблицы.
  4. Удалить временную таблицу