整数类型的 UNSIGNED 属性有什么用？

MySQL 中的整数类型可以使用可选的 UNSIGNED 属性来表示不允许负值的无符号整数。使用 UNSIGNED 属性可以将正整数的上限提高一倍，因为它不需要存储负数值。

例如， TINYINT UNSIGNED 类型的取值范围是 0 ~ 255，而普通的 TINYINT 类型的值范围是 -128 ~ 127。INT UNSIGNED 类型的取值范围是 0 ~ 4,294,967,295，而普通的 INT 类型的值范围是 -2,147,483,648 ~ 2,147,483,647。

对于从 0 开始递增的 ID 列，使用 UNSIGNED 属性可以非常适合，因为不允许负值并且可以拥有更大的上限范围，提供了更多的 ID 值可用。

char和varchar的区别

CHAR

• CHAR类型用于存储固定长度字符串：MySQL总是根据定义的字符串长度分配足够的空间。当存储CHAR值时，MySQL会删除字符串中的末尾空格同时，CHAR值会根据需要采用空格进行剩余空间填充，以方便比较和检索。但正因为其长度固定，所以会占据多余的空间，也是一种空间换时间的策略；
• CHAR适合存储很短或长度近似的字符串。例如，CHAR非常适合存储密码的MD5值、定长的身份证等，因为这些是定长的值。
• 对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型占用磁盘的存储空间是连续分配的，不容易产生碎片。
• 对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节，因为还有一个记录长度的额外字节。

VARCHAR：

• VARCHAR类型用于存储可变长度字符串，是最常见的字符串数据类型。它比固定长度类型更节省空间，因为它仅使用必要的空间(根据实际字符串的长度改变存储空间)。

• VARCHAR需要使用1或2个额外字节记录字符串的长度：如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节。假设采用latinl字符集，一个VARCHAR(10)的列需要11个字节的存储空间。VARCHAR(1000)的列则需要1002 个字节，因为需要2个字节存储长度信息。

• VARCHAR节省了存储空间，所以对性能也有帮助。但是，由于行是变长的，在UPDATE时可能使行变得比原来更长，这就导致需要做额外的工作。如果一个行占用的空间增长，并且在页内没有更多的空间可以存储，在这种情况下，不同的存储引擎的处理方式是不一样的。例如，MylSAM会将行拆成不同的片段存储，InnoDB则需要分裂页来使行可以放进页内。

• 操作内存的方式：对于varchar数据类型来说，硬盘上的存储空间虽然都是根据字符串的实际长度来存储空间的，但在内存中是根据varchar类型定义的长度来分配占用的内存空间的，而不是根据字符串的实际长度来分配的。显然，这对于排序和临时表会较大的性能影响。

VARCHAR(100)和 VARCHAR(10)的区别是什么？

VARCHAR(100)和 VARCHAR(10)都是变长类型，表示能存储最多 100 个字符和 10 个字符。因此，VARCHAR (100) 可以满足更大范围的字符存储需求，有更好的业务拓展性。而 VARCHAR(10)存储超过 10 个字符时，就需要修改表结构才可以。

虽说 VARCHAR(100)和 VARCHAR(10)能存储的字符范围不同，但二者存储相同的字符串，所占用磁盘的存储空间其实是一样的，这也是很多人容易误解的一点。

不过，VARCHAR(100) 会消耗更多的内存。这是因为 VARCHAR 类型在内存中操作时，通常会分配固定大小的内存块来保存值，即使用字符类型中定义的长度。例如在进行排序的时候，VARCHAR(100)是按照 100 这个长度来进行的，也就会消耗更多内存。

DECIMAL 和 FLOAT/DOUBLE 的区别是什么？

DECIMAL 和 FLOAT 的区别是：DECIMAL 是定点数，FLOAT/DOUBLE 是浮点数。DECIMAL 可以存储精确的小数值，FLOAT/DOUBLE 只能存储近似的小数值。

