常用基础 - 主键 和 自增

  • 作者:KK

  • 发表日期:2017.11.27


主键的介绍

主键的值是唯一的,在一张表里只有一个主键,比如一张表的字段有 idcard 、 name 分别保存身份证号的姓名,那我们就可以将身份证号码字段设定为主键,因为身份证号码是唯一不重复的。

主键的角色就是用于作为一条数据的唯一标识,也就是数据的身份证号码,正常情况下我们不会修改主键的值,一旦创建就永久不动它,当然你要动是可以的。


建表后再添加主键

# 建表,此时没主键
CREATE TABLE people(
	idcard VARCHAR(18),
	name VARCHAR(20)
);

# 基于现有的表追加主键
ALTER TABLE people ADD PRIMARY KEY (`idcard`);

键表时就声明主键

CREATE TABLE people(
	idcard VARCHAR(18),
	name VARCHAR(20),
	PRIMARY KEY (`idcard`)
);

就是在字段列表后面追加一句PRIMARY KEY (主键字段名)即可


主键的测试

上面创建好带主键的表了,怎么玩呢……就这样先执行2条SQL语句试试插入重复的 idcard 值看它会有啥反应:

INSERT INTO people(idcard, name) VALUES('44182919990909123X', '小明');

INSERT INTO people(idcard, name) VALUES('44182919990909123X', '小红');

以上2条语句分别往表里插入小明和小红两个人的信息,可是第2条执行的时候就会报错说ERROR 1062 (23000): Duplicate entry '44182919990909123X' for key 'PRIMARY',意思大概就是“PRIMARY(主键)已经有了个相同的值 '44182919990909123X' ”,所以要保证插入的主键值不重复才行。


主键的使用场景思考

可实回到实际情况想想哦,我们建这些表存的数据并不止是存个人信息,还要存其它许多数据,拿一个博客来说,通常博客都有文章、文章分类和留言板,就只拿文章来说吧,文章表有标题、内容以及发表时间,这是最基本的,那用以下语句建个表:

CREATE TABLE article(
	title VARCHAR(18),
	content TEXT,
	created_time DATETIME
);

当我们要查看所有文章的时候,要获取这些数据当然就用SELECT * FROM article来查询全部文章,可是要获取指定一篇文章数据的时候怎么办?怎么办?对了,那可怎么办啊?

办法就是为文章表添加一个主键,来吧,先执行这条语句ALTER TABLE article ADD COLUMN(id INT(11));添加一个叫 id 的字段,然后再执行添加索引的语句ALTER TABLE article ADD PRIMARY KEY (id);,这样就将 id 字段设为主键了

然后我们插入文章时声明ID即可:

INSERT INTO article(id, title, content, created_time) VALUES(1, '文章标题1', '文章内容1', '2012-12-11 12:12:12');
INSERT INTO article(id, title, content, created_time) VALUES(2, '文章标题2', '文章内容2', '2012-12-12 12:12:12');
INSERT INTO article(id, title, content, created_time) VALUES(3, '文章标题3', '文章内容3', '2012-12-13 12:12:12');

查查询第2篇文章,只要执行SELECT * FROM article WHERE id = 2就行了,很好理解吧,把id = 2作为条件进行筛选,不是2的就不会取出来了。


自增

像上面那样虽然有了主键可以作为数据的唯一标识,可是也有麻烦之处:每次插入数据的时候,怎么知道哪个主键没被使用啊,不小心撞了数字就会因为主键重复而出错误提示了,所以通常我们会将主键设定为自增

自增的情况是这样的:我们插入数据的时候不需要指定自增字段,它会自动生成一个值,这个值是上一个值的+1(也可以设为+2,+3,具体查“MySql自增步长设置”)。

基于现有的主键字段将它设定为自增,要执行如下语句:

ALTER TABLE article CHANGE id id INT(11) NOT NULL AUTO_INCREMENT;
# 前半句  ALTER TABLE article CHANGE id   是说修改article的id字段。
# 后半句 id INT(11) NOT NULL AUTO_INCREMENT   是id字段的定义,就是修改成什么样子。

自增的测试

先执行TRUNCATE TABLE article;清空文章表的数据吧,然后执行如下三个语句:

INSERT INTO article(title, content, created_time) VALUES('文章标题1', '文章内容1', '2012-12-11 12:12:12');
INSERT INTO article(title, content, created_time) VALUES('文章标题2', '文章内容2', '2012-12-12 12:12:12');
INSERT INTO article(title, content, created_time) VALUES('文章标题3', '文章内容3', '2012-12-13 12:12:12');

以上三条语句并没有指定 id 字段,但是SELECT * FROM article;你会发现 id 字段会有值,而且是连续的,这就是自增的 id 字段了


主键+自增的实际应用场景描述

  1. ID的产生:实际情况下,一个博客在管理后台添加一篇文章时,程序代码执行SQL插入数据时不会指定ID,但表里由于设定了自增会产生一个新的ID。

  2. ID的列出:访客在博客前台查看文章列表时,前台可以执行SELECT * FROM article;查出所有文章的标题列到前台,并且包括了 ID。

  3. 访客要查看具体某一篇文章的内容,点击进去,这个链接通常会这样设置:http://xxx.com/article?id=3

  4. 访问链接时,后台收到了 id=3 的参数,于是就往数据库里查出WHERE id = 3的文章数据输出到文章详情页面,这样就实现了查询指定文章了,所以主键起到了数据唯一标识的作用。


用于数据关联

比如文章分类表(id、name)每个分类都有自己的ID,然后文章表加一个category_id字段,如果这个值是2,就是指分类表的 id=2 这条数据,这样就实现了关联文章的分类,甚至关联文章的发表用户等等,


建表时声明自增主键

一般建表时都会声明一个自增的主键,下面贴一段代码让大家需要的时候复制:

CREATE TABLE `article`(
	`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
	`category_id` TINYINT NOT NULL DEFAULT 0 COMMENT '分类ID',
	`user_id` INTEGER NOT NULL DEFAULT 0 COMMENT '用户ID',
	`title` VARCHAR(255) NOT NULL COMMENT '文章标题',
	`content` TEXT NOT NULL COMMENT '文章内容',
	PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;