3.1.3 字符串类型
MySQ1. 中的字符串类型分为 CHAR、VARCHAR、TEXT 等多种类型,不同数据类型具有不同的特点,具体如表3-5所示。
1.CHAR 和 VARCHAR 类型
CHAR 和 VARCHAR类型都用来保存字符串数据。不同的是,VARCHAR可以存储可变长度的字符串。在 MySQ1.中,定义 CHAR 和 VARCHAR 类型的方式如下所示。
CHAR (M)
VABCHAR(M)
在上述定义方式中,M指的是字符串的最大长度。为了对比 CHAR 和 VARCHAR 之间的区别,下面以 CHAR(4)和 VARCHAR(4)为例进行说明,具体如表 3-6 所示。
从表 3-6 中可以看出,对于 CHAR(4)无论插人值的长度是多少,所占用的存储空间都是4字节。而VARCHAR(4)占用的字节数为实际长度加1.
2.TEXT 类型
TEXT 类型用于保存大文本数据,例如,文章内容,评论等比较长的文本。它的类型分为4种,具体如表 3-7 所示。
TEXT类型所能保存的最大字符数量取决于字符串实际占用的字节数。兴脚下留心
(1)CHAR和 VARCHAR类型在插入教据时,若字特串末尾有空格,CHAR 类型会自动去掉空格后保存,而VARCHAR,TEXT 类型会保留空格。
(2)在使用“=”等运算符对CHAR,VARCHAR,TEXT进行比较时,字符串末尾的空格会被忽略。倒如，使用 WHERE查询a字符串,查询结果中可能包含a后面有空格的情况，反之，若查询条件字符串末尾有空格(如a"),空格也会被忽略。
(3)由于默认情况下创建的数据库和表使用的校对集(latinl_swedish_ci)对大小写不敏感,因此 CHAR,VARCHAR、TEXT、ENUM,SET 类型都不区分大小写。例如,使用WHERE 查询?字符串,则“a"和"A”都会被查询出来。而 BINARY,VARBINARY,BLOB
类型区分大小写，这是因为它们使用二进制方式保存数据，(4)MySQL默认规定一条记录的最大长度是 65535 字节,一般来说,字段分配的存佛空间和额外开销加在一起不能超过 65535 字节,如果超过了这个限制,在 SOL.严格模式下表会创建失败,提示 Row size too large。但 TEXT和BL,OB 类型字段的存储空间不受此限制,它们只占用额外开销(大约 12 字节)。
(5)在没有超过65535 限制的情况下,CHAR 字段的 M最大值为 255,VARCHAR 字段的 M 的最大值取决于字符集,常用的字符集有 |atin1(跌认)、gbk和 ut[8,对应的 M最大值分别为 65533、32766和21844,若表中只有一个字段且设置了非空约束,M 可达到最大值，否则 M的最大值会减小。
(6)从执行效率上来说,TEXT和 BL.OB不如 CHAR,VARCHAR 类型,建议只有当需要保存大量数据时，才选择使用工EXT或BIOB类型。多学一招:二进制方式比较字符串
若要在 CHAR,VARCHAR、TEXT类型的字符串比较时严格区分大小写,有多种实现方式，接下来讲解两种比较常见的方式。
(1)使用 BINARY关键字。在字段名或其个值的前面加上 BINARY关键宇可以将类型转换为二进制，转换后进行比较就可以严格区分大小写和空格。具体示例如下。
(2)设置字段的校对集。latinl、gbk、utf8 编码认的校对集分别为 latinl_swedish_cigbk_chinese ci.utf8 general ci,将其分别改为 latinl bin,gbk bin,utf8 bin 即可区分大小写，但在比较时仍会怒略字符事末尾的空格。具体示例如下
3.ENUM 类型
ENUM 类型又称为枚举类型,定文ENUM 类型的方式如下所示，
EKUM(“值 1','值 2',"值 3',…,"偵 n'}
在上述格式中,('值 1',值 2,"值 3',…，值 n')称为枚举列表,ENUM 类型的数据只能从枚举列表中取,并且只能取一个。ENMU 类型的使用示例如下
+① 创建表
my3gl> CREATE TABLE my enm (gender uH!'male", "fenale')),"@ 插人商条测试记录
mySgl> INSERT INTO mY em VAIUES ('male'), ('female") .
+③ 查询记录,查询结果为"female"
myBgl>SEECT + M y cnum WERE gender="fenale":
①插人枚举列表中没有的值测试
mya1> INSERT INTO mY mm VAIUS ('m").
EROR, 126501000j: Data truncated for colunn "gender' at row 1
在 MySQL,中,枚举列表最多可以有 65535 个值,每个值都有一个顺序编号,实际保存在记录中的是顺序编号,而不是列表中的值,因此不必担心过长的值占用空间。但在使用SELECT.INSERT 等语句进行操作时,仍然使用列表中的值。
4.SET 类型
SET类型用于保存字符串对象,其定义格式与ENUM 类型类似,具体如下
SET“值 1'。'值 2',"值 3',…,"值 n')
SET类型的列表中最多可以有64个值,且列表中的每个值都有一个顺序编号,为了节省空间,实际保存在记录中的也是顺序编号,但在使用 SELECT,INSERT 等语句进行操作时,仍然要使用列表中的值。
SET类型与ENUM的区别在于,它可以从列表中选择一个或多个值来保存,多个值之间用逗号”,"分隔。具体使用示例如下。
小提示:
(1)ENIJM 类型类似于单选柜,SET类型类似于复选框
(2)ENUM和SET类型的优势在于规范数据本身,限定只能插入规定的数据项,节省了存储空间,查询速度比CHAR,VARCHIAR 类型快。
(3)ENUM和SET类型列表中的值都可以使用中文,但必须设置支持中文的字符集,
宿了第3章数据类型与约束
例如 CREATE TABLE my_enum(gender ENUM('男',女))CHARSET=GBK;。(4)ENUM和SET类型在填写列表,插入值,查找值等操作时,都会自动忽略末尾的空格,
5. BINARY 和 VARBINARY 类型
BINARY和 VARBINARY类型类似于CHAR和 VARCHAR,不同的是,它们所表示的是二进制数据。定义 BINARY和 VARBINARY类型的方式如下所示。
BTNARY (M)
或
WARBINARY (M)
在上述格式中,M是指二进制数据的最大字节长度。BINARY类型的长度是固定的，如果数据的长度不足最大长度,将在数据的后面用“\0"补齐,最终达到指定长度。例如,指定数据类型为 BINARY(3),当插人a时,实际存储的数据为“a\0\o",当插人 ab时,实际存储的数据为“abo”。
接下来演示 BINARY 和 VARBINARY 的使用示例,具体如下所示。
从上述示例可以看出,在查询 BINARY类型时,查询条件字符串也需要加上"\0"填充符,否则查询不到该记录,并且 BINARY 和 VARBINARY 都区分大小写。
6.BLOB 类型
BI.OB类型用于保存数据量很大的二进制数据,如图片,PDF 文档等。BL.OB类型分为4 种,具体如表 3-8 所示。
需要注意的是,BL.OB类型与 TEXT类型很相似,但 BLOB 类型数据是根据二进制编码进行比较和排序,而TEX工类型数据是根据文本模式进行比较和排序。接下来演示 BIOB 的使用示例,具体如下所示。
#① 创建表,插人测试记录
mYegl>CREATE TAELE my bleb i Lol :
mysgl> INsERT INTo my blob vhlurs i'data'):+②查询记录,查询结果为"data”
myagl> 5LEer b FRod my blob wEpE b "data',
#. 查询记录,由于区分大小写,查询结果为空
myeg1> SELEoT b FRM my blob WHERE b-"Data".
从上述示例可以看出,BLOB类型在查询时区分大小写，
多学一招:ISON 数据类型
MySQL从5.7.8版本开始提供了JSON数据类型。JSON是一种轻量级的数据交换格式,由 JavaScript 语言发展而来,其本质是一个字符串。MySQL.中JSON 类型值常见的表现方式有两种,分别为JSON数组和JSON对象,示例如下。
+① J5 数组
["abc",10,mull,true, false]
+② J5d 对象
!"k1":"value"。"k2":10}
从上述示例可知，JSON数组中保存的教据可以是任意类型。其中，JSON数组使用“「和“]”符号实现,多个值之间使用逗号分隔,如 10 和 null;JSON 对象使用“{”和“}”符号实现,保存的教据是一组键值对,如 k] 和k2 是键名(或称为属性名),而 value 和 10 是键名对应的值。
与直接使用 MySQL,字符串类型相比,JSON数据类型具有自动验证格式,优化存储格式的优点。JSON 数据类型所需的空间大致与1ONGBLOB或LONGTEXT相同,且不能有跌认值。下面演示JSON数据类型的使用示例，具体如下所示。
从上述示例可以看出，JSON数据类型的字段以字符串的方式插入数据即可
3.2 表的约束
为了防止数据表中插人错误的数据,MySQL,定义了一些维护数据库完整性的规则,即表的约束。常见约束分为5种,分别是默认约束,非空约束,主键约束,唯一约束和外键约束。外键约束比较复杂,涉及多表操作,将在后面的章节中讲解,本节主要讲解其余4种约束的使用方法。
3.2.1 默认约束
默认约束用于为数据表中的字段指定默认值,即当在表中插人一条新记录时,如果没有给这个字段赋值,那么,数据库系统会自动为这个字段插人默认值。默认值是通过DEFAULT 关键字定文的,其基本语法格式如下。
字段名 数据类型 DEEULT 默认值:
需要注意的是,BLOB、TEXT数据类型不支持默认约束。下面通过案例演示默认约束的使用及注意事项
(1)创建 my_default 表,准备 name 和 age 两个字段进行测试,为age 添加默认约束,设置默认值为 18。
在上述示例中,由于 name 和 age 字段没有设置非空约束,在插人记录时省略了这两个字段的值,则分别使用默认值 NULL, 和 18。为 age 字段设置默认值 18后,插人 NULL, 值，则保存结果为 NULL,不使用默认值。在为有默认值的字段指定数据时,可以通过DEFAULT 关键字直接指定其使用默认值。
(4)为现有的表添加或删除默认约束,具体SOL语句及执行结果如下
删除戳认约束
myS1>ALTER TAIK my default MDIpY age INT USGNEDFCuety oK, o rowg afLected [0.0l sec)
Records:0 puplicates:0 warnings:0
+② 添加獸认的束
MYSG1>ALTER TABIE mY defalt MODIEY Age INT UNSIGNED DEFAULT 18.Ouery ok,O rows affected (0.0l see)
Records:0 Duplicates:0 warnings:0
通过上述示例可以看出,使用 ALTER TABLE 修改列属性即可添加或删除默认约束
3.2.2 非空约束
非空约束指的是字段的值不能为 NULL,在 MySQL,中,非空约東是通过 NOT NULL定文的,其基本语法格式如下。
为了让读者更好地理解,下面通过案例演示非空约束的使用及注意事项。
(1)创建 my_not_null 表,准备 nl、n2 和 n3 字段进行测试,为 n2 和 n3 设置非空约束，为 n3 设置默认值为 18。
在上述结果中,Nu 列的值为NO表示该字段添加了非空约束。需要注意的是,添加了非空约束的 n2 字段的 Default 为 NULL,表示未给该字段设置默认值,而不能将其理解为默认值为 NULL,否则在插人数据时,若 n2 字段为 NULL.,MYSQL, 会报“Column 'n2cannot be nul!"错误提示。另外,在创建数据表时,非空约束与值为 NULL, 的默认约束(DEFULT NULL)不能同时存在,否则数据表在创建时会失败,提示“Invalid default valuefor 'h2"错误。
(3)插人记录进行测试,具体 SQL语句及执行结果如下
在上述示例中,由于 n2 字段不能为 NULL,且没有默认值,在插人时不能插人 NULL或省略该字段;n3字段设置了默认值,在插人时可以省略该字段,但不能插人 NULL。小提示，为现有的表添加成制除非空的来的方式与默认约束类似，使用ALTERTABIE修改列属性即可。但若目标列中已经保存了NUIL值,添加非空约来会失败,提示"Invalid use of NU!l. value”,只要将 NULI,值改为其他值即可解决。
3.2.3 唯一约束
唯一约束用于保证数据表中字段的唯一性,即表中字段的值不能重复出现。唯一约束是通过 UNIQUE 定义的,其基本语法格式如下所示。
在上述语法格式中,列级约束和表级约束是 MySQL,中的两种定义约束的方式。列级约東定义在一个列上,只对该列起约束作用;表级约束是独立于列的定义,可以应用在一个表的多个列上。
为了让读者更好地理解,下面通过案例演示唯一约束的使用及注意事项。
(1)创建 my_unique_l表和 my_unique 2表,分别通过列级约束和表级约束的方式添加唯一约束。具体 SQL语句和执行结果如下
在上述结果中,如果在 id 和 username 的 Key 列看到 UN1,说明唯一约束已经添加成功,这两个字段是唯一键。值得一提的是,当表级约束仅建立在一个字段上时,其作用效果与列级约束相同。
(2)为含唯一约束的字段插人记录,具体SQL语句及执行结果如下
从上述结果可以看出,添加唯一约束后,插人重复记录会失败。其中,username 字段出现了重复值 NULL,这是因为 MySQL,的唯一约束允许存在多个 NULL, 值。(3)添加和删除唯一性约束。若为一个现有的表添加或删除唯一约束,无法通过修改字段属性的方式操作,而是按照索引的方式来操作。关于索引的概念和使用会在后面的章节中详细讲解,读者此时只需了解用到的这些操作即可。具体 SQL,语句及执行结果如下
在上述操作中,第③步的执行结果中出现了“UNIQUE KEYid(id)”,它是添加唯一约束的完整语法,即 UNIQUE(id)的完整形式,如下所示。WIIEKY素引名字段列表)
上述语法表示在添加唯一约束时创建索引,用于加快查询速度。其中,索引名可以自己指定,也可以省略,MYSQL,会自动使用字段作为索引名。当需要对索引进行删除时,需要指定这个素引名。
(4)创建复会唯一约束。在表级唯一性约束创建时,UNIQUE()的字段列表中,可以加多个字段,组成复合唯一键,其特点是只有多个字段的值相同时才视为重复记录。具体S01.语句及执行结果如下。
