MySQL基础教程3 —— 数据类型之字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

1. CHAR和VARCHAR类型

CHAR和VARCHAR类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

CHAR和VARCHAR类型声明的长度表示你想要保存的最大字符数。例如，CHAR(30)可以占用30个字符。

CHAR列的长度固定为创建表时声明的长度。长度可以为从0到255的任何值。当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。

VARCHAR列中的值为可变长字符串。长度可以指定为0到65,535之间的值。(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。

同CHAR对比，VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。

VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。

如果分配给CHAR或VARCHAR列的值超过列的最大长度，则对值进行裁剪以使其适合。如果被裁掉的字符不是空格，则会产生一条警告。如果裁剪非空格字符，则会造成错误(而不是警告)并通过使用严格SQL模式禁用值的插入。

下面的表显示了将各种字符串值保存到CHAR(4)和VARCHAR(4)列后的结果，说明了CHAR和VARCHAR之间的差别：

值	CHAR(4)	存储需求	VARCHAR(4)	存储需求
''	'    '	4个字节	''	1个字节
'ab'	'ab  '	4个字节	'ab '	3个字节
'abcd'	'abcd'	4个字节	'abcd'	5个字节
'abcdefgh'	'abcd'	4个字节	'abcd'	5个字节

请注意上表中最后一行的值只适用不使用严格模式时；如果MySQL运行在严格模式，超过列长度不的值不保存，并且会出现错误。

从CHAR(4)和VARCHAR(4)列检索的值并不总是相同，因为检索时从CHAR列删除了尾部的空格。通过下面的例子说明该差别：

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO vc VALUES ('ab  ', 'ab  ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT CONCAT(v, '+'), CONCAT(c, '+') FROM vc;
+----------------+----------------+
| CONCAT(v, '+') | CONCAT(c, '+') |
+----------------+----------------+
| ab  +          | ab+            |
+----------------+----------------+
1 row in set (0.00 sec)

根据分配给列的字符集校对规则对CHAR和VARCHAR列中的值进行排序和比较。

请注意所有MySQL校对规则属于PADSPACE类。这说明在MySQL中的所有CHAR和VARCHAR值比较时不需要考虑任何尾部空格。例如：

mysql> CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO names VALUES ('Monty ', 'Monty ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Monty  ', yourname = 'Monty  ' FROM names;
+--------------------+----------------------+
| myname = 'Monty  ' | yourname = 'Monty  ' |
+--------------------+----------------------+
|                  1 |                    1 |
+--------------------+----------------------+
1 row in set (0.00 sec)

请注意所有MySQL版本均如此，并且它不受SQL服务器模式的影响。

对于尾部填充字符被裁剪掉或比较时将它们忽视掉的情形，如果列的索引需要唯一的值，在列内插入一个只是填充字符数不同的值将会造成复制键值错误。

CHAR BYTE是CHAR BINARY的别名。这是为了保证兼容性。

ASCII属性为CHAR列分配latin1字符集。UNICODE属性分配ucs2字符集。

2. BINARY和VARBINARY类型

BINARY和VARBINARY类类似于CHAR和VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BINARY和VARBINARY允许的最大长度一样，如同CHAR和VARCHAR，不同的是BINARY和VARBINARY的长度是字节长度而不是字符长度。

BINARY和VARBINARY数据类型不同于CHAR BINARY和VARCHAR BINARY数据类型。对于后一种类型，BINARY属性不会将列视为二进制字符串列。相反，它致使使用列字符集的二元 校对规则，并且列自身包含非二进制字符字符串而不是二进制字节字符串。例如CHAR(5) BINARY被视为CHAR(5) CHARACTER SET latin1 COLLATE latin1_bin，假定默认字符集是latin1。这不同于BINARY(5)，它保存5字节二进制字符串，没有字符集或 校对规则。

当保存BINARY值时，在它们右边填充值以达到指定长度。填充值是0x00(零字节)。插入值时在右侧添加0x00 on，并且选择时不删除尾部的字节。比较时所有字节很重要，包括ORDER BY和DISTINCT操作。比较时0x00字节和空格是不同的，0x00<空格。

例如：对于一个BINARY(3)列，当插入时 'a' 变为 'a \0'。'a\0'插入时变为'a\0\0'。当选择时两个插入的值均不更改。

对于VARBINARY，插入时不填充字符，选择时不裁剪字节。比较时所有字节很重要，包括ORDER BY和DISTINCT操作。比较时0x00字节和空格是不同的，0x00<空格。

对于尾部填充字符被裁剪掉或比较时将它们忽视掉的情形，如果列的索引需要唯一的值，在列内插入一个只是填充字符数不同的值将会造成复制键值错误。

如果你计划使用这些数据类型来保存二进制数据并且需要检索的值与保存的值完全相同，应考虑前面所述的填充和裁剪特征。下面的例子说明了用0x00填充的BINARY值如何影响列值比较：

mysql> CREATE TABLE t (c BINARY(3));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t SET c = 'a';
Query OK, 1 row affected (0.01 sec)

mysql> SELECT HEX(c), c = 'a', c = 'a\0\0' from t;
+--------+---------+-------------+
| HEX(c) | c = 'a' | c = 'a\0\0' |
+--------+---------+-------------+
| 610000 |       0 |           1 |
+--------+---------+-------------+
1 row in set (0.09 sec)

如果检索的值必须与指定进行存储而没有填充的值相同，最好使用BLOB数据类型。

创建表时，MySQL可以默默更改BINARY或VARBINARY列的类型。

3. BLOB和TEXT类型

BLOB是一个二进制大对象，可以容纳可变数量的数据。有4种BLOB类型：TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB。它们只是可容纳值的最大长度不同。

有4种TEXT类型：TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。这些对应4种BLOB类型，有相同的最大长度和存储需求。

BLOB 列被视为二进制字符串(字节字符串)。TEXT列被视为非二进制字符串(字符字符串)。BLOB列没有字符集，并且排序和比较基于列值字节的数值值。TEXT列有一个字符集，并且根据字符集的 校对规则对值进行排序和比较。

在TEXT或BLOB列的存储或检索过程中，不存在大小写转换。

当未运行在严格模式时，如果你为BLOB或TEXT列分配一个超过该列类型的最大长度的值值，值被截取以保证适合。如果截掉的字符不是空格，将会产生一条警告。使用严格SQL模式，会产生错误，并且值将被拒绝而不是截取并给出警告。

在大多数方面，可以将BLOB列视为能够足够大的VARBINARY列。同样，可以将TEXT列视为VARCHAR列。BLOB和TEXT在以下几个方面不同于VARBINARY和VARCHAR：

· 当保存或检索BLOB和TEXT列的值时不删除尾部空格。(这与VARBINARY和VARCHAR列相同）。

请注意比较时将用空格对TEXT进行扩充以适合比较的对象，正如CHAR和VARCHAR。

· 对于BLOB和TEXT列的索引，必须指定索引前缀的长度。对于CHAR和VARCHAR，前缀长度是可选的。

· BLOB和TEXT列不能有 默认值。

LONG和LONG VARCHAR对应MEDIUMTEXT数据类型。这是为了保证兼容性。如果TEXT列类型使用BINARY属性，将为列分配列字符集的二元 校对规则。

MySQL连接程序/ODBC将BLOB值定义为LONGVARBINARY，将TEXT值定义为LONGVARCHAR。

由于BLOB和TEXT值可能会非常长，使用它们时可能遇到一些约束：

· 当排序时只使用该列的前max_sort_length个字节。max_sort_length的 默认值是1024；该值可以在启动mysqld服务器时使用--max_sort_length选项进行更改。

运行时增加max_sort_length的值可以在排序或组合时使更多的字节有意义。任何客户端可以更改其会话max_sort_length变量的值：

mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM tbl_name
    -> ORDER BY comment;

当你想要使超过max_sort_length的字节有意义，对含长值的BLOB或TEXT列使用GROUP BY或ORDER BY的另一种方式是将列值转换为固定长度的对象。标准方法是使用SUBSTRING函数。例如，下面的语句对comment列的2000个字节进行排序：

mysql> SELECT id, SUBSTRING(comment,1,2000) FROM tbl_name
    -> ORDER BY SUBSTRING(comment,1,2000);

·         BLOB或TEXT对象的最大大小由其类型确定，但在客户端和服务器之间实际可以传递的最大值由可用内存数量和通信缓存区大小确定。你可以通过更改max_allowed_packet变量的值更改消息缓存区的大小，但必须同时修改服务器和客户端程序。例如，可以使用 mysql和mysqldump来更改客户端的max_allowed_packet值。

每个BLOB或TEXT值分别由内部分配的对象表示。这与其它列类型形成对比，后者是当打开表时为每1列分配存储引擎。

4. ENUM类型

ENUM是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。

在某些情况下，ENUM值也可以为空字符串('')或NULL：

· 如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。后面有详细讨论。

· 如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且 默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。

每个枚举值有一个索引：

· 来自列规定的允许的值列中的值从1开始编号。

· 空字符串错误值的索引值是0。这说明你可以使用下面的SELECT语句来找出分配了非法ENUM值的行：

· mysql> SELECT * FROM tbl_name WHERE enum_col=0;

· NULL值的索引是NULL。

例如，定义为ENUM的列('one'，'two'，'three')可以有下面所示任何值。还显示了每个值的索引：

值	索引
NULL	NULL
''	0
'one'	1
'two'	2
'three'	3

枚举最多可以有65,535个元素。

当创建表时，ENUM成员值的尾部空格将自动被删除。

当检索时，保存在ENUM列的值使用列定义中所使用的大小写来显示。请注意可以为ENUM列分配字符集和 校对规则。对于二进制或大小写敏感的校对规则，当为列分配值时应考虑大小写。

如果在数值上下文中检索一个ENUM值，将返回列值的索引。例如，你可以这样从ENUM列搜索数值值：

mysql> SELECT enum_col+0 FROM tbl_name;

如果将一个数字保存到ENUM列，数字被视为索引，并且保存的值是该索引对应的枚举成员。(但是，这不适合LOAD DATA，它将所有输入视为字符串）。不建议使用类似数字的枚举值来定义一个ENUM列，因为这很容易引起混淆。例如，下面的列含有字符串值'0'、'1'和'2'的枚举成员，但数值索引值为1、2和3：

numbers ENUM('0','1','2')

根据枚举成员在列定义中列出的顺序对ENUM值进行排序。(换句话说，ENUM值根据索引编号进行排序）。例如，对于ENUM('a'，'b')，'a'排在'b'前面，但对于ENUM('b'，'a')，'b'排在'a'前面。空字符串排在非空字符串前面，并且NULL值排在所有其它枚举值前面。要想防止意想不到的结果，按字母顺序规定ENUM列。还可以使用GROUP BY  CAST(col AS CHAR)或GROUP BY  CONCAT(col)来确保按照词汇对列进行排序而不是用索引数字。

如果你想要确定一个ENUM列的所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE enum_col，并解析输出中第2列的ENUM定义。

5. SET类型

SET是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。指定包括多个SET成员的SET列值时各成员之间用逗号(‘,’)间隔开。这样SET成员值本身不能包含逗号。

例如，指定为SET('one', 'two') NOT NULL的列可以有下面的任何值：

''
'one'
'two'
'one,two'

SET最多可以有64个不同的成员。

当创建表时，SET成员值的尾部空格将自动被删除。

当检索时，保存在SET列的值使用列定义中所使用的大小写来显示。请注意可以为SET列分配字符集和 校对规则。对于二进制或大小写敏感的校对规则，当为列分配值时应考虑大小写。

MySQL用数字保存SET值，所保存值的低阶位对应第1个SET成员。如果在数值上下文中检索一个SET值，检索的值的位设置对应组成列值的SET成员。例如，你可以这样从一个SET列检索数值值：

mysql> SELECT set_col+0 FROM tbl_name;

如果将一个数字保存到SET列中，数字中二进制表示中的位确定了列值中的SET成员。对于指定为SET('a','b','c','d')的列，成员有下面的十进制和二进制值：

SET成员	十进制值	二进制值
'a'	1	0001
'b'	2	0010
'c'	4	0100
'd'	8	1000

 

如果你为该列分配一个值9，其二进制形式为1001，因此第1个和第4个SET值成员'a'和'd'被选择，结果值为 'a,d'。

对于包含多个SET元素的值，当插入值时元素所列的顺序并不重要。在值中一个给定的元素列了多少次也不重要。当以后检索该值时，值中的每个元素出现一次，根据表创建时指定的顺序列出元素。例如，假定某个列指定为SET('a','b','c','d')：

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));

插入值'a,d'、'd,a'、'a,d,d'、'a,d,a'和'd,a,d':

mysql> INSERT INTO myset (col) VALUES 
-> ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

当检索时所有这些值显示为 'a,d'：

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
5 rows in set (0.04 sec)

如果将SET列设置为一个不支持的值，则该值被忽略并发出警告：

mysql> INSERT INTO myset (col) VALUES ('a,d,d,s');
Query OK, 1 row affected, 1 warning (0.03 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1265 | Data truncated for column 'col' at row 1 |
+---------+------+------------------------------------------+
1 row in set (0.04 sec)

mysql> SELECT col FROM myset;
+------+
| col  |
+------+
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
| a,d  |
+------+
6 rows in set (0.01 sec)

SET值按数字顺序排序。NULL值排在非NULL SET值的前面。

通常情况，可以使用FIND_IN_SET()函数或LIKE操作符搜索SET值：

mysql> SELECT * FROM tbl_name WHERE FIND_IN_SET('value',set_col)>0;
mysql> SELECT * FROM tbl_name WHERE set_col LIKE '%value%';

第1个语句找出SET_col包含value set成员的行。第2个类似，但有所不同：它在其它地方找出set_col包含value的行，甚至是在另一个SET成员的子字符串中。

下面的语句也是合法的：

mysql> SELECT * FROM tbl_name WHERE set_col & 1;
mysql> SELECT * FROM tbl_name WHERE set_col = 'val1,val2';

第1个语句寻找包含第1个set成员的值。第2个语句寻找一个确切匹配的值。应注意第2类的比较。将set值与'val1,val2'比较返回的结果与同'val2,val1'比较返回的结果不同。指定值时的顺序应与在列定义中所列的顺序相同。

如果想要为SET列确定所有可能的值，使用SHOW COLUMNS FROM tbl_name LIKE set_col并解析输出中第2列的SET定义。