02-MySQL 字段

官网保平安：https://www.mysql.com/
MySQL 思维导图：https://www.processon.com/view/link/63bc2c8ea82ed9463ba99f38

MySQL 字段类型

MySQL 字段类型可以简单分为三大类：

  • 数值类型：整型（TINYINT、SMALLINT、MEDIUMINT、INT 和 BIGINT）、浮点型（FLOAT 和 DOUBLE）、定点型（DECIMAL）
  • 字符串类型：CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB 等，最常用的是 CHAR 和 VARCHAR。
  • 日期时间类型：YEAR、TIME、DATE、DATETIME 和 TIMESTAMP 等。

MySQL 常见字段类型总结

MySQL 字段类型比较多，我这里会挑选一些日常开发使用很频繁且面试常问的字段类型，以面试问题的形式来详细介绍。如无特殊说明，针对的都是 InnoDB 存储引擎。

另外，推荐阅读一下《高性能 MySQL(第三版)》的第四章，有详细介绍 MySQL 字段类型优化。

数值

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

FLOAT、DOUBLE、DECIMAL 存储金额的区别

  • 浮点数以 8 位精度存储在 FLOAT 中，有四个字节。
  • 浮点数以 18 位精度存储在 DOUBLE 中，有八个字节。
  • DECIMAL 存储其实是 string 类型存储。

浮点数计算都不准（只要不是 2 的整数倍都会有问题）。

个人意见：不建议使用 DECIMAL 类型存储金额，DECIMAL 是通过二进制实现的一种编码方式，计算效率不如整型。不如直接把一个数扩大 10 倍或者 100 倍。

字符串

CHAR、VARCHAR、BINARY、VARBINARY 存储字符的区别

  • CHAR 使用固定长度的空间进行存储，CHAR(M) 类型一般需要预先定义字符串长度。如果不指定(M)，则长度默认 1 个字符。
    CHAR 在存储时会在右边填充空格以达到指定的长度，检索时会去掉空格；
    CHAR(M) 和 VARCHAR(M) 的 M 都代表能够保存的字符数的最大值，无论是字母、数字还是中文，每个都只占用一个字符。

  • VARCHAR 保存可变长度的字符串，VARCHAR 定义时，必须指定长(M)，否则报错。
    VARCHAR 使用额外的一个或两个字节存储字符串长度，VARCHAR(10) 除了需要存储 10 个字符，还需要 1 个字节存储长度信息(10)，超过 255 的长度需要 2 个字节来存储。检索 VARCHAR 类型数据时会保留尾部空格。
    MySQL 5.0 版本以上，VARCHAR(20) 指的是 20 字符，无论存放的是数字、字母还是 UTF8 汉字（每个汉字3字节），都可以存放 20 个，最大大小是 65532 字节。

  • BINARY 保存二进制字符串，它保存的是字节而不是字符，没有字符集限制，BINARY(N) 和 VARBINARY(N) 中的 N 指的是字节长度，BINARY(8) 可以保存8个字节。

  • VARBINARY、BINARY 的区别和 CHAR、VARCHAR 类型是相似的。它保存的是字节而不是字符，没有字符集限制，BINARY(N) 和 VARBINARY(N) 中的 N 指的是字节长度。

使用原则：

  • CHAR 更适合存储长度较短或者长度都差不多的字符串，例如 Bcrypt 算法、MD5 算法加密后的密码、身份证号码。VARCHAR 类型适合存储长度不确定或者差异较大的字符串，例如用户昵称、文章标题等。
  • 对于经常改变的值，CHAR 优于 VARCHAR，原因是固定长度的行不容易产生碎片。
  • 对于很短的列，CHAR 优于 VARCHAR，原因是 VARCHAR 需要额外一个或两个字节存储字符串的长度。
  • 对于 InnoDB 表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，这个表头部分存放着指向各有关数据列的指针），而且主要影响性能的因素是因为数据行使用的存储总量，所以使用 CHAR 类型不见得会比使用 VARCHAR 类型好。
    事实上，因为 CHAR 类型通常要比 VARCHAR 类型占用更多的空间，所以从减少空间占用量和减少磁盘 I/O 的角度，使用 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 这个长度来进行的，也就会消耗更多内存。

