数据库技术提升-MySQL数据库原理、设计与应用【1.7】

3.2.4 主键约束

        在 MySQL中,为了快速查找表中的某条信息,可以通过设置主键来实现。主键可以唯一标识表中的记录,类似指纹,身份证用于标识人的身份一样。
        主键约東通过 PRIMARY KEY定义,它相当于唯一约束和非空约束的组合,要求被约東字段不允许重复,也不允许出现 NULL,值,每个表最多只允许含有一个主键,主键约束的创建也分为列级和表级。其基本语法格式如下。
1列级约東
字段名 數据类型 PIMEY EY
·表级约束
FRIMARY KEY(字段名1，字段名2，…)
        在上述语法中,表级约束的字段若只有一个,则为单字段主键与列级约束添加的效果相同;若有多个,则为复合主键。复合主键需要用多个字段来确定一条记录的唯一性,类似于复合唯一键。
为了让读者更好地理解,下面通过案例演示主键约束的使用及注意事项。
(1)创建 my_primary 表,为id 字段添加主键约束。

(2)使用DESC查看表结构,执行结果如下。

从上述结果可以看出,id 字段的 Key 列为 PRI,表示该字段为主键。同时,id字段的Nul 列为 NO,表示该字段不能为 NULL。
(3)插人记录进行测试,具体 $Q1.语句及执行结果如下。

从上述结果可以看出,添加主键约束后,插入重复值或 NULI.值会失败(4)为一个现有的表添加或删除主键约束,具体 SQL,语句及执行结果如下。

        从上述结果可以看出,在删除id字段的主键约束后,该字段的非空约束并没有被同时删除。若需要删除id字段的非空约束,执行第③步删除操作即可。

3.3 自动增长

        在为数据表设置主键约束后,每次插人记录时,都需要检査主键的值,防止插人的值重复导致插人失败,这会给数据库的使用带来很多麻烦。为此,可以利用 MySQL,提供的自动增长功能来自动生成主键的值，
自动增长功能通过 AUTO_INCREMENT 来实现,其基本语法格式如下，
字段名 数据类型 A IHE
在使用 AUTO INCREMENT 时,需要注意以下 4点。
        (1)一个表中只能有一个自动增长字段,该字段的数据类型是整数类型,且必须定义为键,如 UNIQUE KEY PRIMARY KEY。
        (2)若为自动增长字段插人 NULL,0,DEFAULT 或在插人时省略该字段,则该字段就会使用自动增长值:若插人的是一个具体值,则不会使用自动增长值。
        (3)自动增长值从1开始自增,每次加1。若插人的值大于自动增长的值,则下次插人的自动增长值会自动使用最大值加1:若插人的值小于自动增长值,则不会对自动增长值产生影响。
        (4)使用 DEL.ETE别除记录时,自动增长值不会减小或填补空缺,
为了让读者更好地理解,下面通过案例演示自动增长的使用及注意事项(1)创建 my_auto 表,为id字段添加自动增长。

在上述结果中,最后一条记录的id字段在插人时使用了 0,MySQL 会忽略该值,使用自动增长值(即 id 最大值 6 进行加 1),从而得到 id 的值为 ?。
(5)使用 SHOW CREATE TABLE 查看自动增长值,执行结果如下

在上述结果中,"AUTOINCREMENT=8"用于指定下次插人的自动增长值为8。若在下次插人时指定了大于8的值,此处的8会自动更新为下次插人值加1。(6)为现有的表修改或删除自动增长,具体SQ1.语句及执行结果如下

        需要注意的是,在为字段删除自动增长并重新添加自动增长后,自动增长的初始值会自动设为该列现有的最大值加1。在修改自动增长值时,修改的值若小于该列现有的最大值，则修改不会生效。
小提示:通过“SHOW VARIABLES LIKE 'auto_increment%';"可以查看 MySQL,中用于维护自动增长的变量分别是auto_increment_increment(默认值1)和 auto_incrementoffset(默认值为 1),通过更改这两个变量可以改变自动增长的计算方式,读者可以参考官方手册中的详细说明。

3.4 字符集与校对集

        MySQL, 默认使用的字符集为 latinl(1sO-8859-1的别名),这是一种单字节编码的字符集。由于中文汉字属于多字节编码的字符,若要保存中文,需要设置成其他支持中文的字符集,如 GBK,UTF8。校对集是字符之问的比较关系,在比较,排序等操作时都会用到校对集。本节将针对 MySQ1, 中的字符集与校对集进行详细讲解。