DECIMAL 用于存储具有精度要求的小数，例如与货币相关的数据，可以避免浮点数带来的精度损失。

在 Java 中，MySQL 的 DECIMAL 类型对应的是 Java 类 java.math.BigDecimal。

在 MySQL 中存储金额数据，应该使用什么数据类型?

在数据库中业界常用两种类型来存储金额：bigint和 decimal。

• bigint 代码中用 long。

  • 范围：可以存储的整数范围为-2^63到 2^63-1(在 MySQL 中为 64 位有符号整数)
  • 存储空间：占用8字节(64位)
  • 精度：精确存储整数，但不支持小数部分，存储的金额单位是分

• decimal 代码中使用 BigDecimal。

  • 范围：可以存储的数字范围和小数位数由定义的精度和标度决定
  • 存储空间：存储空间取决于定义的精度和标度，存储较大数值时会占用更多空间
  • 精度：支持高精度的小数运算，精确存储定点数，一般用 decimal(18,6)，18 是总位数，6 是小数

项目中到底选择 Long 还是 BigDecimal?

long 类型保存到分，使得小数位(厘)的数据不好处理(需要手动处理，比较麻烦)，因此精度不够高。

在高精度金额计算场景下不合适，有些三方支付系统是需要抽成的，例千分之三、万分之一等等，这类的抽成要求精度比较高，例如 会得到1234.5678 这样的金额，乘以 100 后仍然无法存储为精确整教，还是小数,，存储则会丢失精度。0.0078 看似很少，但抽成的笔数一多，比如一天 30 万笔，每笔少个 0.0078，每天的差额也得 2340 元！一年不得 85 万！所以高精度的计算不推荐使用 bigint。

BigDeimnal则很活用于高精度金额场景，且非常灵活，只不过相对于long性能会差一些，但是在大部分业务上我个人认为可以忽略这个性能问题，除非是特殊场景要求极端性能，所以一般情况下我推荐使用 gDecimal.

int(10)和char(10)的区别？

int(10)中的10表示的是显示数据的长度，而char(10)表示的是存储数据的长度。

为什么不推荐使用 TEXT 和 BLOB？

数据库规范通常不推荐使用 BLOB 和 TEXT 类型，这两种类型具有一些缺点和限制，例如：

• 不能有默认值。
• 在使用临时表时无法使用内存临时表，只能在磁盘上创建临时表（《高性能 MySQL》书中有提到）。
• 检索效率较低。
• 不能直接创建索引，需要指定前缀长度。
• 可能会消耗大量的网络和 IO 带宽。
• 可能导致表上的 DML 操作变慢。
• ……

TEXT 类型最大可以存储多长的文本?

TEXT 是非标准字符串类型，除了它还有 TINYTEXT、MEDIUMTEXT 和 LONGTEXT，一般我们存储一些不确定的较长文本才会使用到它们，例如文章之类的。

它们的存储的文本长度汇总如下:

• TINYTEXT：最大长度为 255 字节。
• TEXT：最大长度为 65,535 字节(约 64 KB)。
• MEDIUMTEXT：最大长度为16,777,215字节(约16 MB)
• LONGTEXT：最大长度为 4,294,967,295 字节(约4GB)。

DATETIME 和 TIMESTAMP 的区别是什么？

DATETIME 类型没有时区信息，TIMESTAMP 和时区有关。

TIMESTAMP 只需要使用 4 个字节的存储空间，但是 DATETIME 需要耗费 8 个字节的存储空间。但是，这样同样造成了一个问题，Timestamp 表示的时间范围更小。

• DATETIME：1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
• Timestamp：1970-01-01 00:00:01 ~ 2037-12-31 23:59:59

Boolean 类型如何表示？

MySQL 中没有专门的布尔类型，而是用 TINYINT(1) 类型来表示布尔值。TINYINT(1) 类型可以存储 0 或 1，分别对应 false 或 true。

为什么不建议使用null作为默认值

