JAVA MyBatis读取MySQL UNSIGNED ZEROFILL左侧零丢失修复笔记

项目中开发同学反映从MySQL中查询到的UNSIGNED ZEROFILL整型定义的字段值，前面自动补全的“0”都丢失了。

经研究从MySQL8.0.17版本开始移除了ZEROFILL修饰语。

新版的MyBatis也紧跟MySQL的release，不再支持对UNSIGNED ZEROFILL整型字段的有效查询。

As of MySQL 8.0.17, the ZEROFILL attribute is deprecated for numeric data types; you should expect support for it to be removed in a future version of MySQL. Consider using an alternative means of producing the effect of this attribute. For example, applications could use the LPAD() function to zero-pad numbers up to the desired width, or they could store the formatted numbers in CHAR columns.

解决方式参考MySQL官方文档的说明，个人认为有以下三种备选。

①数据库定义中移除UNSIGNED ZEROFILL修饰，然后查询时通过LPAD()函数手动实现“补零”

mysql> SELECT LPAD(1024, 8, 0);
+------------------+
| LPAD(1024, 8, 0) |
+------------------+
| 00001024         |
+------------------+
1 row in set (0.00 sec)

②通过CAST()函数手动将整型转换成字符型查询，然后再将结果返回JAVA

mysql> SELECT CAST('00001024' AS CHAR(8));
+-----------------------------+
| CAST('00001024' AS CHAR(8)) |
+-----------------------------+
| 00001024                    |
+-----------------------------+
1 row in set (0.00 sec)

③修改数据库定义中的UNSIGNED ZEROFILL字段为CHAR类型

 

 

参考：

https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_lpad

https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#function_cast