5.1.4 字符串类型
字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其它数据，比如图片和声音的二进制数据。字符串可以进行区分或者不区分大小写的串比较，另外，还可以进行模式匹配查找。MySQL中字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。【表5.5】列出了MySQL中的字符串数据类型。
表5.5MySQL中字符串数据类型
|
类型名称 |
说明 |
存储需求 |
|
CHAR(M) |
固定长度非二进制字符串 |
M字节，1 <= M <= 255 |
|
VARCHAR(M) |
变长非二进制字符串 |
L+1字节，在此L <= M和1 <= M <= 255 |
|
TINYTEXT |
非常小的非二进制字符串 |
L+1 字节，在此L< 2 ^ 8 |
|
TEXT |
小的非二进制字符串 |
L+2 字节，在此L< 2 ^ 16 |
|
MEDIUMTEXT |
中等大小的非二进制字符串 |
L+3 字节，在此L< 2 ^ 24 |
|
LONGTEXT |
大的非二进制字符串 |
L+4 字节，在此L< 2 ^ 32 |
|
ENUM |
枚举类型，只能有一个枚举字符串值 |
1或2个字节，取决于枚举值的数目(最大值65535) |
|
SET |
一个设置，字符串对象可以有零个或多个SET成员 |
1，2，3，4或8个字节，取决于集合成员的数量(最多64个成员) |
VARCHAR和BLOB和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度(在前面的表格中用L表示)，而不是取决于类型的最大可能尺寸。例如，一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串，实际的存储需要是字符串的长度(L)，加上1个字节以记录字符串的长度。对于字符“abcd”，L是4而存储要求是5个字节。本章节介绍了这些数据类型的作用以及如何在查询中使用这些类型。
1.CHAR和VARCHAR类型
CHAR（M）为固定长度字符串，在定义时指定字符串列长。当保存时在右侧填充空格以达到指定的长度。M表示列长度。M的范围是0到255个字符。例如，CHAR(4)定义了一个固定长度的字符串列，其包含的字符个数最大为4。当检索到CHAR值时，尾部的空格被删除掉。
VARCHAR（M）是长度可变的字符串，M表示最大列长度。M的范围是0到65,535。VARCHAR的最大实际长度由最长的行的大小和使用的字符集确定，而其实际占用的空间为字符串的实际长度加1。例如，VARCHAR(50)定义了一个最大长度为50的字符串，如果插入的字符串只有10个字符，则实际存储的字符串为10个字符和一个字符串结束字符。VARCHAR在值保存和检索时尾部的空格仍保留。
【例5.19】下面将不同字符串保存到CHAR(4)和VARCHAR(4)列，说明CHAR和VARCHAR之间的差别。
表5.6 CHAR(4)于VARCHAR(4)存储区别
|
插入值 |
CHAR(4) |
存储需求 |
VARCHAR(4) |
存储需求 |
|
‘’ |
‘ ’ |
4字节 |
‘’ |
1字节 |
|
‘ab’ |
‘ab ’ |
4字节 |
‘ab’ |
3字节 |
|
‘abc’ |
‘abc’ |
4字节 |
‘abc’ |
4字节 |
|
‘abcd‘ |
‘abcd’ |
4字节 |
‘abcd’ |
5字节 |
|
‘abcdef’ |
‘abcd’ |
4字节 |
‘abcd’ |
5字节 |
对比结果可以看到，CHAR（4）定义了固定长度为4的列，不管存入的数据长度为多少，所占用的空间均为4个字节。VARCHAR（4）定义的列所占的字节数为实际长度加1。
当查询时CHAR（4）和VARCHAR（4）的值并不一定相同，如【例5.20】。
【例5.20】创建tmp8表，定义字段ch和vch数据类型依次为CHAR（4）、VARCHAR（4）向表中插入数据“ab ”，SQL语句如下：
创建表tmp8：
CREATE TABLE tmp8(
ch CHAR(4), vch VARCHAR(4)
);
输入数据：
INSERT INTO tmp8 VALUES('ab ', 'ab ');
查询结果：
mysql> SELECT concat('(', ch, ')'), concat('(',vch,')') FROM tmp8;
+----------------------+---------------------+
| concat('(', ch, ')') | concat('(',vch,')') |
+----------------------+---------------------+
| (ab) | (ab ) |
+----------------------+---------------------+
1 row in set (0.00 sec)
从查询结果可以看到，ch在保存“ab ”时将末尾的两个空格删除了，而vch字段保留了末尾的两个空格。
2.TEXT类型
TEXT列保存非二进制字符串，如文章内容、评论等。当保存或查询TEXT列的值时不删除尾部空格。TEXT类型分为四种：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。不同的TEXT类型的存储空间和数据长度不同。
⑴ TINYTEXT最大长度为255(28–1)字符的TEXT列。
⑵ TEXT最大长度为65,535(216–1)字符的TEXT列。
⑶ MEDIUMTEXT最大长度为16,777,215(224–1)字符的TEXT列。
⑷ LONGTEXT最大长度为4,294,967,295或4GB(232–1)字符的TEXT列。
3.ENUM类型
ENUM是一个字符串对象，其值来自表创建时在列规定中枚举的一列值。语法格式如下：
字段名 ENUM('值1','值2',... '值n')
字段名指将要定义的字段，值n指枚举列表中的第n个值。ENUM类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格时，其尾部的空格将自动被删除。ENUM值在内部用整数表示，每个枚举值均有一个索引值：列表值所允许的成员值从1开始编号，MySQL存储的就是这个索引编号。枚举最多可以有65,535个元素。
例如定义ENUM类型的列('first'，'second'，'third')，该列可以取的值和每个值的索引如【表5.7】。
表5.7 ENUM类型的取值范围
|
值 |
索引 |
|
NULL |
NULL |
|
'' |
0 |
|
first |
1 |
|
second |
2 |
|
third |
3 |
ENUM值依照列索引顺序排列。并且，空字符串排在非空字符串前，NULL值排在其它所有的枚举值前。这一点也可以从【表5.7】看到。
在这里有一个方法，可以查看列成员以及列成员的索引值，如【例5.21】。
【例5.21】创建表tmp9，定义ENUM类型的列enm('first'，'second'，'third')，查看列成员的索引值，SQL语句如下：
首先，创建tmp9表：
CREATE TABLE tmp9( enm ENUM('first','second','third') );
插入各个列值：
INSERT INTO tmp9 values('first'),('second') ,('third') ,('') ,(NULL);
查看索引值：
mysql> SELECT enm, enm+0 FROM tmp9;
+------------+----------+
| enm | enm+0 |
+------------+----------+
| first | 1 |
| second | 2 |
| third | 3 |
| | 0 |
| NULL | NULL |
+------------+----------+
可以看到，这里的索引值和前面所述的相同。
【例5.22】创建表tmp10，定义INT类型的soc字段，ENUM类型的字段level，列表值为（'excellent','good', 'bad'），向表tmp10中的level字段插入数据‘good’，1，2，3，‘best’，SQL语句如下：
首先，创建数据表：
CREATE TABLE tmp10 (soc INT, level enum('excellent', 'good','bad') );
插入数据：
mysql>INSERT INTO tmp10 values(70,'good'), (90,1),(75,2),(50,3),(100,'best');
Query OK, 5 rows affected, 1 warning (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 1
这里系统提示警告信息，使用SHOW查看警告内容：
+---------+------+----------------------------------------------------+
| Level | Code | Message |
+---------+------+---------------------------------------------------+
| Warning | 1265 | Data truncated for column 'level' at row 5 |
+---------+------+----------------------------------------------------+
可以看到，由于字符串值“best”不在ENUM列表中的，对数据进行了截断操作，查询结果如下：
mysql> SELECT * FROM tmp10;
+------+-----------+
| soc | level |
+------+-----------+
| 70 | good |
| 90 | excellent |
| 75 | good |
| 50 | bad |
| 100 | |
+------+-----------+
由结果可以看到，因为ENUM列表中的值在MySQL中都是以编号序列的存储的，因此，插入列表中的值“good”或者插入其对应序号‘2’的结果是相同的；“best”不是列表中的值，因此插入的结果为空字符串；
4.SET类型
SET是一个字符串对象，可以有零或多个值，SET列最多可以有64个成员，其值为表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。语法格式如下：
SET('值1','值2',... '值n')
与ENUM类型相同，SET值在内部用整数表示，列表中每一个值都有一个索引编号，当创建表时，SET成员值的尾部空格将自动被删除。但与ENUM类型不同的是，ENUM类型的字段只能从定义的列值中选择一个值插入，而SET类型的列可从定义的列值中选择多个字符的联合。
如果插入SET字段中列值有重复，则MySQL自动删除重复的值；插入SET字段的值的顺序并不重要，MySQL会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，MySQL将忽视这些值，并给出警告。
【例5.23】创建表tmp11，定义SET类型的字段s，取值列表为（'a', 'b', 'c', 'd'），插入数据('a')，('a,b,a')，('c,a,d')，('a,x,b,y')，SQL语句如下：
首先创建表tmp11：
CREATE TABLE tmp11 ( s SET('a', 'b', 'c', 'd'));
插入数据：
INSERT INTOtmp11 values('a'),( 'a,b,a'),('c,a,d'),('a,x,b,y');
由于插入了SET列不支持的值，因此MySQL给出警告，使用SHOW 查看警告信息：
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1265 | Data truncated for column 's' at row 4 |
+---------+------+----------------------------------------------+
查看结果：
mysql> SELECT * FROM tmp11;
+-------+
| s |
+-------+
| a |
| a,b |
| a,c,d |
| a,b |
+-------+
从结果可以看到，对于SET来说如果插入的值为重复的，则只取一个，例如“a,b,a”，则结果为“a,b”；如果插入了不按顺序排列值，则自动按顺序插入，例如“c,a,d”，结果为“a,c,d”；如果插入了不正确值，该值将被忽略，例如插入值“a,x,b,y”，而结果却为“a,b”。