MySQL 中的字符串类型

字符类型包括:

CHAR 与 VARCHAR

CHAR(m) m 取值范围 0~255。列宽固定,存储时字符串右边会补空格,取出时自动去掉空格,除非开启了 PAD_CHAR_TO_FULL_LENGTH

对于值未超出的情况,VARCHAR 在存储及查询时皆会完整保留末尾的空格字符。

VARCHAR(m) m 取值范围 0~65535,不定长度,根据存储的值而定,但上限为 m。存储时,会在值的前面预留一到两位用来存储字符串长度。0~255时预留一位,超过的情况使用两位来标识所存储的字符串的长度

在非严格模式下,超过的非空格值会自动截断来存储,同时生成警告信息。建议开启严格模式以避免这种不完整数据的产生。

对于超出的空格值,VARCHAR 会在插入钱截断并产生警告信息,而对于 CHAR 则没有警告信息,直接静默截断存储。

以下表格以 m为4的情况展示了 非严格模式下 CHAR,VARCHAR 在存储不同长度字符串时的表现。

Value CHAR(4) Storage Required VARCHAR(4) Storage Required
'' '    ' 4 bytes '' 1 byte
'ab' 'ab  ' 4 bytes 'ab' 3 bytes
'abcd' 'abcd' 4 bytes 'abcd' 5 bytes
'abcdefgh' 'abcd' 4 bytes 'abcd' 5 byte

以下代码展示了两者在查询时对于末尾空格的处理情况。

mysql> CREATE TABLE vc (v VARCHAR(4), c CHAR(4));
Query OK, 0 rows affected (0.01 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.06 sec)

在进行排序和字符串比较时,具体表现和使用的字符集(character set collation )有关。

Pad 属性

Pad 即字符串长度不够时的补齐操作,一般是添加空格。大部分字符集都有一个 PAD SPACE 的补齐属性(pad attribute)。基于 UCA 9.0.0 的 Unicode 字符集其补齐属性为 NO PAD

可通过查询 INFORMATION_SCHEMA COLLATIONS 表中的 PAD_ATTRIBUTE 列获取到和字符集的补齐属性。

补齐属性直接影响非二进制字符串(CHAR, VARCHAR, and TEXT)进行比较时的末尾的空白会如何处理。

对于 NO PAD 字符集,末尾的空白会参与字符串比较,PAD SPACE 末尾的空白则不影响比较结果,但在使用 LIKE 关键字时空白是会参与进来的。

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

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

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

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

对于要求列值要求在记录中唯一(unique values)的表格,在插入时需要格外注意,如果插入的值只包含空格的区别,在空格不影响比较结果的字符集中会报 duplicate-key error。比如已经有记录的值为 a 尝试插入 a 时会报错。

BINARY 与 VARBINARY

基本与 CHAR 和 VARCHAR 类似,不同点在于存储的是二进制字符值。存储长度的取值范围也一样,只是单位是字节而不是字符。

写入时对于超出限制的值,会自动截断,除非开启 MySQL 的严格模式,此时会报错而非截断后成功写入。

对于 BINARY 类型,存入的值长度不够时会自动补上二进制中的零 0x00。查询时也会返回补零后的值。在进行比较操作时,所有字节都参与计算,注意二进制的零值 0x00 与空格是不同的值,0x00 < 空格。

以下代码展示了自动补零在存取时的表现,

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)

所以如果你期望查询时得到与存储时一致的值,最好使用 VARBINARYBLOB 类型。

BLOB 与 TEXT 类型

BLOB (binary large object) 即大型二进制形式的对象,用于数据量大的情况。BLOB 大类中具体包含:TINYBLOB, BLOB, MEDIUMBLOB 以及 LONGBLOB 四种数据类型,他们只存在存储上限的区别。

文本类型 TEXT 下面同样包含不同上限的具体类型:TINYTEXT, TEXT, MEDIUMTEXT 及 LONGTEXT 。这四种类型功能及上限与 BLOB 中的四种类型一一对应,只是该类型用于字符而非二进制值。

BLOB 类型内部处理时以二进制字符串的形式处理,在进行比较操作或排序时,按其表示的二进制数字来进行。而 TEXT 类型表示非二进制字符,在进行比较或排序时与字符集有关。

非严格模式下写入一个超出长度的值时,会自动截断后存入,同时生成警告信息,这一行为可通过开启严格模式来避免。

其中对于 TEXT 类型,截取末尾的空格字符时始终生成警告信息,与 MySQL 当前设置的模式无关。

一般来说你可把 BLOB 看作是 VARBLOB,存储任意大的数据。TEXT 同理,可看成 VARCHAR。但还是有一些区别的:

  • 建立索引时 BLOB 和 TEXT 需要指定长度(index prefix length),而 CHAR 和 VARCHAR 不需要。
  • BLOB 和 TEXT 类型没有默认值,不能设置 DEFAULT 属性。

因为 BLOB 和 TEXT 所存的值有可能很大,实际使用时会遇到些限制,比如

  • 排序时只取 max_sort_length 的长度。该值默认为 1024,但可随时配置:
mysql> SET max_sort_length = 2000;
mysql> SELECT id, comment FROM t
    -> ORDER BY comment;
  • 查询 BLOB 和 TEXT 类型时其结果会处理到一个临时表中,而不是像其他类型一样放到内存里,因为内存中是不支持这两种类型的(关于原因详见 Section 8.4.4, “Internal Temporary Table Use in MySQL)。这里临时表的操作涉及到额外的文件 I/O,在查询时会影响性能,所以应该尽量避免这样的操作。
  • 尽管 BLOB 和 TEXT 允许存储的值很大,实际使用时,其上限受内存及传输 buffer 大小的影响。

