SQL：char 和 varchar、binary 和 varbinary、二进制字符串、严格模式、汉字编码方式
- 参考：MySQL中varchar与char区别 博主：叫我大头
一、char 和 varchar 区别
1、定长和变成
-
char：定长，长度固定；varchar：变长，长度可变；
- 当插入的字符串长度小于定义长度时，则会以不同的方式来处理，如char（10），表示存储的是10个字符，无论你插入的是多少，都是10个，如果少于10个，则用空格填满。而varchar（10），小于10个的话，则插入多少个字符就存多少个。
-
当所插入的字符串超出它们的长度时，视情况来处理：
- 如果是严格模式，则会拒绝插入并提示错误信息；
- 如果是宽松模式，则会截取然后插入；
2、存储容量不同
- char：最多能存放的字符个数 255，和编码无关；
- varchar：最多能存放 65532 个字符；
- varchar 的最大有效长度由最大行大小和使用的字符集确定，整体最大长度是 65,532字节；
3、存储效率不同
- char：存储长度固定，存储速度较快，存储效率较高；
- varchar：存储长度不固定，存储速度较慢，存储效率较低；
- 原因：varchar 进行存储前需要先计算长度，再加上计算的到字符串长度信息，一般 1-2 个 byte，所以每次存储都要有额外的计算，得到长度等工作；
二、varchar
1、varchar 怎么知道所存储字符串的长度？
- 先计算长度后，再加上计算的到字符串长度信息，一般1-2个byte，所以每次存储都要有额外的计算，得到长度等工作；
- 对于 varchar 字段来说，需要使用一个（如果字符串长度小于 255）或两个字节（长度大于 255）来存储字符串的长度，因为他需要有一个 prefix 来表示他具体 bytes 数是多少；（因为varchar是变长的，没有这个长度值他不知道如何读取数据）。
2、varchar(M)，既然 varchar 是自适应存储空间，能不能把 M 值尽量往大了设置？
- 答案是否定的；
- mysql 把表信息放到内存中（查询第一次后，就缓存住了，linux 下很明显，但windows下似乎没有，不知道为啥），这时内存的申请是按照固定长度来的，如果 varchar 很大就会有问题，所以还是应该按需索取。；
3、MySQL 中 varchar 最大长度是多少？
- 这不是一个固定的数字，先简要说明一下限制规则：
-
限制规则
- 字段的限制在字段定义的时候有以下规则：
- 存储限制
- varchar 字段是将实际内容单独存储在聚簇索引之外，内容开头用1到2个字节表示实际长度（长度超过255时需要2个字节），因此最大长度不能超过65535字符；
- 编码长度限制
- 字符类型若为 gbk，每个字符最多占2个字节，最大长度不能超过32766；
- 字符类型若为 utf8，每个字符最多占3个字节，最大长度不能超过21845；
- 若定义的时候超过上述限制，则varchar字段会被强行转为 text 类型，并产生warning；
- 行长度限制
- 导致实际应用中 varchar 长度限制的是一个行定义的长度；
- MySQL要求一个行的定义长度不能超过 65535bytes，若定义的表长度超过这个值，则提示：
- ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs；
- 存储限制
- 字段的限制在字段定义的时候有以下规则：
-
计算例子
- 举两个例说明一下实际长度的计算：
-
若一个表只有一个varchar 类型，如定义为：create table t4(c varchar(N)) charset=gbk；
-
则此处 N 的最大值为 (65535-1-2) / 2 = 32766 个字符；
- 减1的原因是实际行存储从第二个字节开始；
- 减2的原因是varchar头部的2个字节表示长度；
- 除2的原因是字符编码是gbk；
-
- 若一个表定义为：create table t4(c int, c2 char(30), c3 varchar(N)) charset=utf8;
- 则此处N的最大值为 (65535-1-2-4-30*3)/3=21812；
- 减1和减2与上例相同；
- 减4的原因是int类型的c占4个字节；
- 减30*3的原因是char(30)占用90个字节，编码是utf8；
- 如果被varchar超过上述的规则 2（编码长度限制），被强转成 text 类型，则每个字段占用定义长度为11字节，当然这已经不是 “varchar” 了；
- 则此处N的最大值为 (65535-1-2-4-30*3)/3=21812；
- create table t4(c int, c2 char(30), c3 varchar(21812)) ENGINE=InnoDB DEFAULT CHARSET=utf8；
- 则此处N的最大值为 (65535-1-2-4-30*3)/3=21812；
-
- 举两个例说明一下实际长度的计算：
4、在 MySQ L中用来判断是否需要进行对据列类型转换的规则
- 在一个数据表里，如果每一个数据列的长度都是固定的，那么每一个数据行的长度也将是固定的；
- 只要数据表里有一个数据列的长度的可变的，那么各数据行的长度都是可变的；
- 如果某个数据表里的数据行的长度是可变的，那么，为了节约存储空间，MySQL会把这个数据表里的固定长度类型的数据列转换为相应的可变长度类型．
- 例外：长度小于 4 个字符的 char 数据列不会被转换 varchar 类型；
-
另外
- char 一般存储很短的信息、固定长度的信息、十分频繁改变的 column；
三、tinary 和 vartinary 区别
-
BINARY 和 VARBINARY 类型类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字节字符串；
- BINARY：定长，长度固定；
- 指定长度之后，不足最大长度的，将在它们右边填充‘\0’补齐以达到指定长度。
- 例如：指定列数据类型为BINARY(3)，当插入‘a’ 时，存储的内容实际为“a\0\0”，当插入“ab”时，实际存储的内容为“ab\0”，不管存储的内容是否达到指定的长度，其存储空间均为指定的值M。
- VARBlNARY：变长，长度可变；
- 指定好长度之后，其长度可以在 0 到最大值之间；
- 例如：指定列数据类型为VARBINARY(20)，如果插入的值的长度只有10，则实际存储空间为 10 加 l，即其实际占用的空间为字符串的实际长度加 l。
- BINARY：定长，长度固定；
-
其使用的语法格式如下：
- column_name BINARY(M)或者VARBINARY(M)；
四、二进制字符串
- 二进制字符串：用于存储二进制的大对象，二进制字符串类型有 BIT、BLOB、BINARY 和 VARBINARY；
- 二进制的大对象：非传统数据，如图像、音频和视频文件、程序可执行文件等；
- TEXT 列被视为非二进制字符串(字符字符串)；
- 二进制的大对象：非传统数据，如图像、音频和视频文件、程序可执行文件等；
- 在实际操作中，一般不建议将二进制的大文件存储在数据库中，而是将大对象的物理位置存储在数据中；
五、严格模式
- mysql5.6之后的版本默认都是严格模式；
- 查看数据库配置中变量名包含 mode 的配置信息：
-
show varibles like "%mode%";
-
- set session：只在当前操作界面有效；
- set global：全局有效；
- 设置严格模式：set global sql_mode = 'STRICT_TRANS_TABLES';
- 设置宽松模式：set sql_mode='NO_ENGINE_SUBSTITUTION';
六、MySQL 中汉字的编码方式
- 一般 mysql，用的是 utf-8，不过这个最好看一下；
- 字符集分单字节和多字节：
- Latin1：一个字符占一个字节，最多能存放 65532 个字符；
- GBK：一个字符占两个字节， 最多能存 32766 个字符（varchar）；
- UTF8：一个字符占三个字节， 最多能存 21844 个字符（varchar）；
- 更改表的编码：
-
alter table tb_name character set utf8;
-
- 数据列的编码格式：
-
alter table tb_name change first_name first_name varchar(20) character set utf8 not null;
-
