命令行操作MySQL - 字符串类型

这是命令行操作MySQL数据库系列博客的第七篇，今天这篇博客记录字符串类型（char、varchar 与 text）的用法。

---

一、char类型和varchar类型

char类型和varchar类型都在创建表时指定了最大长度，其基本形式如下：字符串类型（M）

其中，“字符串类型”参数指定了数据类型为char类型还是varchar类型；M参数指定了该字符串的最大长度为M。例如，char(4)就是数据类型为char类型，其最大长度为4。

char类型的长度是固定的，在创建表时就指定了。其长度可以是0~255的任意值。例如，char(100)就是指定char类型的长度为100。char 存储值时，它们会用空格右填充到指定的长度。

varchar类型的长度是可变的，在创建表时指定了最大长度。定义时，其最大值可以取0~65535之间的任意值。指定varchar类型的最大值以后，其长度可以在0到最大长度之间。例如，varchar(100)的最大长度是100，但是不是每条记录都要占用100个字节，而是在这个最大值范围内使用多少就分配多少。varchar类型实际占用的空间为字符串的实际长度加1或2，这样即可有效节约系统的空间。

值	CHAR(4)	存储字节数	VARCHAR(4)	存储字节数
‘ ’	‘ ’	4	‘ ’	1
‘ab’	‘ab’	4	‘ab’	3
‘abcd’	‘abcd’	4	‘abcd’	5
‘abcdefgh’	-	插入失败	-	插入失败

例：

如果插入的数据后面有空格，char类型会屏蔽后面隐藏的空格，varchar 不会

实战建议

1. char一定会使用指定的空间，varchar是根据数据来定空间;
2. char的数据查询效率比varchar高：varchar是需要通过后面的记录数来计算;
3. 如果确定数据一定是占指定长度，那么使用char类型；
4. 如果不确定数据到底有多少，那么使用varchar类型；
5. 如果数据长度超过255个字符而在65535之内，直接使用varchar;
6. 如果字符串尾部要保留空格，必须选择varchar.

---

二、text类型

test类型是一种特殊的字符串类型，包括tinytext、text、mediumtext和longtext，其长度和存储空间的对比下表所示:

类型	占用字节	允许的长度
tinytext	1	0 ~ 255 字节
text	2	0 ~ 65535 字节
mediumtext	3	0 ~ 16772150 字节
longtext	4	0 ~ 4294967295 字节

各种test类型的区别在于允许的长度和存储空间不同。因此，在这几种test类型中，根据需求选取既能满足需要又节省空间的类型即可。

特别注意：

1. 以上各类型无须指定长度！
2. 允许的长度是指实际存储的字节数，而不是实际的字符个数，比如假设一个中文字符占两个字节，那么test类型可存储 65535/2 = 32767 个中文字符，而varchar(100)可存储100个中文字符，实际占200个字节，但varchar(65535) 并不能存储65535个中文字符，因为已超出表达范围.

例：

我们创建数据库时，默认是使用utf8mb4编码，该编码中文字符占3个字节。因此，e_text字段最大可以存储255 / 3 = 85个字符，而v_varchar字段是按字符个数统计的，所以255 * 3 = 765 个字节 小于 65535， 所以varchar(255)可以插入255个中文字符。

实战建议

1. char长度固定， 即每条数据占用等长字节空间；适合用在身份证号码、手机号码等定。超过255字 节的只能用varchar或者text；
2. varchar可变长度，可以设置最大长度；适合用在长度可变的属性。
3. text不设置长度， 当不知道属性的最大长度时，适合用text， 能用varchar的地方不用text；
4. 如果都可以选择，按照查询速度： char最快， varchar次之，text最慢。

---

三、实战练习

经过七篇文章的练习，现在来做一个小练习，巩固记忆。

创建一个用户信息表，用来存储用户的如下信息：姓名，手机号码，家庭地址，个人简介，性别，年龄，身份证号。数据库表名可定义为userinfo, 同时增加一个列id 用来作为每一条记录的唯一标识，并设为主键，自加！

以下是答案：

use test;   #选择数据库test 
create table userinfo (
id int(11) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT ‘主键’,
name   varchar(64) DEFAULT NULL COMMENT ‘姓名’,
mobile  char(11)  DEFAULT NULL COMMENT ‘手机号码’,
address varchar(128) DEFAULT NULL COMMENT ‘居住地址’,
description text  DEFAULT NULL COMMENT ‘个人简介-不知道具体的范围，不常更新用text’,
sex     char(1)  DEFAULT NULL COMMENT ‘性别 - 男或女’,
age     tinyint unsigned DEFAULT 0 COMMENT ‘年龄’,
idno    char(18) DEFAULT NULL COMMENT ‘身份证号码’
);   

注意，复制的话，需要将中文单引号改为英文当引号方可运行。

---

总结:
知道长度使用char，不知道长度使用varchar，不知道最大长度使用text；能用varchar的地方不用text。