ENUM 类型

枚举值类型通过在创建表时指定该类型可接受的合法值列表,相比一般的字符类型有以下优势:

  • 节省存储空间,枚举值实际存储时会转成数字。
  • 语义化。相比 直接使用数字类型,枚举类型在查询时会返回其对应的字符串。

枚举的创建与使用

枚举中的项通过单引号指定,以下是一个创建和使用枚举类型的示例:

CREATE TABLE shirts (
    name VARCHAR(40),
    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
);
INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),
  ('polo shirt','small');
SELECT name, size FROM shirts WHERE size = 'medium';
+---------+--------+
| name    | size   |
+---------+--------+
| t-shirt | medium |
+---------+--------+
UPDATE shirts SET size = 'small' WHERE size = 'large';
COMMIT;

不支持在创建时使用表达式,比如下面这样是不行的:

# 🚨
CREATE TABLE sizes (
    size ENUM('small', CONCAT('med','ium'), 'large')
);

# 🚨
SET @mysize = 'medium';

CREATE TABLE sizes (
    size ENUM('small', @mysize, 'large')
);

枚举项的数字值

枚举列表中每项都对应一个数字值,是一个从 1 开始的索引值。枚举类型最多可包含 65,535 个候选项。

对于写入的非法值会存储成 0,所以可通过查询为 0 的列找出所有这些非法的记录。

mysql> SELECT * FROM tbl_name WHERE enum_col=0;

NULL 的索引值是 NULL。

ENUM('Mercury', 'Venus', 'Earth') 为例,以下表格展示了不同值其对应的索引值:

Value Index
NULL NULL
'' 0
'Mercury' 1
'Venus' 2
'Earth' 3

在需要数字的上下文中,枚举类型返回时会自动处理成数字,譬如:

mysql> SELECT enum_col+0 FROM tbl_name;

插入时,数字类型的值会自动与枚举中的索引对应,如果插入的数字带引号,即尝试以字符串形式存入,MySQL 会先从枚举列表中找,如果找不到相应的匹配项,会将这个插入值当索引处理。所以尽量不要设置数字类型的枚举,这导致存取时一些不可预测的情况发生。比如:

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

当你存入数字 2 时,会解析对应到枚举中索引为 2 的值即 '1'。存入字符 '2' 时,与枚举中最后一项匹配。尝试存入 '3' 时,枚举值中没有相应的匹配,此时 MySQL 尝试将 3 当成索引存入,对应的值是枚举中的 '2'

mysql> INSERT INTO t (numbers) VALUES(2),('2'),('3');
mysql> SELECT * FROM t;
+---------+
| numbers |
+---------+
| 1       |
| 2       |
| 2       |
+---------+

查看枚举值

通过 SHOW COLUMNS FROM tbl_name LIKE 'enum_col' 可在结果中的 Type 列查看定义好的枚举其可选值有哪些。

mysql> SHOW COLUMNS FROM enum_test LIKE 'n';
+-------+-------------------+------+-----+---------+-------+
| Field | Type              | Null | Key | Default | Extra |
+-------+-------------------+------+-----+---------+-------+
| n     | enum('0','1','2') | YES  |     | NULL    |       |
+-------+-------------------+------+-----+---------+-------+
1 row in set (0.00 sec)

排序规则

排序时根据其在枚举列表中的索引来定。比如 ENUM('b', 'a') 中 'b' 会排在 a 前面,而不是按常理那样觉得 a 是在 b 前面的。为了避免这种不太直观的情况,在定义枚举时最好按字母顺序定义,也可以在使用 ORDER BY 语句时通过指定排序类型来修正,即使用 ORDER BY CAST(col as CHAR)ORDER BY CONCAT(col)

空字符串会排在非空值前面,NULL 值在所有值前面。

SET 类型

SET 类型是一组字符串值,它的值类似于枚举,必需是定义表时指定的候选值中所列出的值,不同点在于它可同时支持设置多个候选值,用逗号分隔(同时意味着候选值自身不能包含逗号)。

比如类型为定义为 SET('one', 'two') NOT NULL 的列,可有以下合法的值:

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

SET 最多可定义 64 个候选值。严格模式下,定义时如果指定了重复的候选值会抛错。候选值末尾的空白会自动截取掉。

内部存储时其实存的是一个字节表。每个候选值都对应一个二进制值中的一位。比如定义如下的表:

  CREATE TABLE set_test(
    rowid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    myset SET('Travel','Sports','Dancing','Fine Dining')
  );

每个值对应一个二进制位数值,所以每个候选值也对应一个计算后的数字。

Element SET Value Decimal Value
Travel 00000001 1
Sports 00000010 2
Dancing 00000100 4
Fine Dining 00001000 8

这样在设置时,如果设置的是数字,会解析成对应的二进制形式,比如 9=8+1,对应 TravelFine Dining。所以实际设置成了 Travel,Fine Dining

同时 9 的二进制表示是 00001001,通过上面表格对比可知相应位(二进制中第0位和第三位)对应的值也是 Travel/00000001Fine Dining/00001000

在设置时不关心候选值的顺序及次数,多次指定同一个候选值最终只会出现一次。

mysql> CREATE TABLE myset (col SET('a', 'b', 'c', '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


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

通过 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%';

以下用法也是可行的:

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

相关资源