DELETE、DROP、TRUNCATE 的区别

用法不同:

  • DROP(丢弃数据)：DROP TABLE 表名，直接将表都删除掉，在删除表的时候使用。
  • TRUNCATE(清空数据)：TRUNCATE TABLE 表名，只删除表中的数据，再插入数据的时候自增长 id 又从 1 开始，在清空表中数据的时候使用。
  • DELETE(删除数据)：DELETE FROM 表名 where 列名=值，删除某一行的数据，如果不加 where 子句和 TRUNCATE TABLE 表名 作用类似。

属于不同的数据库语言:

  • TRUNCATE 和 DELETE 只删除数据，不删除表结构，DROP 删除表结构，并且释放所占的空间。
  • DELETE 属于 DML 语言，需要事务管理，commit 之后才能生效。DROP 和 TRUNCATE 属于 DDL 语言，操作立刻生效，不可回滚。

执行速度不同:

  • 删除数据的速度：DROP > TRUNCATE > DELETE

  • DELETE 命令执行的时候会产生数据库的 binlog 日志，而日志记录是需要消耗时间的，但是也有个好处方便数据回滚恢复。

  • TRUNCATE 命令执行的时候不会产生数据库日志，因此比 DELETE 要快。除此之外，还会把表的自增值重置和索引恢复到初始大小等。

  • DROP 命令会把表占用的空间全部释放掉。

Tips：你应该更多地关注在使用场景上，而不是执行效率。

使用场合：

  • 当你不再需要该表时，用 DROP。
  • 当你仍要保留该表，但要删除所有记录时，用 TRUNCATE。
  • 当你要删除部分记录时（always with a where clause)，用 DELETE。

DML 语句和 DDL 语句区别：

  • DML（Data Manipulation Language）是数据库操作语言的缩写，是指对数据库中表记录的操作，主要包括表记录的插入、更新、删除和查询，是开发人员日常使用最频繁的操作。
  • DDL（Data Definition Language）是数据定义语言的缩写，简单来说，就是对数据库内部的对象进行创建、删除、修改的操作语言。它和 DML 语言的最大区别是 DML 只是对表内部数据的操作，而不涉及到表的定义、结构的修改，更不会涉及到其他对象。DDL 语句更多的被数据库管理员（DBA）所使用，一般的开发人员很少使用。

时间

DATETIME、TIMESTAMP 存储时间的区别

  • TIMESTAMP 把客户端插入的时间从当前时区转化为 UTC（世界标准时间）进行存储。查询时，将其又转化为客户端当前时区进行返回。对于跨时区的业务，TIMESTAMP 更为合适。
    TIMESTAMP 以时间戳格式存储，占用 4 个字节，范围小 1970-1-1 到 2038-1-19，显示依赖于所指定得时区， 默认在第一个列行的数据修改时可以自动得修改 TIMESTAMP 列的值。

  • 对于 DATETIME，不做任何改变，基本上是原样输入和输出。
    DATETIME 以 YYYY-MM-DD HH:MM:SS 格式存储时期时间，精确到秒，占用 8 个字节的存储空间，DATETIME 类型与时区无关。

  • 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

TIMESTAMP 的 CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP

TIMESTAMP 有两个属性，分别是 CURRENT_TIMESTAMP 和 ON UPDATE CURRENT_TIMESTAMP，

  1. CURRENT_TIMESTAMP：当要向数据库执行 INSERT 操作时，如果字段有 CURRENT_TIMESTAMP 属性，则无论这个字段有没有设置值都插入当前的系统时间。
  2. ON UPDATE CURRENT_TIMESTAMP：当执行 UPDATE 操作时，如果字段有 ON UPDATE CURRENT_TIMESTAMP 属性。则字段无论值有没有变化，它的值也会跟着更新为当前 UPDATE 操作时的系统时间。
