Удаление дублей в MySQL

Если вы только начинаете заниматься программированием, не стоит слишком разделять данные по таблицам, так как написание запросов к БД по извлечению информации может превратится в очень сложную, и иногда непосильную, задачу. Воспринимайте информацию из данной статьи, как пример того, что можно сделать в будущем.

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

Id клиента (id_client) Название модели (model_name)
1 Honda
2 Mazda
3 Honda
4 ВАЗ
И еще множество строк...

Изначально, когда только создавалась база, большой объем данных не предполагался, но когда кол-во записей перевалило за 10000 строк, дублирующиеся названия автомобилей стали заметно тормозить сортировку по ним да и дисковое пространство, занимаемое дублями, также наводило тоску. Решение - необходимо убрать дублирующиеся объемные текстовые данные из MySQL, заменив их на цифровые идентификаторы. Т.е. вместо Honda, Mersedes, Mazda, Honda, ВАЗ будут идти цифры-идентификаторы: 1, 2, 1, 3.

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

Для решения поставленной задачи необходимо создать 2 дополнительные таблицы: auto_model_list, в которой будет храниться только уникальные названия моделей с их id и таблицу соотвестсвия клиентов и их автомобилей - clients_auto.

Таблица моделей авто - auto_model_list
CREATE TABLE `auto_model_list` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`model_name` CHAR( 100 ) NOT NULL ,
UNIQUE (
`model_name`
)
) ENGINE = MYISAM
Таблица соответствия: у какого клиента какие автомобили - clients_auto
CREATE TABLE  `clients_auto` (
  `id_client` int(10) unsigned NOT NULL,
  `id_model` int(10) NOT NULL,
  KEY `id_client` (`id_client`)
) ENGINE=MyISAM

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

CREATE TABLE `clients_auto` AS SELECT `model_name`, `id_client` FROM `shop_orders` WHERE 1

С помощью этого запроса создается новая таблица clients_auto, с двумя колонками model_name и id_client, в которые копируется вся информация из аналогичных колонок таблицы shop_orders. Чтобы сразу изменить название столбца можно дописать в запросе к MySQL ...SELECT `model_name` AS my_name ...

Аналогично создаем таблицу auto_model_list и копируем в нее данные:

CREATE TABLE `auto_model_list` AS SELECT `model_name` FROM `shop_orders` WHERE 1

Теперь к таблице auto_model_list, с помощью phpmyadmin добавляем (можно, конечно и ALTER TABLE - смотря по обстоятельствам) колонку id: primary key, auto_increment, unsigned.

Поскольку данные сохранены в таблице MySQL clients_auto и избыточно продублированы, необходимость в колонке model_name таблицы shop_orders отпадает - колонку model_name следует удалить.

А вот удалить дублирующиеся названия в таблице MySQL auto_model_list, не потеряв логической связки данных с таблицей shop_orders несколько сложнее. Для этого необходимо также изменить данные в таблице clients_auto. Ниже приведен скрипт, которые успешно решает эту задачу.

// Выбираем только необходимые записи из auto_model_list
// Если в таблице хранится действиетельно большое кол-во данных -
// необходимо разделить запрос, воизбежание превышения лимита памяти
// выделяемого php-скрипту
$query = 'SELECT id, model_name
          FROM auto_model_list'
;
$result = mysql_query($query);
$array_unic = array();
while ($row = mysql_fetch_assoc($result))
  {
  if (!array_key_exists($row['model_name'], $array_unic))    
    $array_unic[$row['model_name']] = $row['id'];
  else
    $array_del[$row['id']] = $row['model_name'];
  }
 
$tmp_array_unic = array();
foreach ($array_del AS $key => $val)
  {
  if (!array_key_exists($array_unic[$val], $tmp_array_unic))
    {

    echo $array_unic[$val].' --- '.$val.' - '.$key."<br>";
    $tmp_array_unic[$array_unic[$val]] = $val;

    // Удаляем все дубли
    $query = 'DELETE FROM auto_model_list
              WHERE id != '
.$array_unic[$val].' AND
                    model_name = "'
.$val.'"';
    echo $query.'<br>';
    //$result = mysql_query($query);
    echo mysql_error().mysql_affected_rows().'<br>';

    // Изменяем ID для тех записей таблицы clients_auto
    // у которых нет соответствий в auto_model_list
    $query = 'UPDATE clients_auto
              LEFT JOIN auto_model_list ON (clients_auto.id = auto_model_list.id_model)
              SET id_model = '
.$array_unic[$val].'
              WHERE auto_model_list.id IS NULL'
;
    echo $query.'<br>';
    //$result = mysql_query($query);
    echo mysql_error().mysql_affected_rows().'<br><br>';
    }
  //echo '==='.$array_unic[$val].' --- '.$val.' - '.$key."<br>";
  }

Все запросы на удаление и изменение в БД $result = mysql_query($query) заккоментированы, так как сначала следует убедиться, изучив запросы, выведенные на экран, что все верно. После успешной отработки скрипта - не забудте указать индекс для auto_model_list.model_name, как UNIQUE KEY.

Если возникнут вопросы по использованию скрипта - оставляйте их в комментариях.

Опубликовано: 2011/06/20
HTML-код ссылки на эту страницу:
<a href="https://petrenco.com/mysql.php?txt=64" target="_blank">Удаление дублей в MySQL</a>
8340
Добавить комментарий
Ваш e-mail: (не виден посетителям сайта)
Ваше имя:
Комментарий:
Символы с картинки:
Только выделенные поля формы добавления комментариев обязательны к заполнению.