MySQL-字符串数据类型

CHAR与VARCHAR类型

字符串(文本)类型	特点	长度	长度范围	占用的存储空间
CHAR(M)	固定长度	M	0 <= M <= 255	M个字节
VARCHAR(M)	可变长度	M	0 <= M <= 65535	(实际长度 + 1) 个字节

1. CHAR类型：

• CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则表示长度默认是1个字符。
• 如果保存时，数据的实际长度比CHAR类型声明的长度小，则会在右侧填充空格以达到指定的长度。当MySQL检索CHAR类型的数据时，CHAR类型的字段会去除尾部的空格
• 定义CHAR类型字段时，声明的字段长度即为CHAR类型字段所占存储空间的字节数

CREATE TABLE test_char1(
    c1 CHAR,
    c2 CHAR(5)
);

INSERT INTO test_char1 VALUES('a','Tom');
SELECT c1,CONCAT(c2,'***') FROM test_char1;

INSERT INTO test_char1(c2) VALUES('a ');
SELECT CHAR_LENGTH(c2) FROM test_char1;

2. VARCHAR类型：

• VARCHAR(M) 定义时， 必须指定长度M，否则报错。
• MySQL4.0版本以下，varchar(20)：指的是20字节，如果存放UTF8汉字时，只能存6个（每个汉字3字节）
• MySQL5.0版本以上，varchar(20)：指的是20字符
• 检索VARCHAR类型的字段数据时，会保留数据尾部的空格
• VARCHAR类型的字段所占用的存储空间为字符串实际长度加1个字节

CREATE TABLE test_varchar1(
    NAME VARCHAR #错误
);

#Column length too big for column 'NAME' (max = 21845);
CREATE TABLE test_varchar2(
    NAME VARCHAR(65535) #错误，实际长度=65535+1，超出范围
);

3. 使用 CHAR 或 VARCHAR的情况

类型	特点	空间上	时间上	适用场景
CHAR(M)	固定长度	浪费存储空间	效率高	存储不大，速度要求高
VARCHAR(M)	可变长度	节省存储空间	效率低	非CHAR的情况

• MyISAM数据存储引擎和数据列：MyISAM数据表，最好使用固定长度(CHAR)的数据列代替可变长度(VARCHAR)的数据列。这样使得整个表静态化，从而使数据检索更快，用空间换时间
• MEMORY 存储引擎和数据列：MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系，两者都是作为CHAR类型处理的
• InnoDB 存储引擎，建议使用VARCHAR类型。因为对于InnoDB数据表，内部的行存储格式并没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），而且主要影响性能的因素是数据行使用的存储总量，由于char平均占用的空间多于varchar，所以除了简短并且固定长度的，其他考虑varchar。这样节省空间，对磁盘I/O和数据存储总量比较好

TEXT类型

文本字符串类型	特点	长 度	长度范围	占用的存储空间
TINYTEXT	小文本、可变长度	L	0 <= L <= 255	L + 2 个字节
TEXT	文本、可变长度	L	0 <= L <= 65535	L + 2 个字节
MEDIUMTEXT	中等文本、可变长度	L	0 <= L <= 16777215	L + 3 个字节
LONGTEXT	大文本、可变长度	L	0 <= L<= 4294967295（相当4GB）	L + 4 个字节

• 由于实际存储的长度不确定，MySQL 不允许 TEXT 类型的字段做主键。遇到这种情况，你只能采用CHAR(M)，或者 VARCHAR(M)
• 插入数据包含空格时，不会删除TEXT类型的数据尾部的空格
• TEXT文本类型，可以存比较大的文本段，搜索速度稍慢，因此如果不是特别大的内容，建议使用CHAR，VARCHAR来代替。
• TEXT类型不用加默认值，加了也没用
• text和blob类型的数据删除后容易导致“空洞”，使得文件碎片比较多，所以频繁使用的表不建议包含TEXT类型字段，建议单独分出去，单独用一个表

CREATE TABLE test_text(
    tx TEXT
);

ENUM类型

文本字符串类型	长度	长度范围	占用的存储空间
ENUM	L	1 <= L <= 65535	1或2个字节

• ENUM类型也叫作枚举类型，ENUM类型的取值范围需要在定义字段时进行指定
• 设置字段值时，ENUM类型只允许从成员中选取单个值，不能一次选取多个值。
• 其所需要的存储空间由定义ENUM类型时指定的成员个数决定
• 当ENUM类型包含1～255个成员时，需要1个字节的存储空间
• 当ENUM类型包含256～65535个成员时，需要2个字节的存储空间
• ENUM类型的成员个数的上限为65535个

CREATE TABLE test_enum(
    season ENUM('春','夏','秋','冬','unknow')
);

INSERT INTO test_enum VALUES('春'),('秋');

# 忽略大小写
INSERT INTO test_enum VALUES('UNKNOW');