Mysql不建议用Null作为列默认值不是因为不能使用索引，而是因为：

• 索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化。比如进行索引统计时，count(1),max(),min() 会省略值为NULL 的行。
• NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题，因为 InnoDB 存储记录的时候，如果表中存在允许为 NULL 的字段，那么行格式 (opens new window)中至少会用 1 字节空间存储 NULL 值列表。建议用""或默认值0来代替NULL

不建议使用null作为默认值，并且建议必须设置默认值，原因如下：

• 既然都不可为空了，那就必须要有默认值，否则不插入这列的话，就会报错；
• 数据库不应该是用来查问题的，不能靠mysql报错来告知业务有问题，该不该插入应该由业务说了算；
• 对于DBA来说，允许使用null是没有规范的，因为不同的人不同的用法。

但像合同生效时间、获奖时间 等这种不可控字段，是可以不设置默认值的，但同样需要not null

为什么禁止使用外键

• 外键会降低数据库的性能。在MySQL中，外键会自动加上索引，这会使得对该表的查询等操作变得缓慢，尤其是在大型数据表中。
• 外键也会限制了表结构的调整和更改。在实际应用中，表结构经常需要进行更改，而如果表之间使用了外键约束，这些更改可能会非常难以实现。因为更改一个表的结构，需要涉及到所有以其为父表的子表，这会导致长时间锁定整个数据库表，甚至可能会导致数据丢失。
• 在MySQL中，外键约束可能还会引发死锁问题。当想要对多个表中的数据进行插入、更新、删除操作时，由于外键约束的存在，可能会导致死锁，需要等待其他事务释放锁。
• MySQL中使用外键还会增加开发难度。开发人员需要处理数据在表之间的关系，而这样的处理需要花费更多的时间和精力，以及对数据库的深入理解。同时，外键也会增加代码的复杂度，使得SQL语句变得难以理解和调试。

在阿里巴巴开发手册中也有提到，传送门

使用自增主键有什么好处？

自增主键可以让主键索引尽量地保持递增顺序插入，避免了页分裂，因此索引更紧凑，在查询的时候，效率也就更高。

自增主键保存在什么地方？

不同的引擎对于自增值的保存策略不同：

• MyISAM引擎的自增值保存在数据文件中。
• 在MySQL8.0以前，InnoDB引擎的自增值是存在内存中。MySQL重启之后内存中的这个值就丢失了，每次重启后第一次打开表的时候，会找自增值的最大值max(id)，然后将最大值加1作为这个表的自增值；MySQL8.0版本会将自增值的变更记录在redo log中，重启时依靠redo log恢复。

自增主键一定是连续的吗？

不一定，有几种情况会导致自增主键不连续。

1、唯一键冲突导致自增主键不连续。当我们向一个自增主键的InnoDB表中插入数据的时候，如果违反表中定义的唯一索引的唯一约束，会导致插入数据失败。此时表的自增主键的键值是会向后加1滚动的。下次再次插入数据的时候，就不能再使用上次因插入数据失败而滚动生成的键值了，必须使用新滚动生成的键值。

2、事务回滚导致自增主键不连续。当我们向一个自增主键的InnoDB表中插入数据的时候，如果显式开启了事务，然后因为某种原因最后回滚了事务，此时表的自增值也会发生滚动，而接下里新插入的数据，也将不能使用滚动过的自增值，而是需要重新申请一个新的自增值。

3、批量插入导致自增值不连续。MySQL有一个批量申请自增id的策略：

• 语句执行过程中，第一次申请自增id，分配1个自增id
• 1个用完以后，第二次申请，会分配2个自增id
• 2个用完以后，第三次申请，会分配4个自增id
• 依次类推，每次申请都是上一次的两倍（最后一次申请不一定全部使用）

如果下一个事务再次插入数据的时候，则会基于上一个事务申请后的自增值基础上再申请。此时就出现自增值不连续的情况出现。

