|
|||||||||||||||
|
Удаление дублей в MySQLЕсли вы только начинаете заниматься программированием, не стоит слишком разделять данные по таблицам, так как написание запросов к БД по извлечению информации может превратится в очень сложную, и иногда непосильную, задачу. Воспринимайте информацию из данной статьи, как пример того, что можно сделать в будущем.
Есть 1 таблица shop_orders, в которой содержатся, для примера, данные о покупателях, наименования моделей автомобилей и еще куча другой информации.
Изначально, когда только создавалась база, большой объем данных не предполагался, но когда кол-во записей перевалило за 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
Добавить комментарий
|