字符类型包括:
- CHAR
- VARCHAR
- BINARY
- VARBINARY
- BLOB
- TEXT
- ENUM
- SET
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)
所以如果你期望查询时得到与存储时一致的值, 最好使用 VARBINARY 或 BLOB 类型.
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, 对应 Travel 和 Fine Dining. 所以实际设置成了 Travel,Fine Dining.
同时 9 的二进制表示是 00001001, 通过上面表格对比可知相应位 (二进制中第 0 位和第三位) 对应的值也是 Travel/00000001 和 Fine 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';
- MySQL Reference Manual - String Types
- The MySQL SET Datatype
来源: http://www.bubuko.com/infodetail-3044293.html