Mysql中让两个字段不同时相同的方法

有时候我们会遇到这样的一种情况：有一些不同的专业，每个专业中有一些不同的学号，专业+学号能对应到个人。这时，应该如何在数据库中定义列，来保证专业+学号对应的学生的唯一性呢？

把学号定义成唯一（UNIQUE）显然是行不通的，因为A专业和B专业都存在1000这样一个学号。

 

方法1：联合主键

PRIMARY KEY(major,id);

不推荐。因为联合主键会给表的维护带来不便。另外，可能已经设定好了主键。

 

方法2：建立联合唯一约束（联合唯一索引）（推荐）

联合唯一约束：（创建唯一性约束时会自动创建唯一性索引）

ALTER TABLE student ADD CONSTRAINT major_id UNIQUE (major,id);

联合唯一索引：

CREATE UNIQUE INDEX major_id_index ON student(major,id);

二者的区别：

①当往数据库中插入20条数据，其中5条数据重复时，如果用唯一性约束，结果必然是20条数据全部被拒绝插入。如果使用唯一性索引，若打开了“忽略重复值”开关（插入时在INSERT后面加上IGNORE，即：INSERT IGNORE INTO students () VALUES ();），则会将15条不重复的数据正确插入，5条重复数据被忽略。

②如果某列多行值为null，在mysql 的innodb引擎中，是允许在唯一索引的字段中出现多个null值的。

根据NULL的定义，NULL表示的是未知，因此两个NULL比较的结果既不相等，也不不等，结果仍然是未知。根据这个定义，多个NULL值的存在应该不违反唯一约束，所以是合理的，在oracel也是如此。

 

创建新表的时候：

CREATE TABLE `test` ('aaa' varchar(16) NOT NULL default '', 'bbb' varchar(16) NOT NULL default '', 'ccc' int(11) UNSIGNED NOT NULL default 0, KEY `sindex` (`aaa`,`bbb`,`ccc`) ) ENGINE=MyISAM COMMENT='';

这样就在 aaa、bbb、ccc 3列上建立联合索引了。

如果表已经建好了，那么就在phpmyadmin里面执行：
alert table test add INDEX `sindex` (`aaa`,`bbb`,`ccc`)

就可以在这3列上建立联合索引了

 

 

 

数据库表中去除多个字段完全重复的数据，保留唯一

select distinct * into #t from tb 
truncate table tb
insert into tb select * from #t

一条sql语句效率比较低，建临时表效率比较高些

create table mayong as (select distinct* from sms.vehicle);
delete from sms.vehicle ;
insert into sms.vehicle select * from mayong;

 

 

SQL 查询重复数据，多字段联合重复

SELECT *
FROM pagelist a
WHERE ((SELECT COUNT(*)
FROM pagelist
WHERE ntitle = a.ntitle) > 1)
ORDER BY ntitle DESC
--查询重复的数据

Delete from  pagelist Where ID Not In (Select Max(ID) From pagelist 
Group By ntitle)

--删除重复的数据，保留一个

 delete from dict  where pid=689 and ID in(
select id from dict s1
where id not in (
select max(id) from dict s2
where 
 s1.dict_value=s2.dict_value 
)
)

 delete from ks_kucunribao where ID in(
select id from ks_kucunribao s1
where id not in (
select max(id) from ks_kucunribao s2
where 
 s1.gangkouID=s2.gangkouID 
and s1.intime=s2.intime
and s1.orderindex=s2.orderindex 
and s1.船名=s2.船名 
and  s1.日期= s2.日期 
and  s1.委托人= s2.委托人 
and  s1.收货人= s2.收货人 
and  s1.货种= s2.货种
and  s1.垛位= s2.垛位
and  s1.库存= s2.库存
and  s1.卸船开始日期= s2.卸船开始日期
and  s1.卸船完成日期= s2.卸船完成日期
and  s1.卸船数= s2.卸船数
and  s1.前港结存= s2.前港结存
and  s1.全船结存= s2.全船结存
and  s1.货主= s2.货主
and  s1.入库时间= s2.入库时间
and  s1.货代= s2.货代
and  s1.昨日结存= s2.昨日结存
and  s1.当日调进= s2.当日调进
and  s1.当日调出= s2.当日调出
and  s1.差异= s2.差异
and  s1.库厂= s2.库厂
and  s1.位置= s2.位置
and  s1.来源国= s2.来源国
and  s1.船代= s2.船代
and  s1.提单数= s2.提单数
and  s1.水尺数= s2.水尺数
and  s1.疏港= s2.疏港
and  s1.今日结存= s2.今日结存
and  s1.备注= s2.备注
and  s1.货种类型= s2.货种类型
and  s1.dictID= s2.dictID
 
)
)

SELECT m.* FROM [数据$] m,(
SELECT [小区名称], [楼号],[单元号], [门牌号]
FROM [数据$] 
GROUP BY [小区名称], [楼号],[单元号], [门牌号]
HAVING COUNT(1)>1

) AS m1
WHERE m.[小区名称]=m1.[小区名称] AND m.[楼号]=m1.[楼号] AND m.[单元号]=m1.[单元号]  AND m.[门牌号]=m1.[门牌号]