Mysql数据类型面试题15连问

整数类型的 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。

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

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

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

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

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

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。

utf8 、utf8mb3和 utf8mb4的区别

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

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

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

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

• 差异比较

差异点	utf8mb3	utf8mb4
最大使用字节数	3	4
支持字符类型	BMP	BMP+其它字符
字符类型	常见的 Unicode 字符	常见的 Unicode 字符 + 部分罕用汉字 + emoji表情 + 新增的 Unicode 字符等
Unicode范围	U0000 - U+FFFF（即BMP)	U0000 - U+10FFFF
占用存储空间	略小（如CHAR(10) 需要10 * 3 = 30 个字节的空间；VARCHAR 类型需要额外使用1个字节来记录字符串的长度）	稍大（如CHAR(10) 需要 10 * 4 = 40 个字节的空间；VARCHAR 类型需要额外使用2个字节来记录字符串的长度）
兼容性	切换至utf8mb4 一般不会有问题，但要注意存储空间够不够、排序规则是否变化	切换至utf8mb3可能会有问题，字符丢失、报错或乱码
安全性	稍低，更容易被恶意字符串攻击	较高，保留恶意字符串，然后报错或乱码提示

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