前言
Github:https://github.com/yihonglei/road-of-arch/tree/master/thinking-in-mysql
概述
MySql 中提供多种字符串存储类型,分别为:
CHAR, VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM 和 SET 等等类型。
咱们主要选择最常用的分析。
一 CHAR 和 VARCHAR 类型
对于 char 和 varchar 类型,mysql 与 oracle 同样是用于存储字符串,但是设计逻辑完全实践结果有很大区别。
二者的存储方式不同:
char 用于存储定长字符串,长度0~255,而 varchar 用于存储变长字符串,长度0~65535(5.0.3之前的是255)。
在 mysql 中,检索的时候 char 删除了尾部空格,而 varchar 则保持尾部空格,同样,char 存储时自动截掉了尾部空格,
varchar 则保持尾部空格存入。
在 oracle 中,是不会删除 char 的尾部空格的。
通过实例看现象:
从实验结果,查询长度可以看到,第一次插入'ab ',尾部有空格,char没有存入尾部空格,
而varchar则保留空格存入,第二次插入'ab',二者长度都是2。这就是二者在mysql中的区别。
以下单独针对oracle中char和varchar不同空格处理能力进行分析:
CREATE TABLE t1 (
v VARCHAR(4),
c CHAR(4)
);
-- oracle中char为定长,varchar为变长,
-- 'ab ',分别存入数据库长度均为4位
INSERT INTO t1 VALUES('ab ','ab ');
COMMIT;
SELECT LENGTH(v),LENGTH(c) FROM t1;
-- 如果存入的是'ab',哪么对于varchar的长度是2位,
-- 而对于char的长度是4位,这是因为varchar自动根据
-- 实际情况分配长度,节省空间,而char则是根据定义
-- 的长度4位进行存储,不够的补空串存储,浪费空间。
-- 但是,char定长的查询速度比varchar快,char比varchar
-- 浪费存储空间,这就是计算机中时间和空间永远不可调和的矛盾,
-- 实际使用时比如身份证查询等定长字符串可以考虑用char,
-- 而其他不是经常用于查询,对查询效率没有很高要求的,可以考虑
-- 用varchar,有时候需要时间快,有时候需要节省存储空间
INSERT INTO t1 VALUES('ab','ab');
COMMIT;
SELECT LENGTH(v),LENGTH(c) FROM t1;
oracle以上最后一个语句查询的结果:
mysql与oracle关于定长与变长的总结:
(1)mysql中存入'ab ', char去掉空格,存入2位,varcahr保留空格存入4位;
如果都存入'ab', char和varchar二者都是两位存入。
(2)oracle中存入'ab ',char和varchar两者都存入4位;
如果都 存入'ab', char是定长,存入4位,自动补了两位空,
varchar是变长,存入2位,自动根据实际长度分配存入空间。
对于mysql中char会自动去掉末尾空格,这是mysql的优点,oracle中char确没有这样的自动功能。
mysql与oracle的区别在于,mysql的char会自动去掉尾部空格存储,而oracle不会,
mysql中char尾部没有空格的,按实际长度存入,而oracle中实际不足定义位数,自动补空到定长位存储。
二 BINARY和VARBINARY类型
BINARY和VARBINARY类似于CHAR和VARCHAR,不同的是他们包含二进制字符串而不包含非二进制字符串。
通过实例探究一下二者的存储:
通过hex()转换为16进制。
三 ENUM类型
字符串中的ENUM称为枚举类型,它的值范围需要在创建的时候通过枚举方式显示的指定,最多运行存储65535个成员。
通过insert语句批量插入('M'),('1'),('2'),('F'),('f'),(null)六个元素,其中数字1表示取enum('M','F')中第一元素M,而数字2表示取第二个元素F,
而对于小写f,是不区分大小写的。最后一次插入了一个3,直接报错,因为enum('M','F')中根本找不到第三个元素,只能报错了。
对于插入数值,如果从enum中找到元素就用找到的元素插入,找不到就报错,不会给你默认插入第一个或第二个,证明有些书上说的跟
实际的不一样。
四 SET类型
SET与ENUM类似,最大的不同之处在于SET可以一次选取多个元素,而ENUM一次只能选择一个元素。
从插入元素可以看出,来源于set定义时所有元素的组合,不在元素范围,将报错,同时,如果组合重复,自动去重处理。