4、自增步长不是1，也会导致自增主键不连续。

InnoDB的自增值为什么不能回收利用？

主要为了提升插入数据的效率和并行度。

假设有两个并行执行的事务，在申请自增值的时候，为了避免两个事务申请到相同的自增 id，肯定要加锁，然后顺序申请。

假设事务 A 申请到了 id=2， 事务 B 申请到 id=3，那么这时候表 t 的自增值是 4，之后继续执行。

事务 B 正确提交了，但事务 A 出现了唯一键冲突。

如果允许事务 A 把自增 id 回退，也就是把表 t 的当前自增值改回 2，那么就会出现这样的情况：表里面已经有 id=3 的行，而当前的自增 id 值是 2。

接下来，继续执行的其他事务就会申请到 id=2，然后再申请到 id=3。这时，就会出现插入语句报错“主键冲突”。

而为了解决这个主键冲突，有两种方法：

• 每次申请 id 之前，先判断表里面是否已经存在这个 id。如果存在，就跳过这个 id。但是，这个方法的成本很高。因为，本来申请 id 是一个很快的操作，现在还要再去主键索引树上判断 id 是否存在。
• 把自增 id 的锁范围扩大，必须等到一个事务执行完成并提交，下一个事务才能再申请自增 id。这个方法的问题，就是锁的粒度太大，系统并发能力大大下降。

可见，这两个方法都会导致性能问题。

因此，InnoDB 放弃了“允许自增 id 回退”这个设计，语句执行失败也不回退自增 id。

自增主键会遇到什么问题？

使用数据库的自增主键，虽然在很多情况下都很方便，但也可能遇到一些问题：

• 插入性能问题：对于非常高并发的插入操作，自增主键可能会成为性能瓶颈。因为每次插入新记录时，都需要获取一个新的自增ID，这个操作是串行的，无法并发执行。
• 主键耗尽：如果表的记录非常多，可能会出现自增主键耗尽的情况。尤其是对于定义为整型的自增主键，如果插入的记录数超过了整型的最大值，就无法再插入新的记录。
• 分布式系统问题：在分布式系统中，如果多个数据库节点都需要生成自增主键，就需要保证生成的主键在全局是唯一的。这通常需要引入额外的机制或工具，比如分布式ID生成器。

如果 InnoDB 表没有配置主键，有最大值上限吗?

如果我们在 InnoDB 表中不配置主键，那么默认 InnoDB 会创建一个不可见的长度为 6 个字节的 row id.

InnoDB 在全局维护了一个 dict_sys.row_id 值，所有需要用到 row id 的表，每次插入一行数据，都会获取这个值，然后将其+1。

这个值的范周是 0~2^48-1。如果这个值达到上限后，又会从0开始，然后继续循环，如果插入的新数据的 row id 在表中已存在，那么老的数据会被这个新数据覆盖，不会产生有任何报错。

如果要验证这个情况，可以通过 gbd 动态地将 mysgl 的 dict sys.row id 修改，执行多次插入就能发现覆盖的问题。

一般生产环境中不会有表没有设置主键的情况，这个仅做了解即可。

utf8 、utf8mb3和 utf8mb4的区别

utf8mb3：只支持最长三个字节的BMP（Basic Multilingual Plane，基本多文种平面）字符（不支持补充字符）。

utf8mb4：mb4即 most bytes 4，即最多使用4个字节来表示完整的UTF-8，具有以下特征：

• 支持BMP和补充字符。
• 每个多字节字符最多需要四个字节。

utf8mb4是utf8的超集并完全兼容它，是MySQL 在 5.5.3 版本之后增加的一个新的字符集，能够用四个字节存储更多的字符，几乎包含了世界上所有能看到见的语言字符。

• 差异比较

如何选择？一句话就是，根据具体的业务需求和实际情况，选择最合适的字符集。

本文来自在线网站：seven的菜鸟成长之路，作者：seven，转载请注明原文链接：www.seven97.top