处理重复数据
防止表中出现重复数据
方法:设置多重主键或添加唯一索引
primary key(valuelist)
valuelist是一个行字段列表,可以将多个或一个行字段设置为主键,插入数据时检查valuelist是否有重复的
注意,设置为主键的键不能为NULL,要加上NOT NULL的属性
下面是一个添加复合主键的例子,注意,复合主键和双主键不同,复合主键,仅当其中有一个主键不同即可插入
CREATE TABLE person_tbl
(
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
PRIMARY KEY (last_name, first_name)
);
往含有复合主键中插入两条主键相同的记录会报错,下面这一段代码往表中插入了两条相同的记录
insert into dupicate (first_name,last_name) values ("USTC","China");
insert into dupicate (first_name,last_name) values ("USTC","China");
执行到第二条语句的时候出现了错误,报错信息是
Error: ER_DUP_ENTRY: Duplicate entry 'Gao-Haihan' for key 'dupicate.PRIMARY'
想要在sql脚本中避免因为插入重复的主键数据导致异常退出,可以这样修改插入语句
insert ignore into dupicate (first_name,last_name) values ("USTC","China");
你也可以添加一个唯一索引来实现这一点
mysql CREATE TABLE person_tbl (
first_name CHAR(20) NOT NULL,
last_name CHAR(20) NOT NULL,
sex CHAR(10),
UNIQUE (last_name, first_name) );
统计重复数据
DISTINCT关键字可以帮助我们过滤掉重复数据,也可以使用GROUP BY实现这个功能
select DISTINCT columnlist from tablename
在tablename表中,重复的数据被剔除
select columnlist from tablename group by (columnlist)
按照元组columnlist将记录进行分组,这样也可以避免出现重复数据
删除重复数据
先创建一个临时表,将不含重复记录的原表拷贝存在这个临时表中
create table temp_table select columnlist from origin_table group by (columnlist);
这样这个表中存放的就是没有重复记录的原表
然后我们把原表删除,临时表名字改成原表即可
drop table origin_table;
alter table temp_table rename to origin_table;
MySQL导出数据
我们之前提到过,mysql的数据存储在文件中,本节是关于将文件中的数据提取为文本文件的内容
select ... into outfile
先看两个例子,先是将数据导出到指定路径文件中
select * from tablename into outfile 'filepath'
导出生成csv格式
select * from tablename into outfile 'filepath' fields terminated by ',' enclosed by '""' lines terminated by '\r\n'
将文件读入数据库,可以使用load data infile
注意,导出生成的文件是可读的,但是权限由服务器所有,所以你可能在删除该文件时产生麻烦
导出表作为原始数据
mysql提供了一个名为mysqldump的程序可以帮助我们转存储数据库。生成的不是数据库中记录,而是可以从头生成整个数据库的脚本
mysqldump -r username -p --no-create-info --tab=filepath databasename tablename
导出SQL格式的数据
mysqldump -u username -p databasename tablename > filename
相当于一个重定向,备份数据库的所有数据,则不需要指明tablename
备份所有数据库
mysqldump -u username -p databasename --all-databases > filename