MySQL/JDBC问题集锦（时区、NULL）
数值类型
MySQL 支持所有标准 SQL 数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。
关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值，并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。
作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 Bytes | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 Bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 Bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 Bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 Bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 Bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 Bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性，将在后面描述。
| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | '-838:59:59'/'838:59:59' | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
| 类型 | 大小 | 用途 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。
MySQL/Java 类型映射
mysql任何类型都可以转为java的String
| jdbcType | javaType |
|---|---|
| BIT | java.lang.Boolean |
| BIT | byte[] |
| TINYINT | java.lang.Integer |
| SMALLINT [UNSIGNED] | java.lang.Integer |
| MEDIUMINT [UNSIGNED] | java.lang.Integer |
| INTEGER | java.lang.Integer |
| INTEGER [UNSIGNED] | java.lang.Long |
| BIGINT | java.lang.Long |
| BIGINT [UNSIGNED] | java.math.BigInteger |
| FLOAT | java.lang.Float |
| DOUBLE | java.lang.Double |
| DECIMAL | java.math.BigDecimal |
| DATE | java.sql.Date |
| DATETIME | java.sql.Timestamp |
| TIMESTAMP | java.sql.Timestamp |
| TIME | java.sql.Time |
| CHAR | java.lang.String |
| VARCHAR | java.lang.String |
| BINARY | byte[] |
| VARBINARY | byte[] |
| TINYBLOB | byte[] |
| VARCHAR | java.lang.String |
| BLOB | byte[] |
| VARCHAR | java.lang.String |
| MEDIUMBLOB | byte[] |
| VARCHAR | java.lang.String |
| LONGBLOB | byte[] |
| VARCHAR | java.lang.String |
| CHAR | java.lang.String |
| CHAR | java.lang.String |
-
如果jdbcType定义可以为空，则使用包装类；
-
不可为空则使用 基本数据类型（但是如果可以为空，定义为基本类型，则会初始化为java里面的零值！！！）；
综上，最好是用包装类型；
datetime 零值转换问题
datetime 的零值 为 0000-00-00 00:00:00
mysql5.7+如果无法手动设置 零值 时，请检查 sqlmode 变量，并去掉 NO_ZERO_IN_DATE, NO_ZERO_DATE 两个值
-- 查询
show variables like 'sql_mode';
-- 设置 可以 set global sqlmode = 设置全局所有session
set sql_mode=${删除NO_ZERO_IN_DATE, NO_ZERO_DATE过后的值}
mysql的零值在Java中没有对应的表示，无法转换会抛出以下异常
Exception in thread "main" java.sql.SQLException: Zero date value prohibited
Connector/J提供了一个jdbc连接url参数
-
exception (the default), which throws an SQLException with an SQLState of S1009.（默认行为）
-
convertToNull, which returns NULL instead of the date.
-
round, which rounds the date to the nearest closest value which is 0001-01-01.
在连接的时候，按照以下方式连接即可
jdbc:mysql://localhost/test_db?zeroDateTimeBehavior=convertToNull
datetime和timestamp时区问题
基本概念
时区
由于地域的限制，人们发明了时区的概念，用来适应人们在时间感受上的差异，比如中国的时区是东8区，表示为+8:00，或GMT+8，而日本的时区是东9区，表示为+9:00，或GMT+9，当中国是早上8点时，日本是早上9点，即东8区的8点与东9区的9点，这两个时间是相等的。
UTC(世界标准时间)
协调世界时，又称世界标准时间或世界协调时间，简称UTC（从英文“Coordinated Universal Time”／法文“Temps
Universel Coordonné”而来），是最主要的世界时间标准，其以原子时秒长为基础，在时刻上尽量接近于格林尼治标准时间。
GMT(格林尼治平时)
格林尼治平时（又称格林尼治平均时间或格林尼治标准时间，旧译格林威治标准时间；英语：Greenwich Mean
Time，GMT）是指位于英国伦敦郊区的皇家格林尼治天文台的标准时间，因为本初子午线被定义在通过那里的经线。
理论上来说，格林尼治标准时间的正午是指当太阳横穿格林尼治子午线时（也就是在格林尼治上空最高点时）的时间。由于地球在它的椭圆轨道里的运动速度不均匀，这个时刻可能与实际的太阳时有误差，最大误差达16分钟。
由于地球每天的自转是有些不规则的，而且正在缓慢减速，因此格林尼治时间已经不再被作为标准时间使用。现在的标准时间，是由原子钟报时的协调世界时（UTC）。
CST
-
Central Standard Time (USA) UT-6:00 美国标准时间
-
Central Standard Time (Australia) UT+9:30 澳大利亚标准时间
-
China Standard Time UT+8:00 中国标准时间
-
Cuba Standard Time UT-4:00 古巴标准时间
绝对时间
如unix时间缀，是1970-01-01 00:00:00开始到现在的秒数，如：1582416000，这种表示是绝对时间，不受时区影响（与时区无关），也叫纪元时epoch。
本地时间
相对于某一时区的时间，是本地时间，比如东8区的2020-02-23 08:00:00，是中国人的本地时间，而在此时，日本人的本地时间是2020-02-23 09:00:00，所以本地时间都是与某一时区相关的，脱离时区看本地时间，是没有意义的，因为你并不知道这具体是指的什么时间点。
-
Java中，java.util.Date和java.sql.Timestamp 都是绝对时间
-
通过SimpleDateFormat格式化出来的yyyy-MM-dd HH:mm:ss形式的时间字符串，是本地时间
-
如果 SimpleDateFormat 没有调用 setTimeZone() 显示指定时区，那么默认用的是 jvm 运行在的操作系统上的时区，我们日常工作的电脑的时区基本都是GMT+8或者Asia/Shanghai。
字段存储（MySQL）
-
datetime存储类似于时间字符串【yyyy-MM-dd HH:mm:ss】的形式
-
TIMESTAMP内部是以一个正整数来存储的，所以占用4字节，最小是0，转化为时间就是1970-01-01 00:00:00（UTC），而最大是2^31 – 1 转化为 UTC时间就是2038-01-19 03:14:07，如果是中国时区就是UTC+8 2038-01-19 11:14:07（TIMESTAMP会以UTC时区存储）
v5.6.4开始，
---------------------------
1 bit sign (1 = non-negative, 0= negative)
17 bits year*13+month (year 0-9999, month 0-12)
5 bits day (0-31)
5 bits hour (0-23)
6 bits minute (0-59)
6 bits second (0-59)
---------------------------
40 bits = 5 bytes
现在开始聊聊时区的问题
时区来源
mysql变量
mysql可以通过以下sql语句查询设置time_zone
show variables like '%time_zone%';
-- 当time_zone=system时，会使用到system_time_zone
-- 切记，当time_zone=system，system_time_zone=cst时，一定要手动设置time_zone，否则可能在不同的地区导致cst代表了不同的时区。详情见👆提到了的cst对应的四个时区
查询结果如下：
| Variable_name | Value |
|---|---|
| system_time_zone | UTC |
| time_zone | SYSTEM |
用途：通过 mysql shell执行sql语句时，当前shell具备一个session上下文。当前上下文使用的就是 mysql的 time_zone变量。如果这时time_zone=system，就会取 systemn_time_zone的值
jdbc连接url参数
在通过jdbc驱动连接mysql进行操作时，需要指明URL，如 jdbc:mysql://localhost:3306/TestDb?characterEncoding=utf8
形如characterEncoding为连接参数，和http get参数类似
会有一个参数用于标识当前连接的时区，+需要转义为%2B：
-
在5.x版本，需要使用serverTimeZone=GMT%2B8；
-
在8.x版本需要使用forceConnectionTimeZoneToSession=true&connectionTimeZone=GMT%2B8
-
其他版本没用过，不知道serverTimeZone具体是5.x到8.x之间哪个版本修改的；
jvm时区
java有个 java.util.TimeZone 类，用于标识当前jvm时区；会影响SimpleDateFormat等和时区相关的类，默认都是取的TimeZone.getDefault()的值，TimeZone如果没有主动设置值，默认取的就是本机的时区
上面列举了java应用和mysql服务之间可能出现的timezone相关的东西。
结论
-
如果是站在分析Java应用jdbc驱动使用mysql的基础上讨论时区问题可以先忽略 mysql的变量【time_zone和system_time_zone】
-
serverTimeZone/connectionTimeZone有值的情况下优先使用自定义的值，没有指定的情况下，会取java.util.TimeZone.getDefault()的值
案例分析
mysql的time_zone变量以及mysql shell操作分析时区影响
mysql变量====> time_zone=utc
mysql已有表结构===>
CREATE TABLE `accounts`
(
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
`username` varchar(16) NOT NULL COMMENT '用户名',
`ts` timestamp NOT NULL COMMENT '时间戳',
`dt` datetime NOT NULL COMMENT '创建时间'
PRIMARY KEY (`id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8mb4 COMMENT ='测试表';
shell 1 ====>
-- 因为会话默认时区为utc，手动设置当前会话为GMT+1
set time_zone = "+1:00"
show variables like '%time_zone%';
-- 时区查询结果如下
| Variable_name | Value |
|---|---|
| system_time_zone | UTC |
| time_zone | +01:00 |
insert into `accounts`(username,ts,dt) values("u1","2022-04-17 14:47:19","2022-04-17 14:47:19");
select *from accounts;
-- 查询结果如下，和插入时一模一样
| id | username | ts | dt |
|---|---|---|---|
| 1 | u1 | 2022-04-17 14:47:19 | 2022-04-17 14:47:19 |
shell 2 ====>
show variables like '%time_zone%';
-- 时区查询结果如下
| Variable_name | Value |
|---|---|
| system_time_zone | UTC |
| time_zone | SYSTEM |
select *from accounts;
-- 查询结果如下，会发现ts减少了1小时，dt没变。
| id | username | ts | dt |
|---|---|---|---|
| 1 | u1 | 2022-04-17 13:47:19 | 2022-04-17 14:47:19 |
shell 3====>
-- 因为会话默认时区为utc，手动设置当前会话为GMT+2
set time_zone = "+2:00"
show variables like '%time_zone%';
-- 时区查询结果如下
| Variable_name | Value |
|---|---|
| system_time_zone | UTC |
| time_zone | +02:00 |
select *from accounts;
-- 查询结果如下，会发现ts增加了1小时，dt没变。
| id | username | ts | dt |
|---|---|---|---|
| 1 | u1 | 2022-04-17 15:47:19 | 2022-04-17 14:47:19 |
结论： timestamp 存储的时候，会把本地时间（yyyy-MM-dd HH:mm:ss） 根据当前session的timezone转为utc时区对应的绝对时间（unix时间戳）进行存储；shell1的ts在存储时，因为是GMT+1，所以会先转为 2022-04-17 13:47:19（UTC）再和1970-01-01 00:00:00做差值计算秒数，所有针对上面三个会话在查询的时候，得到的都是同一个timestamp值，只是mysql根据每个session的timezone进行了本地时间格式化（yyyy-MM-dd HH:mm:ss ），所有才会出现1小时的时差；
datetime存储时，是什么年月日时分秒，mysql就存什么，查询的时候再按照对应的值返回即可。所有没有受到session的timezone影响；
jdbc驱动连接时区的影响（先上图，结论后续再加上）
指定 serverTimeZone/connectionTimeZone=UTC, TimeZone默认
指定 serverTimeZone/connectionTimeZone=UTC, TimeZone设置为GMT+5
不指定 serverTimeZone/connectionTimeZone，TimeZone默认
jdbc的select和execute时，datetime、timestamp都是和mysql server在交换yyyy-MM-dd HH:mm:ss字符串。
-
JDBC在发送请求之前结合Connection的Timezone转化java.sql.Timestamp对象为string；
-
jdbc不关心server端如何执行和存储（对mysql如何存储datetime和timestamp无感知），只知道下次JDBC读取时仍然是此时转化好的string。
-
在收到查询回复时，也是结合Connection的Timezone转化string（一直和1中发送的String一致）为java.sql.Timestamp对象
