一、毫秒信息丢失的原因
mysql-connector识别服务端信息时,误判服务端不支持存储毫秒
public class NativeCapabilities implements ServerCapabilities {
public NativeCapabilities(NativePacketPayload initialHandshakePacket) {
try {
// 获取的版本信息是:5.5.5-10.3.6-MariaDB-1:10.3.6+maria~jessie-log
this.serverVersion = ServerVersion.parseVersion(initialHandshakePacket.readString(StringSelfDataType.STRING_TERM, "ASCII"));
......
......
this.serverHasFracSecsSupport = this.serverVersion.meetsMinimum(new ServerVersion(5, 6, 4));
} catch (Throwable t) {
}
}
}
于是在绑定时间类型的参数时,不写入毫秒信息
public abstract class AbstractQueryBindings<T extends BindValue> implements QueryBindings<T> {
@Override
public void setTimestamp(int parameterIndex, Timestamp x, MysqlType targetMysqlType) {
int fractLen = -1;
if (!this.session.getServerSession().getCapabilities().serverSupportsFracSecs() || !this.sendFractionalSeconds.getValue()) {
fractLen = 0;
} else if (this.columnDefinition != null && parameterIndex <= this.columnDefinition.getFields().length && parameterIndex >= 0) {
fractLen = this.columnDefinition.getFields()[parameterIndex].getDecimals();
}
setTimestamp(parameterIndex, x, null, fractLen, targetMysqlType);
}
}
二、使用mariadb官方客户端处理毫秒信息
1、使用正确的数据类型
数据表字段使用类型:datetime(3);
java程序使用类型:java.util.Date,或者它的子类:java.sql.Timestamp;
注意:不能是java.sql.Date
2、引入依赖
<dependency>
<groupId>org.mariadb.jdbc</groupId>
<artifactId>mariadb-java-client</artifactId>
<version>3.0.9</version>
</dependency>
3、修改jdbc地址
jdbc:mariadb://10.0.0.1:3306/mydb?serverTimezone=GMT%2B8&useUnicode=true&useSSL=false&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull