mysql插入数据时,去掉重复的数据;
1. 利用insert ignore into语句去重
mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'J', 'T'); Query OK, 1 row affected (0.00 sec) mysql> INSERT IGNORE INTO person_tbl (last_name, first_name) -> VALUES( 'J', 'T'); Query OK, 0 rows affected (0.00 sec)
2.查询过滤重复数据
①. 利用MySQL的关键字DISTINCT去重
mysql> SELECT DISTINCT last_name, first_name -> FROM person_tbl;
②. 利用group by进行去重
mysql> SELECT last_name, first_name -> FROM person_tbl -> GROUP BY (last_name, first_name);
3. 删除表中的重复数据
思路:先建一个临时表,把不重复的数据数据存放在临时表中,然后将原表删除,再将临时表rename成正式表
mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex); mysql> DROP TABLE person_tbl; mysql> ALTER TABLE tmp RENAME TO person_tbl;