自增主键

自增主键（Auto-Increment Primary Key）是一种数据库特性，它允许数据库在插入新记录时自动为主键字段生成唯一的递增值。这简化了应用程序的开发，并确保了表中每一行都有唯一的标识符。避免了随机查询，从而提高了查询效率。

自增主键的存储

在 MySQL 5.7 及之前的版本中，InnoDB 引擎将自增值存储在内存中。这意味着如果 MySQL 服务器重启，自增值会丢失。重启后，InnoDB 会扫描表，找到自增列的最大值，然后加上步长（通常为 1）作为新的自增起始值。这可能导致在高并发插入场景下出现问题。

从 MySQL 8.0 开始，InnoDB 引擎对自增值的处理方式进行了改进。现在，自增值的变动会记录在 redo log 中。这样，即使服务器重启，InnoDB 也可以通过 redo log 恢复正确的自增值，从而避免了数据丢失和潜在的并发问题。

自增主键不连续的场景

• 手动插入指定的主键值。

• 插入语句报错，或者插入事务回滚。

之所以这么设计，是保证在并发申请自增id时，保证自增id严格的顺序递增，不至于出现自增id冲突的情况。

• 批量插入数据的语句时，会触发批量申请自增 id 的策略

注意，这里说的批量插入数据，不是在普通的 insert 语句里面包含多个 value 值！！！，因为这类语句在申请自增 id 的时候，是可以精确计算出需要多少个 id 的，然后一次性申请，申请完成后锁就可以释放了。

而对于 insert … select、replace …… select 和 load data 这种类型的语句来说，MySQL 并不知道到底需要申请多少 id，所以就采用了这种批量申请的策略，毕竟一个一个申请的话实在太慢了。

• 语句执行过程中，第一次申请自增 id，会分配 1 个；
• 1 个用完以后，这个语句第二次申请自增 id，会分配 2 个；
• 2 个用完以后，还是这个语句，第三次申请自增 id，会分配 4 个；
• 依此类推，同一个语句去申请自增 id，每次申请到的自增 id 个数都是上一次的两倍。

日期类型

Datetime 和 Timestamp 是数据库中用于存储日期和时间的两种常见数据类型，它们之间有一些重要的区别。理解这些区别有助于你根据实际需求选择合适的数据类型。

Datetime

• 存储格式： Datetime 类型以 YYYY-MM-DD HH:MM:SS 的格式存储日期和时间。例如：2024-07-20 10:30:00。
• 存储范围： Datetime 类型的存储范围通常比较大，例如 MySQL 中，Datetime 的范围是 1000-01-01 00:00:00 到 9999-12-31 23:59:59。
• 时区： Datetime 类型不存储时区信息。它存储的是一个字面上的日期和时间值。这意味着如果你在不同的时区读取同一个 Datetime 值，它会显示相同的日期和时间，而不会根据时区进行调整。
• 存储空间： 通常需要 8 个字节的存储空间。

Timestamp

• 存储格式： Timestamp 类型存储的是从 Unix 纪元（1970 年 1 月 1 日 00:00:00 UTC）开始的秒数。
• 存储范围： Timestamp 类型的存储范围通常比 Datetime 小，因为它受限于 Unix 时间戳的表示范围。例如 MySQL 中，Timestamp 的范围是 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC。（这个问题被称为“2038 年问题”，一些新的实现已经解决了这个问题，扩大了 Timestamp 的范围）。
• 时区： Timestamp 类型会隐式地存储时区信息。它会将存储的时间值转换为 UTC 时间进行存储，然后在读取时根据服务器的时区设置进行转换。这意味着如果你在不同的时区读取同一个 Timestamp 值，它会根据时区进行调整，显示当地时间。
• 存储空间： 通常需要 4 个字节的存储空间（在一些数据库中也可能是 8 个字节）。

Datetime 和 Timestamp 的主要区别总结

举例说明，建表 SQL 语句：

CREATE TABLE `time_zone_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `date_time` datetime DEFAULT NULL,
  `time_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入数据：

INSERT INTO time_zone_test(date_time,time_stamp) VALUES(NOW(),NOW());

查看数据：

select date_time,time_stamp from time_zone_test;

结果：

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2023-01-21 19:53:32 | 2023-01-21 19:53:32 |
+---------------------+---------------------+

现在我们修改当前会话的时区:

set time_zone='+8:00';

再次查看数据： date_time 不变，time_stamp自动进行了时区转换。

+---------------------+---------------------+
| date_time           | time_stamp          |
+---------------------+---------------------+
| 2023-01-21 19:53:32 | 2023-01-22 03:53:32 |
+---------------------+---------------------+

扩展：一些关于 MySQL 时区设置的一个常用 sql 命令

# 查看当前会话时区
SELECT @@session.time_zone;
# 设置当前会话时区
SET time_zone = 'Europe/Helsinki';
SET time_zone = "+00:00";
# 数据库全局时区设置
SELECT @@global.time_zone;
# 设置全局时区
SET GLOBAL time_zone = '+8:00';
SET GLOBAL time_zone = 'Europe/Helsinki';

隐式类型转换

MySQL 的隐式类型转换指的是在进行运算或比较时，如果操作数或比较数的类型不一致，MySQL 会自动将其中一个或多个操作数转换为另一种类型，以便进行运算或比较。这个转换过程是自动发生的，不需要我们显式地使用 CAST() 或 CONVERT() 函数。

以下是一些常见的隐式类型转换规则：

• 数值类型：
  • 整数和浮点数进行运算时，整数会被转换为浮点数。例如：1 + 1.5 的结果是 2.5。
  • 较小的整数类型（如 INT）可以转换为较大的整数类型（如 BIGINT）。
• 字符串类型：
  • 字符串和数值进行比较时，如果字符串可以被解析为数值，则会将字符串转换为数值。例如：'123' = 123 的结果是 TRUE。
  • 在算术运算中，如果字符串无法完全转换为数值，则会尝试转换为数值，直到遇到第一个非数字字符为止。例如：'123abc' + 1 的结果是 124。
  • 1. 不以数字开头的字符串都将转换为0。如'abc'、'a123bc'、'abc123'都会转化为0；
• 日期和时间类型：
  • 日期和时间类型可以转换为字符串类型。
• 布尔类型：
  • 在数值上下文中，TRUE 被转换为 1，FALSE 被转换为 0。

以上隐式类型转换要特别留意，以免出现索引失效的情况。比如索引 a 是字符类型，查询条件是 a=100 ，就会索引失效。正确的查询条件应该是 a= '100' 。

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 来判断，而不能使用 =、!=、 <、> 之类的比较运算符。而''是可以使用这些比较运算符的。

根据上面所述，所以建议不要使用NULL来作为字段的默认值。