5.1.4 字符串类型
字符串类型用来存储字符串数据，除了可以存储字符串数据之外，还可以存储其它数据，比如图片和声音的二进制数据。字符串可以进行区分或者不区分大小写的串比较，另外，还可以进行模式匹配查找。MySQL中字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。【表5.5】列出了MySQL中的字符串数据类型。
表5.5MySQL中字符串数据类型
|
类型名称
|
说明
|
存储需求
|
|
CHAR(M)
|
固定长度非二进制字符串
|
M字节，1 <= m="">
|
|
VARCHAR(M)
|
变长非二进制字符串
|
L+1字节，在此L <= m="">和1 <= m="">
|
|
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)存储区别
<tbody>
<tr class="firstRow">
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"><strong><span style="font-family:宋体">插入值</span></strong></span></span></p>
</td>
<td style="width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"><strong>CHAR(4)</strong></span></span></p>
</td>
<td style="width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"><strong><span style="font-family:宋体">存储需求</span></strong></span></span></p>
</td>
<td style="width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"><strong>VARCHAR(4)</strong></span></span></p>
</td>
<td style="width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"><strong><span style="font-family:宋体">存储需求</span></strong></span></span></p>
</td>
</tr>
<tr>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span> <span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">4<span style="font-family:宋体">字节</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">1<span style="font-family:宋体">字节</span></span></span></p>
</td>
</tr>
<tr>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>ab<span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>ab <span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">4<span style="font-family:宋体">字节</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>ab<span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">3<span style="font-family:宋体">字节</span></span></span></p>
</td>
</tr>
<tr>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>abc<span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>abc<span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">4<span style="font-family:宋体">字节</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>abc<span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">4<span style="font-family:宋体">字节</span></span></span></p>
</td>
</tr>
<tr>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>abcd<span style="font-family:宋体">‘</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>abcd<span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">4<span style="font-family:宋体">字节</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>abcd<span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">5<span style="font-family:宋体">字节</span></span></span></p>
</td>
</tr>
<tr>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>abcdef<span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>abcd<span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.2pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">4<span style="font-family:宋体">字节</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:宋体">‘</span>abcd<span style="font-family:宋体">’</span></span></span></p>
</td>
<td style="vertical-align:top; width:85.25pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">5<span style="font-family:宋体">字节</span></span></span></p>
</td>
</tr>
</tbody></table><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">对比结果可以看到，</span><span style="font-family:">CHAR</span><span style="font-family:宋体">（</span><span style="font-family:">4</span><span style="font-family:宋体">）定义了固定长度为</span><span style="font-family:">4</span><span style="font-family:宋体">的列，不管存入的数据长度为多少，所占用的空间均为</span><span style="font-family:">4</span><span style="font-family:宋体">个字节。</span><span style="font-family:">VARCHAR</span><span style="font-family:宋体">（</span><span style="font-family:">4</span><span style="font-family:宋体">）定义的列所占的字节数为实际长度加</span><span style="font-family:">1</span><span style="font-family:宋体">。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">当查询时</span><span style="font-family:">CHAR</span><span style="font-family:宋体">（</span><span style="font-family:">4</span><span style="font-family:宋体">）和</span><span style="font-family:">VARCHAR</span><span style="font-family:宋体">（</span><span style="font-family:">4</span><span style="font-family:宋体">）的值并不一定相同，如【例</span><span style="font-family:">5.20</span><span style="font-family:宋体">】。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">【例</span><span style="font-family:">5.20</span><span style="font-family:宋体">】创建</span><span style="font-family:">tmp8</span><span style="font-family:宋体">表，定义字段</span><span style="font-family:">ch</span><span style="font-family:宋体">和</span><span style="font-family:">vch</span><span style="font-family:宋体">数据类型依次为</span><span style="font-family:">CHAR</span><span style="font-family:宋体">（</span><span style="font-family:">4</span><span style="font-family:宋体">）、</span><span style="font-family:">VARCHAR</span><span style="font-family:宋体">（</span><span style="font-family:">4</span><span style="font-family:宋体">）向表中插入数据“</span><span style="font-family:">ab </span><span style="font-family:宋体">”，</span><span style="font-family:">SQL</span><span style="font-family:宋体">语句如下：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">创建表</span><span style="font-family:">tmp8</span><span style="font-family:宋体">：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">CREATE TABLE tmp8(</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">ch CHAR(4), vch VARCHAR(4)</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">);</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">输入数据：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">INSERT INTO tmp8 VALUES('ab ', 'ab ');</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">查询结果：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">mysql> SELECT concat('(', ch, ')'), concat('(',vch,')') FROM tmp8;</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+----------------------+---------------------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| concat('(', ch, ')') | concat('(',vch,')') |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+----------------------+---------------------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| (ab) | (ab ) |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+----------------------+---------------------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">1 row in set (0.00 sec)</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">从查询结果可以看到，</span><span style="font-family:">ch</span><span style="font-family:宋体">在保存“</span><span style="font-family:">ab </span><span style="font-family:宋体">”时将末尾的两个空格删除了，而</span><span style="font-family:">vch</span><span style="font-family:宋体">字段保留了末尾的两个空格。<br/><img src="http://www.jbsage.com//attachment/20250807/a72e4fe9336758f577d3cd3fcb00a3a8.png" alt="a72e4fe9336758f577d3cd3fcb00a3a8.png" width="100%" height="auto"/></span></span></span><br/><br/> </p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><strong>2.TEXT</strong><strong><span style="font-family:宋体">类型</span></strong></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:">TEXT</span><span style="font-family:宋体">列保存非二进制字符串，如文章内容、评论等。当保存或查询</span><span style="font-family:">TEXT</span><span style="font-family:宋体">列的值时不删除尾部空格。</span><span style="font-family:">TEXT</span><span style="font-family:宋体">类型分为四种：</span><span style="font-family:">TINYTEXT</span><span style="font-family:宋体">、</span><span style="font-family:">TEXT</span><span style="font-family:宋体">、</span><span style="font-family:">MEDIUMTEXT</span><span style="font-family:宋体">和</span><span style="font-family:">LONGTEXT</span><span style="font-family:宋体">。不同的</span><span style="font-family:">TEXT</span><span style="font-family:宋体">类型的存储空间和数据长度不同。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">⑴</span><span style="font-family:"> TINYTEXT</span><span style="font-family:宋体">最大长度为</span><span style="font-family:">255(28–1)</span><span style="font-family:宋体">字符的</span><span style="font-family:">TEXT</span><span style="font-family:宋体">列。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">⑵</span><span style="font-family:"> TEXT</span><span style="font-family:宋体">最大长度为</span><span style="font-family:">65,535(216–1)</span><span style="font-family:宋体">字符的</span><span style="font-family:">TEXT</span><span style="font-family:宋体">列。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">⑶</span><span style="font-family:"> MEDIUMTEXT</span><span style="font-family:宋体">最大长度为</span><span style="font-family:">16,777,215(224–1)</span><span style="font-family:宋体">字符的</span><span style="font-family:">TEXT</span><span style="font-family:宋体">列。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">⑷</span><span style="font-family:"> LONGTEXT</span><span style="font-family:宋体">最大长度为</span><span style="font-family:">4,294,967,295</span><span style="font-family:宋体">或</span><span style="font-family:">4GB(232–1)</span><span style="font-family:宋体">字符的</span><span style="font-family:">TEXT</span><span style="font-family:宋体">列。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><strong>3.ENUM</strong><strong><span style="font-family:宋体">类型</span></strong></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:">ENUM</span><span style="font-family:宋体">是一个字符串对象，其值来自表创建时在列规定中枚举的一列值。语法格式如下：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:"><span style="font-family:宋体">字段名</span> ENUM('<span style="font-family:宋体">值</span>1','<span style="font-family:宋体">值</span>2',... '<span style="font-family:宋体">值</span>n')</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">字段名指将要定义的字段，值</span><span style="font-family:">n</span><span style="font-family:宋体">指枚举列表中的第</span><span style="font-family:">n</span><span style="font-family:宋体">个值。</span><span style="font-family:">ENUM</span><span style="font-family:宋体">类型的字段在取值时，只能在指定的枚举列表中取，而且一次只能取一个。如果创建的成员中有空格时，其尾部的空格将自动被删除。</span><span style="font-family:">ENUM</span><span style="font-family:宋体">值在内部用整数表示，每个枚举值均有一个索引值：列表值所允许的成员值从</span><span style="font-family:">1</span><span style="font-family:宋体">开始编号，</span><span style="font-family:">MySQL</span><span style="font-family:宋体">存储的就是这个索引编号。枚举最多可以有</span><span style="font-family:">65,535</span><span style="font-family:宋体">个元素。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">例如定义</span><span style="font-family:">ENUM</span><span style="font-family:宋体">类型的列</span><span style="font-family:">('first'</span><span style="font-family:宋体">，</span><span style="font-family:">'second'</span><span style="font-family:宋体">，</span><span style="font-family:">'third')</span><span style="font-family:宋体">，该列可以取的值和每个值的索引如【表</span><span style="font-family:">5.7</span><span style="font-family:宋体">】。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"><span style="font-family:黑体">表</span>5.7 ENUM<span style="font-family:黑体">类型的取值范围</span></span></span></p><table align="center" border="1" cellspacing="0" class="Table" style="border-collapse:collapse; border:solid windowtext 1.0pt">
<tbody>
<tr class="firstRow">
<td style="height:15.4pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"><strong><span style="font-family:宋体">值</span></strong></span></span></p>
</td>
<td style="height:15.4pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:center"><span style="font-size:9pt"><span style="font-family:"><strong><span style="font-family:宋体">索引</span></strong></span></span></p>
</td>
</tr>
<tr>
<td style="height:16.15pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">NULL</span></span></p>
</td>
<td style="height:16.15pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">NULL</span></span></p>
</td>
</tr>
<tr>
<td style="height:15.4pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">''</span></span></p>
</td>
<td style="height:15.4pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">0</span></span></p>
</td>
</tr>
<tr>
<td style="height:16.15pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">first</span></span></p>
</td>
<td style="height:16.15pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">1</span></span></p>
</td>
</tr>
<tr>
<td style="height:15.4pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">second</span></span></p>
</td>
<td style="height:15.4pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">2</span></span></p>
</td>
</tr>
<tr>
<td style="height:16.15pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">third</span></span></p>
</td>
<td style="height:16.15pt; vertical-align:top; width:119.9pt">
<p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="font-family:">3</span></span></p>
</td>
</tr>
</tbody></table><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:">ENUM</span><span style="font-family:宋体">值依照列索引顺序排列。并且，空字符串排在非空字符串前，</span><span style="font-family:">NULL</span><span style="font-family:宋体">值排在其它所有的枚举值前。这一点也可以从【表</span><span style="font-family:">5.7</span><span style="font-family:宋体">】看到。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">在这里有一个方法，可以查看列成员以及列成员的索引值，如【例</span><span style="font-family:">5.21</span><span style="font-family:宋体">】。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">【例</span><span style="font-family:">5.21</span><span style="font-family:宋体">】创建表</span><span style="font-family:">tmp9</span><span style="font-family:宋体">，定义</span><span style="font-family:">ENUM</span><span style="font-family:宋体">类型的列</span><span style="font-family:">enm('first'</span><span style="font-family:宋体">，</span><span style="font-family:">'second'</span><span style="font-family:宋体">，</span><span style="font-family:">'third')</span><span style="font-family:宋体">，查看列成员的索引值，</span><span style="font-family:">SQL</span><span style="font-family:宋体">语句如下：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">首先，创建</span><span style="font-family:">tmp9</span><span style="font-family:宋体">表：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">CREATE TABLE tmp9( enm ENUM('first','second','third') );</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">插入各个列值：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">INSERT INTO tmp9 values('first'),('second') ,('third') ,('') ,(NULL);</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">查看索引值：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">mysql> SELECT enm, enm+0 FROM tmp9;</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+------------+----------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| enm | enm+0 |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+------------+----------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| first | 1 |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| second | 2 |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| third | 3 |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| | 0 |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| NULL | NULL |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+------------+----------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:宋体">可以看到，这里的索引值和前面所述的相同。<br/><img src="http://www.jbsage.com//attachment/20250807/c7f5272abe64a7cd675ba13cc34f3b01.png" alt="c7f5272abe64a7cd675ba13cc34f3b01.png" width="100%" height="auto"/></span></span><br/> </p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">【例</span><span style="font-family:">5.22</span><span style="font-family:宋体">】创建表</span><span style="font-family:">tmp10</span><span style="font-family:宋体">，定义</span><span style="font-family:">INT</span><span style="font-family:宋体">类型的</span><span style="font-family:">soc</span><span style="font-family:宋体">字段，</span><span style="font-family:">ENUM</span><span style="font-family:宋体">类型的字段</span><span style="font-family:">level</span><span style="font-family:宋体">，列表值为（</span><span style="font-family:">'excellent','good', 'bad'</span><span style="font-family:宋体">），向表</span><span style="font-family:">tmp10</span><span style="font-family:宋体">中的</span><span style="font-family:">level</span><span style="font-family:宋体">字段插入数据‘</span><span style="font-family:">good</span><span style="font-family:宋体">’，</span><span style="font-family:">1</span><span style="font-family:宋体">，</span><span style="font-family:">2</span><span style="font-family:宋体">，</span><span style="font-family:">3</span><span style="font-family:宋体">，‘</span><span style="font-family:">best</span><span style="font-family:宋体">’，</span><span style="font-family:">SQL</span><span style="font-family:宋体">语句如下：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">首先，创建数据表：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">CREATE TABLE tmp10 (soc INT, level enum('excellent', 'good','bad') );</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">插入数据：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">mysql>INSERT INTO tmp10 values(70,'good'), (90,1),(75,2),(50,3),(100,'best');</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">Query OK, 5 rows affected, 1 warning (0.00 sec)</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">Records: 5 Duplicates: 0 Warnings: 1</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">这里系统提示警告信息，使用</span><span style="font-family:">SHOW</span><span style="font-family:宋体">查看警告内容：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+---------+------+----------------------------------------------------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| Level | Code | Message |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+---------+------+---------------------------------------------------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| Warning | 1265 | Data truncated for column 'level' at row 5 |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+---------+------+----------------------------------------------------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">可以看到，由于字符串值“</span><span style="font-family:">best</span><span style="font-family:宋体">”不在</span><span style="font-family:">ENUM</span><span style="font-family:宋体">列表中的，对数据进行了截断操作，查询结果如下：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">mysql> SELECT * FROM tmp10;</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+------+-----------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| soc | level |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+------+-----------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| 70 | good |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| 90 | excellent |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| 75 | good |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| 50 | bad |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| 100 | |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+------+-----------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">由结果可以看到，因为</span><span style="font-family:">ENUM</span><span style="font-family:宋体">列表中的值在</span><span style="font-family:">MySQL</span><span style="font-family:宋体">中都是以编号序列的存储的，因此，插入列表中的值“</span><span style="font-family:">good</span><span style="font-family:宋体">”或者插入其对应序号‘</span><span style="font-family:">2</span><span style="font-family:宋体">’的结果是相同的；“</span><span style="font-family:">best</span><span style="font-family:宋体">”不是列表中的值，因此插入的结果为空字符串；</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><strong>4.SET</strong><strong><span style="font-family:宋体">类型</span></strong></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:">SET</span><span style="font-family:宋体">是一个字符串对象，可以有零或多个值，</span><span style="font-family:">SET</span><span style="font-family:宋体">列最多可以有</span><span style="font-family:">64</span><span style="font-family:宋体">个成员，其值为表创建时规定的允许的一列值。指定包括多个</span><span style="font-family:">SET</span><span style="font-family:宋体">成员的</span><span style="font-family:">SET</span><span style="font-family:宋体">列值时各成员之间用逗号</span><span style="font-family:">(‘,’)</span><span style="font-family:宋体">间隔开。语法格式如下：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">SET('<span style="font-family:宋体">值</span>1','<span style="font-family:宋体">值</span>2',... '<span style="font-family:宋体">值</span>n')</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">与</span><span style="font-family:">ENUM</span><span style="font-family:宋体">类型相同，</span><span style="font-family:">SET</span><span style="font-family:宋体">值在内部用整数表示，列表中每一个值都有一个索引编号，当创建表时，</span><span style="font-family:">SET</span><span style="font-family:宋体">成员值的尾部空格将自动被删除。但与</span><span style="font-family:">ENUM</span><span style="font-family:宋体">类型不同的是，</span><span style="font-family:">ENUM</span><span style="font-family:宋体">类型的字段只能从定义的列值中选择一个值插入，而</span><span style="font-family:">SET</span><span style="font-family:宋体">类型的列可从定义的列值中选择多个字符的联合。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">如果插入</span><span style="font-family:">SET</span><span style="font-family:宋体">字段中列值有重复，则</span><span style="font-family:">MySQL</span><span style="font-family:宋体">自动删除重复的值；插入</span><span style="font-family:">SET</span><span style="font-family:宋体">字段的值的顺序并不重要，</span><span style="font-family:">MySQL</span><span style="font-family:宋体">会在存入数据库时，按照定义的顺序显示；如果插入了不正确的值，默认情况下，</span><span style="font-family:">MySQL</span><span style="font-family:宋体">将忽视这些值，并给出警告。</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">【例</span><span style="font-family:">5.23</span><span style="font-family:宋体">】创建表</span><span style="font-family:">tmp11</span><span style="font-family:宋体">，定义</span><span style="font-family:">SET</span><span style="font-family:宋体">类型的字段</span><span style="font-family:">s</span><span style="font-family:宋体">，取值列表为（</span><span style="font-family:">'a', 'b', 'c', 'd'</span><span style="font-family:宋体">），插入数据</span><span style="font-family:">('a')</span><span style="font-family:宋体">，</span><span style="font-family:">('a,b,a')</span><span style="font-family:宋体">，</span><span style="font-family:">('c,a,d')</span><span style="font-family:宋体">，</span><span style="font-family:">('a,x,b,y')</span><span style="font-family:宋体">，</span><span style="font-family:">SQL</span><span style="font-family:宋体">语句如下：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">首先创建表</span><span style="font-family:">tmp11</span><span style="font-family:宋体">：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">CREATE TABLE tmp11 ( s SET('a', 'b', 'c', 'd'));</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">插入数据：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">INSERT INTOtmp11 values('a'),( 'a,b,a'),('c,a,d'),('a,x,b,y');</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">由于插入了</span><span style="font-family:">SET</span><span style="font-family:宋体">列不支持的值，因此</span><span style="font-family:">MySQL</span><span style="font-family:宋体">给出警告，使用</span><span style="font-family:">SHOW </span><span style="font-family:宋体">查看警告信息：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+---------+------+----------------------------------------------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| Level | Code | Message |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+---------+------+----------------------------------------------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| Warning | 1265 | Data truncated for column 's' at row 4 |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+---------+------+----------------------------------------------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">查看结果：</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">mysql> SELECT * FROM tmp11;</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+-------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| s |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+-------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| a |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| a,b |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| a,c,d |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">| a,b |</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:9pt"><span style="background-color:#d9d9d9"><span style="font-family:">+-------+</span></span></span></p><p style="margin-left:0cm; margin-right:0cm; text-align:justify"><span style="font-size:10.5pt"><span style="font-family:Calibri"><span style="font-family:宋体">从结果可以看到，对于</span><span style="font-family:">SET</span><span style="font-family:宋体">来说如果插入的值为重复的，则只取一个，例如“</span><span style="font-family:">a,b,a</span><span style="font-family:宋体">”，则结果为“</span><span style="font-family:">a,b</span><span style="font-family:宋体">”；如果插入了不按顺序排列值，则自动按顺序插入，例如“</span><span style="font-family:">c,a,d</span><span style="font-family:宋体">”，结果为“</span><span style="font-family:">a,c,d</span><span style="font-family:宋体">”；如果插入了不正确值，该值将被忽略，例如插入值“</span><span style="font-family:">a,x,b,y</span><span style="font-family:宋体">”，而结果却为“</span><span style="font-family:">a,b</span><span style="font-family:宋体">”。</span></span></span></p>