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