1
2
3
CREATE TABLE `t_temp` (
    `upd_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '默认取当前时间',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

如何在 Unix 和 MySQL 时间戳之间进行转换

  • UNIX_TIMESTAMP 是从 MySQL 时间戳转换为 Unix 时间戳的命令。
  • FROM_UNIXTIME 是从 Unix 时间戳转换为 MySQL 时间戳的命令。

其他类型

NULL 和 ‘’ 的区别是什么

NULL 跟 ''(空字符串)是两个完全不一样的值，区别如下：

  • NULL 代表一个不确定的值，就算是两个 NULL，它俩也不一定相等。例如，SELECT NULL=NULL 的结果为 false，但是在我们使用 DISTINCT，GROUP BY，ORDER BY 时，NULL 又被认为是相等的。
  • ''的长度是 0，是不占用空间的，而 NULL 是需要占用空间的。
  • NULL 会影响聚合函数的结果。例如，SUM、AVG、MIN、MAX 等聚合函数会忽略 NULL 值。 COUNT 的处理方式取决于参数的类型。如果参数是 *(COUNT(*))，则会统计所有的记录数，包括 NULL 值；如果参数是某个字段名(COUNT(列名))，则会忽略 NULL 值，只统计非空值的个数。
  • 查询 NULL 值时，必须使用 IS NULL 或 IS NOT NULLl 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

看了上面的介绍之后，相信你对另外一个高频面试题：“为什么 MySQL 不建议使用 NULL 作为列默认值？”也有了答案。

Boolean 类型如何表示？

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

其他

HAVING 子句和 WHERE 的异同点

  • 语法上：WHERE 用表中列名，HAVING 用 SELECT 结果别名。
  • 影响结果范围：WHERE 从表读出数据的行数，HAVING 返回客户端的行数。
  • 索引：WHERE 可以使用索引，HAVING 不能使用索引，只能在临时结果集操作。
  • WHERE 不能使用聚集函数，HAVING 是专门使用聚集函数的。

SELECT cid,COUNT(id) nums FROM abc group by cid HAVING nums>2

FRANT 和 FLUSH PRIVILEGES 语句

  • FRANT 语句会同时修改数据表和内存，判断权限的时候使用的内存数据，因此，规范使用是不需要加上 FLUSH PRIVILEGES 语句。
  • FLUSH PRIVILEGES 语句本身会用数据表的数据重建一份内存权限数据，所以在权限数据可能存在不一致的情况下可以使用。

COUNT(*) 实现方式以及各种 COUNT 对比

首先要弄清楚 COUNT() 的语义。COUNT() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 COUNT 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。

所以，COUNT(*)、COUNT(1) 和 COUNT(主键 id) 都表示返回满足条件的结果集的总行数；而 COUNT(字段)，则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

至于分析性能差别的时候，记住这么几个原则：

  • server 层要什么就给什么；
  • InnoDB 只给必要的值；
  • 现在的优化器只优化了 COUNT(*) 的语义为“取行数”，其他“显而易见”的优化并没有做。

具体来说：

  • 对于 COUNT(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层，server 层拿到 id 后，判断 id 是不可能为空的，所以按行累加。

  • 对于 COUNT(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字 1 进去，1 也代表是不可能为空，所以也按行累加。
    单看这两个用法的差别的话，你能对比出来，COUNT(1) 执行得要比 COUNT(id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。count(1) 相比 count(主键字段) 少一个步骤，就是不需要读取记录中的字段值
    统计所有的行数，包括为 NULL 的行。

  • 对于 COUNT(字段) 来说，如果这个字段定义为 NOT NULL 的话，一行行地从记录里面读出这个字段，判断不可能为空，所以也按行累加；如果这个字段定义允许为 NULL，那么执行的时候，还要把值取出来再判断一下，不是 NULL 才累加。也就是前面的第一条原则，server 层要什么字段，InnoDB 就返回什么字段。

  • 但是 COUNT(*) 是例外，并不会把全部字段取出来，而是专门做了优化，不取值。优化器会找到最小的那棵树来遍历，在保证正确的逻辑前提下，尽量减少扫描数据量，是数据库系统设计的通用法则之一。
    统计所有的行数，包括为 NULL 的行。

    在不同的 MySQL 引擎中，COUNT(*) 有不同的实现方式。

    • MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 COUNT(*) 的时候会直接返回这个数，效率很高。
    • InnoDB 引擎执行 COUNT(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数，所以比较麻烦。

    上面说的两点泛指没有过滤条件的 COUNT(*)，如果加了 WHERE 条件的话，MyISAM 表也是不能返回得这么快的，而业务系统一般都是用 InnoDB 引擎，所以当记录数越来越多的时候，计算一个表的总行数就会越来越慢。

所以结论是：按照效率排序的话 COUNT(字段)<COUNT(非空字段)=COUNT(主键 id)<COUNT(1)≈COUNT(*)，所以建议尽量使用 COUNT(*)。

LIKE 声明中的 % 是什么意思

% 对应于 0 个或更多字符，只是 LIKE 语句中的一个字符。

列对比运算符是什么

在 SELECT 语句的列比较中使用 =，<>，<=，<，>=，>，<<，>>，<=>，AND，OR 或 LIKE 运算符。

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

TEXT 类型类似于 CHAR（0-255 字节）和 VARCHAR（0-65,535 字节），但可以存储更长的字符串，即长文本数据，例如博客内容。

类型 可存储大小 用途
TINYTEXT 0-255 字节 一般文本字符串
TEXT 0-65,535 字节 长文本字符串
MEDIUMTEXT 0-16,772,150 字节 较大文本数据
LONGTEXT 0-4,294,967,295 字节 极大文本数据

BLOB 类型主要用于存储二进制大对象，例如图片、音视频等文件。

类型 可存储大小 用途
TINYBLOB 0-255 字节 短文本二进制字符串
BLOB 0-65KB 二进制字符串
MEDIUMBLOB 0-16MB 二进制形式的长文本数据
LONGBLOB 0-4GB 二进制形式的极大文本数据

BLOB 和 TEXT 类型之间的唯一区别在于 对 BLOB 值进行排序和比较时区分大小写，对 TEXT 值不区分大小写。

在日常开发中，很少使用 TEXT 类型，但偶尔会用到，而 BLOB 类型则基本不常用。如果预期长度范围可以通过 VARCHAR 来满足，建议避免使用 TEXT。

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

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

MySQL 如何优化 DISTINCT

DISTINCT 在所有列上转换为 GROUP BY，并与 ORDER BY 子句结合使用。

SELECT DISTINCT t1.a FROM t1,t2 WHERE t1.a=t2.a;

NOW() 和 CURRENT_DATE() 有什么区别

  • NOW() 命令用于显示当前年份，月份，日期，小时，分钟和秒。
  • CURRENT_DATE() 仅显示当前年份，月份和日期。

在 MySQL 中 ENUM 的用法是什么

ENUM 是一个字符串对象，用于指定一组预定义的值，并可在创建表时使用。

Create TABLE size(name ENUM('Smail,'Medium','Large');

MySQL 查询是否区分大小写

不区分

1
2
3
SELECT VERSION(), CURRENT_DATE;
SELECT version(), current_date;
SELECT vErSiOn(), current_DATE;

所有这些例子都是一样的，MySQL 不区分大小写。

IN 和 EXIST 的区别

  • IN 先执行子查询，然后返回的数据与主查询匹配。
  • EXIST 先执行主查询，将主查询的结果放到子查询中进行验证。

自增 ID

列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况

它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。

为什么 MySQL 自增主键 ID 不连续

  1. 自增初始值和自增步长设置不为 1
  2. 唯一键冲突
  3. 事务回滚
  4. 批量插入（如 insert...select 语句）

深层次原因是：MySQL 不判断自增主键是否存在，从而减少加锁的时间范围和粒度，这样能保持更高的性能，确保自增主键不能回退，所以才有自增主键不连续。

自增主键怎么做到唯一性？自增值加 1 来通过自增锁控制并发。

InnoDB 为什么要用自增 ID 作为主键

自增主键的插入模式，符合递增插入，每次都是追加操作，不涉及挪动记录，也不会触发叶子节点的分裂。每次插入新的记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。

如果用业务逻辑的字段做主键，不容易保证有序插入，由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，写数据成本较高。

MySQL 有哪些自增 ID？各自场景是什么？

https://www.nowcoder.com/discuss/post/404273563073527808

  • 表的自增 ID：当自增主键的值，达到上限之后，再申请值不会变化，所以当我们再次插入数据的时候会出现主键冲突的错误。
  • row_id：我们在创建表的时候，如果不为表指定任何主键，那么MySQL会给这个表创建一个隐藏的自增ID主键 row_id，并且这个隐藏的自增ID的取值是从一个全局变量。row_id 达到上限之后，归 0 在重新递增，如果出现相同的 row_id 后写的数据会覆盖之前的数据。
  • Xid：Xid 是 binlog 文件中常见的一个ID，因为 binlog 是 server 层维护的日志，所以 Xid 也是由 MySQL 的 server 层维护的。它在 binlog 文件中标识一个唯一的事务。Xid 只需要不在同一个 binlog 文件出现重复值即可，理论上会出现重复值，但概率极小可忽略不计。
  • trx_id：InnoDB 的事务ID。和 Xid 不同，trx_id 是由 InnoDB 引擎自己维护的。它的最大值为 2 的 48 次方减 1。如果到达它的最大值之后，会从 0 开始累加。这个值在 MySQL 重启之后不会清零，它做了持久化的操作，所以重启后的 MySQL 事务 ID 是可以累积上一次的值的。
  • thread_id：是我们使用中最常见的，而且也是处理得最好的一个自增 ID 逻辑了。thread_id 的取值来自于系统保存的一个全局变量 thread_id_COUNTer，每新建一个连接，就将 thread_id_COUNTer 赋值给这个新连接的线程变量。
    它的大小是 4 个字节，最大值为：2 的 32 次方减 1，到达最大值之后，他会重新从 0 累加。但是它也不会重复，因为他们使用了唯一数组的设计理念。

Xid 在 MySQL 内部是怎么生成的呢

MySQL 内部维护了一个全局变量 global_query_id，每次执行语句（包括 SELECT 语句）的时候将它赋值给 Query_id，然后给这个变量加 1。如果当前语句是这个事务执行的第一条语句，那么 MySQL 还会同时把 Query_id 赋值给这个事务的 Xid。

而 global_query_id 是一个纯内存变量，重启之后就清零了。所以你就知道了，在同一个数据库实例中，不同事务的 Xid 也是有可能相同的。但是 MySQL 重启之后会重新生成新的 binlog 文件，这就保证了，同一个 binlog 文件里，Xid 一定是唯一的。

一张表，里面有 ID 自增主键，当 insert 了 17 条记录之后，删除了第 15,16,17 条记录，再把 MySQL 重启，再 insert 一条记录，这条记录的 ID 是 18 还是 15 ？

  1. 如果表的类型是 MyISAM，那么是 18。MyISAM 表会把自增主键的最大 ID 记录到数据文件里，重启 MySQL 自增主键的最大 ID 也不会丢失。

  2. 如果表的类型是 InnoDB，那么是 15。InnoDB 表只是把自增主键的最大 ID 记录到内存中，所以重启数据库或者是对表进行 OPTIMIZE 操作，都会导致最大 ID 丢失。

怎样才能找出最后一次插入时分配了哪个自动增量。

LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值，并且不需要指定表名称。

#MySQL #面试
02-MySQL 字段
https://flepeng.github.io/interview-41-数据库-41-MySQL-02-MySQL-字段/
作者
Lepeng
发布于
2020年8月8日
许可协议