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

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

 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. Удалить временную таблицу