MySQL String Types

CHAR VARCHAR

char和varchar类型在最大长度和尾部补0方式有差异

CHAR 固定长度
最大长度可以设置为255,
存储时长度不满足,会在尾部填充0,在取数据时会将0自动去掉
存储长度超过时,会截断

VARCHAR 可变长度
长度可以设置为65535
存储长度不足时,不填充0,会缩小存储空间
存储长度超过时,会截断

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 bytes

通过下面这个例子,可以看到,在VARCHAR(4)字符后面的空白字符被舍去.

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)

The BINARY and VARBINARY Types

The BLOB and TEXT Types

BLOB可以存大量的可变的二进制数据
TEXT可以存大量的可变的字符数据

Data Type	Storage Required
TINYBLOB, TINYTEXT	L + 1 bytes, where L < 28
BLOB, TEXT	L + 2 bytes, where L < 216
MEDIUMBLOB, MEDIUMTEXT	L + 3 bytes, where L < 224
LONGBLOB, LONGTEXT	L + 4 bytes, where L < 232

The ENUM Type

下面的例子中由于我们将size设置成了ENUM类型,我们既可以按照字符串的方式来存,又可以减少存储空间.
使用ENUM时存储large等包含的字符串只占用1个字节,而如果直接存large则会占用char或varchar对应的字节

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;

使用索引
如果有:ENUM('Mercury', 'Venus', 'Earth')
值所对应的索引为:

Value	Index
NULL	NULL
‘’	0
‘Mercury’	1
‘Venus’	2
‘Earth’	3

The SET Type

SET类型包含唯一值

SET(‘one’, ‘two’) NOT NULL

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

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)

参考:
https://dev.mysql.com/doc/refman/8.0/en/string-types.html
https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html