Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to merge and deduplicate MySQL data tables, the content of the article is of high quality, so the editor will share it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Scene:

The crawled data generates a data table with the same structure as another main table, which needs to be merged + deduplicated.

Solution: (direct example)

First create two tables pep,pep2, where pep is the main table

CREATE TABLE IF NOT EXISTS `pep/ pep2` (`id` INT UNSIGNED AUTO_INCREMENT, `no` VARCHAR (100) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Then insert two pieces of data into pep, and insert the same piece of data into pep2 as in pep

Insert into pep (no) values ('abc'); insert into pep (no) values (' caa'); insert into pep2 (no) values ('abc')

Insert data from pep2 into pep

Insert into pep (no) select no from pep2

Group to recreate a new temporary table tmp

Create table tmp select id,no from pep group by no

Note: after creating this table, the id field type is no longer the primary key increment.

May also report an error ````Syntax error or access violation: 1055 Expression # 1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXX.Y.ZZZZ' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by ````solution: execute the following two commands: ```mysql > set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' Mysql > set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; ```

Delete the pep table and rename the tmp table to pep

Drop table pep;alter table tmp rename to pep

Check the desc structure and select * from pep to find that the field type of id has changed. Here, you need to change it back to the original type.

Alter table pep add primary key (id); alter table pep modify id int auto_increment

You can also use join to remove weight, and more quickly, you can add a field (which can be the md5 value of several fields), create a unique index unique for this field, and automatically filter out duplicate data when you insert data later.

On how to merge to duplicate MySQL data table to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.