MySQL 数据类型、字符集相关内容

字符集

计算机只能存储二进制的数据，所谓字符集就是字符的集合，它设定了字符和二进制的数据的对应关系。把字符转变成二进制的过程叫做编码，二进制数据解析成字符的过程叫做解码

每一套字符集都有自己的编解码规则，乱码就是使用了错误的字符集来解码造成的

来看看常用的字节码吧：

ASCII应该是最为熟悉的字符集了，它定义了 128 个字符，其中有 33 个控制字符（比如回车、删除）无法显示

但是ASCII是美国人发明的，没有考虑到中文如何存储。为了解决这个问题，Unicode横空出世，它希望将世界上所有的字符都纳入其中。虽然它做到了，不过Unicode字符集并没有规定如何使用二进制数据表示这些字符

于是又诞生了UTF-8编码，它是一种可变长的字符集，对于中文字符，它使用3个字符来存储；对于英语字母，UTF-8编码和ASCII码是相同的，UTF-8编码最多四个字节

如何区分变长：那既然它是可变长的，怎么区分某个字节代表的是一个单独的字符还是某个字符的一部分呢？ASCII码用一个字节表示128个字符，所以它的最高位一定是0，在遇到最高位为0 的情况时用ASCII码即可，判断用两个表示与用三个表示的字符也是同理

MySQL中的ut8：在mysql中定义了默认的utf-8是阉割过的字符集，它用1到4个字节来表示字符，全名叫utf-8mb3

但是utf8mb3没有考虑到生僻字以及繁体字，而随着人类的语言越来越抽象，emoji 类型字符也成了常用的字符，因此，我们一般在 mysql 中使用 utf8mb4，它是UTF-8 的完整实现，使用1到4个字节来表示字符

只有mysql中有utf8mb4的定义，在java程序中是不认得utf8mb4的，因此在java中直接定义为utf8即可，而在mysql中则要显示定义utf8mb4

比较规则：在MySQL 中可以设置一些比较规则，按照二进制大小来比较char类型不是在每一种情况下都正确的。比如现在有一个需求，要求A与a是比较出来是相等的字符，这就需要提前设定一些比较规则了。MySQL中默认的比较规则是utf8_general_ci

类型介绍

• int：整型使用int定义，可以设置有符号和无符号（UNSIGNED），一个int占4字节，int后面可以不加(位数)，加了也没有意义，同时，还有TINYINT、BIGINT等，类似java中byte、长整形

• 浮点：MySQL中可以指定浮点数和定点数的精度，浮点型使用 float 或者 double 保存，后面一般是(M，D)，M表示该数据的总位数，D表示小数点后面精度。我们还可以使用 DOUBLE PRECISION，该关键字是 FLOAT 关键字的同义词

• decimal：定点数一般来说比浮点数好，它使用string来存放小数点后的数据，这样就不会出现精度丢失的问题，它也是(M，D)形式的。decimal型的默认整数位为10，小数位为0，即默认为整数

• varchar：字符型分用于存放较短的字符char（长度不可变，插入的长度比定义小也会使用定义长度的空间，效率较高，后面括号内数字表示可以存放多少字符）和varchar（长度可变，使用插入长度的空间），存储大文本使用text

• 日期：日期类型有DATE（存放年月日，使用三个字节，时间范围为‘1000-01-01’ to ‘9999-12-31’），DATETIME（存放年月日时分秒，使用八个字节，‘1000-01-01 00:00:00’ to ‘9999-12-31 23:59:59’）

• DATASTAMP（存放年月日时分秒，使用四个字节，‘1970-01-01 00:00:01’ UTC to ‘2038-01-19 03:14:07’。timestamp 翻译为汉语即"时间戳"，它是当前时间到 Unix元年(1970 年 1 月 1 日 0 时 0 分 0 秒)的秒数。对于某些时间的计算，如果是以 datetime 的形式会比较困难，假如我是 1994-1-20 06:06:06 出生，现在的时间是 2016-10-1 20:04:50 ，那么要计算我活了多少秒钟用 datetime 还需要函数进行转换，但是 timestamp 直接相减就行。同时timestamp 只占 4 个字节，而且是以 utc 的格式储存， 它会自动检索当前时区并进行转换，这个禁止使用）

• ENUM 和 SET：有限而且固定值列，比如enum(‘男’, ‘女’)去定义性别，ENUM类型最多允许65536个值，SET类型最多允许64个值，不要使用ENUM，因为数据迁移的时候，他几乎不可能被其他数据库所支持；enum的枚举值对应的索引是从1开始的，可能会造成一些疑惑的问题

char（5）与 int（5）

1，在每个数据类型后，都可以加数字，这个数字代表这个类型最多多少，比如char（20）代表20个字符，char（5）代表5个字符

2，值得注意的是，int类型后面的数字代表自动补全，不管它设置多少，int型都占4字节；在使用int(3)的时候如果你输入的是10，那么显示给你的字符宽度就为3，会默认给你显示3位，当你不足3位时，会帮你补齐3位，当你超过3位时，就没有任何的影响,不会报错（其他的数据类型比如double，char超过了都会报错），如果你存1000，显示这个值就是1000

int、char 以及 varchar 的性能对比

当它们同时存放数据的时候，不管是有索引还是无索引，int类型都是最快的，因为整数得益于原生支持，而 char 和 varchar 的性能差不多

字符串和数值作比较的时会发生什么

字符串和数值都会转换成浮点数进行比较，字符串在转浮点数时，如果字符串开头是数字，那就一直截取，直到截取到的不是数字是其他字符为止。若开头不是数字，那么转换数值类型的结果就直接取0

为什么要非业务属性的自增 Id 做主键

原因1：业务层面

业务主键，就是从业务层面上来反应数据等唯一性，比如订单号，员工编号，商品编号等。这些编号一般都会包含一定的业务含义。比如订单号会拼入下单日期时间，员工编号可能拼入入职时间，商品编号会拼入商品分类缩写等等

非业务主键，就是和业务无关，真正的表数据的唯一标记。一般会采用 bigint 类型的自增列作为非业务主键。非业务主键是数据表真正意义上的主键。主键索引也是基于这个非业务主键来生成的

原因2：技术层面

根据 b+ 树的实现原理，如果不规则插入的话，先查找自己应该被放在哪个位置，再根据 B+ 树自己实现的自适应平衡算法，如果一个节点超过最大值的话它会自己执行叶分裂过程，这样的调整不仅浪费时间，分裂之后的两个节点还只用了一半的空间。这种情况叫中间点（mid point）分裂，同时，这种分裂会导致数据在不同的页面中，变得很离散

这是 b+ 树的叶分裂算法相关内容：https://blog.csdn.net/sekever/article/details/120156467

如果是自增主键的话，根据 MySQL 自己的优化，它不会执行上面过程，如果插入的数据是自增长的，它会直接把数据添加到最右侧节点下，而旧的节点不用移动。如果最右侧节点满了，它会新生成一个节点来存放数据。这种情况也叫插入点优化

而这个非业务属性，就保证了自增这一条件，如果是业务属性，不可避免的可能会是字符串类型、非自增形式

数据库字段类型和 Java 的对应关系

CHAR、VARCHAR 、LONGVARCHAR —> String

SMALLINT —> Short

INTEGER —> Int

不要用DOUBLE和FLOAT存放小数

DECIMAL 、 NUMERIC —> BigDecimal

java中日期类别用Date了

DATE —> LocalDate

TIME（存放时分秒） —> LocalTime

TIMESTAMP、DATETIME —> LocalDateTime

TINYINT —> 自己定义的枚举类