3.4.1 字符集与校对集概述

1.字符集
        字符(character)指计算机中保存的各种文字和符号,包括各种国家的文字,标点符号图形符号,数字等。由于计算机采用二进制保存数据,用户输人的字符将会按照一定的规则转换为二进制后保存,这个过程就是字符编码(character encoding),将一系列字符的编码规则组会起来就形成了字符集(character set,charset)。
        在计算机的发展历史中,出现了许多字符集。MySQL,也提供了各种字符集的支持,通过"SHOW CHARACTER SET;"可以查看可用字符集,如图 3-1 所示

图 3-1 显示了字符集名称(Charset),描述信息(Description),默认校对集(Defaultcollation)和单字符的最大长度(Maxlen)。
常用字符集有 latin1,gbk.ut8,具体说明如表 3-9 所示。

从表 3-9 中可以看出,单字符占用的存储空间越多,所支持的语言越多。另外,表中列举的字符集都向下兼容 ASCII(美国信息交换标准代码),若输人的字符在 ASCI 范围内(0x00一0x7F),则编码结果是完全相同的。

        由于历史原因,MySQl, 中的 utf8 编码与标准的 UTF-8(RFC 3629)存在一些差别。标准的 UTF-8 规定一个字符最多使用4字节,而 MySQL, 中的 ut[8 编码一个字符最多使用 3字节,这导致 UTF-8 中的一些特殊字符(如 emoji 符号)无法在 MySQL,的 utf8 编码中使用。为了解决这个问题,MySQL从5.5.3版本开始新增了ut[8mb4 编码,将一个字符扩展到4字节。因此，如果要考虑支持 RFC 3629 规范,应使用 utf8mb4 编码。
2.校对集
        MySQL,中提供了许多校对集,用于为不同字符集指定比较和排序规则。例如,latinl字符集默认的校对集为 latin1 swedish ci。校对集的名称由””分隔的3部分组成,开头是对应的字符集,中问是国家名或 general,结尾是 ci、cs或 bin。其中,ci表示不区分大小写,c 表示区分大小写,bin 表示以二进制方式比较通过“SHOW COI.ATION:”可以査看 MySQ1. 可用的校对集,如图 3-2 所示。

        图 3-2 显示了校对集名称(Collation),对应的字符集(Charset),校对集 ID(ld),是否为对应字符集的默认校对集(Delault),是否已编译(Compiled)以及排序的内存需求量(Sortlen)。

3.4.2 字符集与校对集的设置

        根据不同的需求,字符集与校对集的设置分为4个方面,分别是 MySQL,环境,数据库数据表以及字段。下面分别进行讲解。
1.MySOL 环境
        使用 MySQL,命令“SHOW VARIABL.ES LIKE tharacter%';"可以查看与字符集相关的变量,输出结果如下所示。

        上述结果表示当前会话(Session)使用的字符集,在不同客户端环境中的输出结果可能不同。这里所说的会话,是指从客户端登录服务器到退出的整个过程。例如,依次打开两个客户端并登录服务器,就产生了两个会话,不同客户端属于不同的会话。由此可见,不同的客户端可以指定不同的字符集环境配置,服务器会按照不同的配置进行处理。接下来通过表 3-10 对输出结果中的变最名进行详细说明。

        在表 3-10 中,读者应重点关注的变量是 character_set_client,character_set_connection、character_set_results和character_set_server,具体解释如下。(1)character_set_server 决定了新创建的数据库默认使用的字符集。需要注意的是,数据库的字符集决定了数据表的默认字符集,数据表的字符集决定了字段的默认字符集。由于 character_set_server 的值为 latinl,因此在前面的学习中,创建的数据库,数据表和字段的默认字符集都是 latinl.
        (2)character set client,character set connection和character set results分别对应客户端,连接层和查询结果的字符集。通常情况下,这3个变量的值是相同的,具体值由客户端的编码而定,从而使客户端输人的字符和输出的查询结果都不会出现乱码。通过“set 变量名三值:"命令可以更改变量的值,示例命令如下
set character set client gbk?
sot charactor sot connect ione gbk!
set character set esults abk;
由于上述命令输人比较麻烦,在 MySQL,中还可以通过“set names 字符集:"直接修改上述3个变量的值，即“set names gbk;”。
小提示:
        (1)使用 set 或 set names 修改字符集只对当前会话有效,不影响其他会话,且会话结来后。下次会话仍然使用默认值。
        (2)与character_set_connection,character_set database,character set server 对应的校对集分别通过变量collation connection,collation database,collation server 来指定。
        (3)若字段使用 utf8 字符集,而客户端使用 gbk 字符集,MySQ1.会自动进行编码转换。由于 utf8 和 gbk 本质上是不同的字符集,虽然大部分常见的字符可以转换成功,但若遇到其中一个字符集中没有的特殊字符，则可能会出现乱码。
2.数据库
        在创建数据库时设定字符集和校对集的语法格式如下
[UEFAULT]CHARACIER SET[e]charset name

IDEFAULT] COUATE [e] collation name 

在上述语法中,CHARACTER SET用于指定字符集,COLLATE 用于指定校对集若仅指定字符集,表示使用该字符集的默认校对集;若仅指定校对集,表示使用该校对集对应的字符集。不例5O1.语付如下，
1创建数据库,指定字符集为 utt8,使用默认校对集 utf8 gemeral ciCREATE, DATARASE mYI 1 CHARACTER SET Ut f9,
!创建数据库,指定字符集为ut8,校对集为 mtf8 bin
CREATE DATAEASE mYdh 2 CHARALTHR SET utfg coLIATE utfe bin!
3.数据表
数据表的字符集与校对集在表选项中设定,语法格式如下
[OEFAULT]CHARACIER SEF[]charset name[srAuLT]coATk[=] collation name
上述语法与指定数据库字符集的语法类似。若没有为数据表指定字符集,则自动使用数据库的字符集。示例 SQL语句如下
CREATE, TABLE, my charset (
uSTnmG VABCHAR. (20)
’ CHARASIER SET ULE8 COLTATE utE8 bin:
上述 SQ1.语句指定数据表字符集为 utf8,校对集为 utf8 bin。
小提示:在 MySQL中,还可以将CHARACTER SET简写为 CHARSET。例如,通过“SHOW CREATE TABlE my_charset;"可以看到设置字符集和校对集的语法形式为DEFAULT CHARSET=uH8 COLLATE=ut8 bin。

4.字段
字段的字符集与校对集在字段属性中设定,语法格式如下
cHARAcIER, sEf charset nane][ooutArE collation nane]
在上述语法中,若没有为字段设定字符集与校对集,则会自动使用数据表的字符集与校对集。不例 SQ1.语句如下。
CEEATE, TABLE, my charset (
username VARHAR.(20) CHARACTPR, SET ULI8 COLIATE utL8 bin
上述 SQ1, 语句指定 username 字段的字符集为 utf8,校对集为 utf8 bin。

3.5 动手实践:设计用户表

        数据库的学习在于多看,多学,多思考,多动手,只有将理论与实际相结合,才能够体会数据库开发与管理的重要性,展现知识学习的价值与力量。接下来请结合本章所学的知识完成用户表的设计
【实践目标】
本实践的目标就是能够根据文字提示,完成对用户表的设计,为表中的字段设置合理的数据类型、约束以及字符集。
实践需求】
在电子商务网站中,提供了用户注册功能,当用户在注册表单中填写信息后,提交表单，就可以注册一个新用户。为了保存用户的数据,需要在数据库中创建一张用户表,该表需要保存的用户信息如下。
。用户名:可以使用中文,不允许重复,长度在 20 个字符以内
。手机号码:长度为 11个字符。
。性别:有男女、保密3种选择。
。注册时间:注册时的日期和时间。
。会员等级:表示会员等级的数字,最高为100。
【动手实践】
1.创建用户表
根据需求创建用户表,为每个字段设置合理的数据类型。具体SQL语句如下

从上述结果可以看出,user表中只有一个用户,即小明。读者也可以尝试加更多的用户。

3.6 本章小结

        本章主要讲解了常用的数据类型,表的约束,自动增长,以及字符集和校对集。这些内容十分零碎,但又非常重要,需要通过实践练习加以透彻理解。数据类型与约束是本章的重点,希望读者掌握每种数据类型和约束的适用场景,可以结合表的实际情况去运用。