# 允许按照角标的方式获取指定索引位置的枚举值
INSERT INTO test_enum VALUES('1'),(3);

# Data truncated for column 'season' at row 1
# 必须是从定义时的数据列表选一个
INSERT INTO test_enum VALUES('ab');

# 当ENUM类型的字段没有声明为NOT NULL时，插入NULL也是有效的
INSERT INTO test_enum VALUES(NULL);

SET类型

成员个数范围（L表示实际成员个数）	占用的存储空间
1 <= L <= 8	1个字节
9 <= L <= 16	2个字节
17 <= L <= 24	3个字节
25 <= L <= 32	4个字节
33 <= L <= 64	8个字节

• SET表示一个字符串对象，可以包含0个或多个成员，但成员个数的上限为 64
• 设置字段值时，可以取 取值范围内的 0 个或多个值
• 当SET类型包含的成员个数不同时，其所占用的存储空间也是不同的
• SET类型在存储数据时成员个数越多，其占用的存储空间越大
• SET类型在选取成员时，可以一次选择多个成员，这一点与ENUM类型不同

CREATE TABLE test_set(
    s SET ('A', 'B', 'C')
);

INSERT INTO test_set (s) VALUES ('A'), ('A,B');

#插入重复的SET类型成员时，MySQL会自动删除重复的成员
INSERT INTO test_set (s) VALUES ('A,B,C,A');

#向SET类型的字段插入SET成员中不存在的值时，MySQL会抛出错误。
INSERT INTO test_set (s) VALUES ('A,B,C,D');

二进制字符串类型

• 二进制字符串类型主要存储一些二进制数据，比如可以存储图片、音频和视频等二进制数据

1. BINARY与VARBINARY类型

二进制字符串类型	特点	值的长度	占用空间
BINARY(M)	固定长度	M （0 <= M <= 255）	M个字节
VARBINARY(M)	可变长度	M（0 <= M <= 65535）	M+1个字节

• BINARY和VARBINARY类似于CHAR和VARCHAR，只是它们存储的是二进制字符串。
• BINARY (M)为固定长度的二进制字符串，M表示最多能存储的字节数，取值范围是0~255个字符。如果未指定(M)，表示只能存储 1个字节 。例如BINARY (8)，表示最多能存储8个字节，如果字段值不足(M)个字节，将在右边填充'\0'以补齐指定长度。
• VARBINARY (M)为可变长度的二进制字符串，M表示最多能存储的字节数，总字节数不能超过行的字节长度限制65535，另外还要考虑额外字节开销，VARBINARY类型的数据除了存储数据本身外，还需要1或2个字节来存储数据的字节数。VARBINARY类型 必须指定(M) ，否则报错

CREATE TABLE test_binary1(
    f1 BINARY,
    f2 BINARY(3),
    # f3 VARBINARY,
    f4 VARBINARY(10)
);

INSERT INTO test_binary1(f1,f2) VALUES('a','a');
INSERT INTO test_binary1(f1,f2) VALUES('尚','尚');#失败
INSERT INTO test_binary1(f2,f4) VALUES('ab','ab');

BLOB类型

二进制字符串类型	值的长度	长度范围	占用空间
TINYBLOB	L	0 <= L <= 255	L + 1 个字节
BLOB	L	0 <= L <= 65535（相当于64KB）	L + 2 个字节
MEDIUMBLOB	L	0 <= L <= 16777215 （相当于16MB）	L + 3 个字节
LONGBLOB	L	0 <= L <= 4294967295（相当于4GB）	L + 4 个字节

• BLOB是一个二进制大对象 ，可以容纳可变数量的数据
• 在实际工作中，往往不会在MySQL数据库中使用BLOB类型存储大对象数据，通常会将图片、音频和视频文件存储到服务器的磁盘上，并将图片、音频和视频的访问路径存储到MySQL

CREATE TABLE test_blob1(
    id INT,
    img MEDIUMBLOB
);

JSON 类型

• JSON（JavaScript Object Notation）是一种轻量级的数据交换格式 
• JSON 可以将 JavaScript 对象中表示的一组数据转换为字符串，然后就可以在网络或者程序之间轻松地传递这个字符串，并在需要的时候将它还原为各编程语言所支持的数据格式
• 在MySQL 5.7中，就已经支持JSON数据类型
• 在MySQL 8.x版本中，JSON类型提供了可以进行自动验证的JSON文档和优化的存储结构，使得在MySQL中存储和读取JSON类型的数据更加方便和高效
• 当需要检索JSON类型的字段中数据的某个具体值时，可以使用“->”和“->>”符号

CREATE TABLE test_json(
    js json
);

INSERT INTO test_json (js)
VALUES ('{"name":"songhk", "age":18, "address":{"province":"beijing",
"city":"beijing"}}');

SELECT 
    js -> '$.name' AS NAME,
    js -> '$.age' AS age ,
    js -> '$.address.province' AS province, 
    js -> '$.address.city' AS city
FROM test_json;