MySQL數(shù)據(jù)表合并去重的簡(jiǎn)單實(shí)現(xiàn)方法

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;

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

insert into pep2(no) values('abc');

insert into pep (no) select no from pep2;

create table tmp select id,no from pep group by no;

 可能也會(huì)報(bào)錯(cuò)
 ```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
 ```
 解決：執(zhí)行以下兩個(gè)命令：
 ```
 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';
 ```

drop table pep;
alter table tmp rename to pep;

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