MySQL/JDBC问题集锦（时区、NULL）

MySQL数据类型

数值类型

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

1. 如果jdbcType定义可以为空，则使用包装类；

2. 不可为空则使用 基本数据类型（但是如果可以为空，定义为基本类型，则会初始化为java里面的零值！！！）；

综上，最好是用包装类型；

datetime 零值转换问题

datetime 的零值 为 0000-00-00 00:00:00

参考 mysql 文档 【日期和时间数据类型】

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参数 zeroDateTimeBehavior

• 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，所以本地时间都是与某一时区相关的，脱离时区看本地时间，是没有意义的，因为你并不知道这具体是指的什么时间点。

1. Java中，java.util.Date和java.sql.Timestamp 都是绝对时间

2. 通过SimpleDateFormat格式化出来的yyyy-MM-dd HH:mm:ss形式的时间字符串，是本地时间

3. 如果 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开始，datetime的存储结构如下：

 ---------------------------
 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：

1. 在5.x版本，需要使用serverTimeZone=GMT%2B8；

2. 在8.x版本需要使用forceConnectionTimeZoneToSession=true&connectionTimeZone=GMT%2B8

3. 其他版本没用过，不知道serverTimeZone具体是5.x到8.x之间哪个版本修改的；

jvm时区

java有个 java.util.TimeZone 类，用于标识当前jvm时区；会影响SimpleDateFormat等和时区相关的类，默认都是取的TimeZone.getDefault()的值，TimeZone如果没有主动设置值，默认取的就是本机的时区

上面列举了java应用和mysql服务之间可能出现的timezone相关的东西。

结论

1. 如果是站在分析Java应用jdbc驱动使用mysql的基础上讨论时区问题可以先忽略 mysql的变量【time_zone和system_time_zone】

2. 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默认

不指定 serverTimeZone/connectionTimeZone，TimeZone设置为GMT+5

jdbc的select和execute时，datetime、timestamp都是和mysql server在交换yyyy-MM-dd HH:mm:ss字符串。

1. JDBC在发送请求之前结合Connection的Timezone转化java.sql.Timestamp对象为string；

2. jdbc不关心server端如何执行和存储（对mysql如何存储datetime和timestamp无感知），只知道下次JDBC读取时仍然是此时转化好的string。

3. 在收到查询回复时，也是结合Connection的Timezone转化string（一直和1中发送的String一致）为java.sql.Timestamp对象