2、primary key与unique key
1)出发点/作用不同:前者是一行数据的唯一标识,后者只是用来避免数据重复。
2)前者的一个列或多个列必须全部为not null;如果其中一个列为null,在添加为主键时,会变为not null,如果再删除主键,列的nullable性质会变回去。后者的列可以为null。
3)一个表只能有一个primary key,可以有多个unique key。【一个表可以没有primary key吗???】
4)对于unique key对应的列,可以多次插入null(虽然也是一种重复);这是由索引的原理,即索引对null的处理决定的。
create table t(id int, key (id));如果有使用id的其他键(如foreign key),则使用其他键对他的命名;如果都没有命名,则使用id;如果一次指定了多个列作为键,则使用第一个列名作为键名。create table t(id int, key kismet(id));指定该key的名称constraint:不能使用,毕竟普通key并没有约束作用
create table t(id int, primary key (id));create table t(id int, primary key kismet(id));可以执行,但是名称不起作用create table t(id int, constraint kismet primary key(id));可以执行,但是名称不起作用
create table t(id int, unique key (id));命名规则与key不同,只使用第一列作为键名create table t(id int, unique key kismet(id));指定该key的名称create table t(id int, constraint kismet unique key(id));指定该key的名称
create table t(id int, foreign key (dage_id) references dage(id));可以执行,执行结果为创建了一个自动命名的foreign key和一个自动命名的普通key。create table t(id int, foreign key kismet(dage_id) references dage(id));可以执行,执行结果为创建了一个自动命名的foreign key和一个名称为kismet的普通key。create table t(id int, constraint kismet foreign key(dage_id) references dage(id));可以执行,执行结果为创建了一个名称为kismet的foreign key和一个名称为kismet的普通key。
RESTRICT(限制外表中的外键改动,默认的)CASCADE(跟随外键改动)SET NULL(设空值)SET DEFAULT(设默认值)NO ACTION(无动作)
CREATE TABLE `dage` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(32) default '',
PRIMARY KEY (`id`)
);
CREATE TABLE `xiaodi` (
`id` int(11) NOT NULL auto_increment,
`dage_id` int(11) default NULL,
`name` varchar(32) default '',
PRIMARY KEY (`id`),
KEY `dage_id` (`dage_id`),
CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`)
);
insert into dage(name) values('铜锣湾');
insert into xiaodi(dage_id,name) values(1,'铜锣湾_小弟A');
[SQL] delete from dage where id=1;
[Err] 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))
[SQL] insert into xiaodi(dage_id,name) values(2,'旺角_小弟A');
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`sample`.`xiaodi`, CONSTRAINT `xiaodi_ibfk_1` FOREIGN KEY (`dage_id`) REFERENCES `dage` (`id`))
show create table xiaodi;#查看键名称
alter table xiaodi drop foreign key xiaodi_ibfk_1;
alter table xiaodi add foreign key(dage_id) references dage(id) on delete cascade on update cascade;
CREATE TABLE People (
last_name varchar(50) not null,
first_name varchar(50) not null,
dob date not null,
gender enum('m', 'f') not null,
key(last_name, first_name, dob)
);
1)匹配全值:对索引中的所有列都指定具体的值。2)匹配最左前缀:你可以利用索引查找last name为Allen的人,仅仅使用索引中的第1列。3)匹配列前缀:例如,你可以利用索引查找last name以J开始的人,这仅仅使用索引中的第1列。4)匹配值的范围查询:可以利用索引查找last name在Allen和Barrymore之间的人,仅仅使用索引中第1列。5)匹配部分精确而其它部分进行范围匹配:可以利用索引查找last name为Allen,而first name以字母K开始的人。6)仅对索引进行查询:如果查询的列都位于索引中,则不需要读取元组的值。7)如果索引字段为A+B,查询A+C时,会使用A索引吗->会,使用explain可以证实
(4)限制
1)查询必须从索引的最左边的列开始。2)不能跳过某一索引列。例如,你不能利用索引查找last name为Smith且出生于某一天的人。3)存储引擎不能使用索引中范围条件右边的列。例如,如果你的查询语句为WHERE last_name="Smith" AND first_name LIKE 'J%' AND dob='1976-12-23',则该查询只会使用索引中的前两列,因为LIKE是范围查询。
1)Hash索引通过哈希函数计算Hash值进行检索,可以查到要查数据的行指针,从而定位数据。2)Hash值不取决于列的数据类型,一个TINYINT列的索引与一个长字符串列的索引一样大。3)Memory存储引擎支持非唯一hash索引,如果多个值有相同的hash code,索引把它们的行指针用链表保存到同一个hash表项中。
(2)限制
1)由于索引仅包含hash code和记录指针,所以,MySQL不能通过使用索引避免读取记录。但是访问内存中的记录是非常迅速的,不会对性造成太大的影响。2)不能使用hash索引排序。3)Hash索引不支持键的部分匹配,因为是通过整个索引值来计算hash值的。4)Hash索引只支持等值比较,例如使用=,IN( )和<=>。对于WHERE price>100并不能加速查询。
CREATE TABLE testhash (
fname VARCHAR(50) NOT NULL,
lname VARCHAR(50) NOT NULL,
KEY USING HASH(fname)
)ENGINE=MEMORY;
以上就是MySQL中key与index详细介绍的详细内容,更多请关注php中文网其它相关文章!
……