MySQL字段类型到JAVA类型的对应及转换

曾经终败给现在 2022-06-07 14:16 1314阅读 0赞

# 1. 概述 #

在使用Java JDBC时，你是否有过这样的疑问：MySQL里的数据类型到底该选择哪种Java类型与之对应？本篇将为你揭开这个答案。

# 2. 类型映射 #

java.sql.Types定义了常用数据库(MySQL、Oracle、DB2等)所用到的数据类型，官名也叫JDBC类型。每个数据库产品的数据类型定义各不相同，但都有JDBC类型与之对应，如MySQL的BIGINT-->JDBC的BIGINT。

而每个JDBC类型，都有默认的Java类型与之对应，即ResultSet.getObject()返回Object的具体类型，如JDBC的BIGINT-->Java的java.lang.Long；JDBC的BIGINT UNSIGNED-->Java的 java.math.BigInteger。

我们可以通过ResultSet.getMetaData().getColumnTypeName(columnIndex)获取字段的JDBC类型，通过ResultSet.getMetaData().getColumnClassName(columnIndex)获取字段的Java类型。

下图展示了MySQL类型类型、JDBC类型、Java类型的映射关系（源自：[5.3 Java, JDBC and MySQL Types][5.3 Java_ JDBC and MySQL Types]）。

<table style="margin:0px; padding:0px; border-collapse:collapse; border-spacing:0px; border:1px solid silver; word-break:break-word; color:rgb(0,0,0); font-family:Verdana,Arial,Helvetica,sans-serif; font-size:14px"> 
 <thead style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px; text-align:center"> <span style="margin:0px; padding:0px">MySQL数据类型</span></td> 
   <th style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> JDBC类型(getColumnTypeName)</th> 
   <th style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> 默认返回的Java类型(getColumnClassName)</th> 
  </tr> 
 </thead> 
 <tbody style="margin:0px; padding:0px"> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">BIT(1)</code>&nbsp;(new in MySQL-5.0)</td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">BIT</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.Boolean</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">BIT( &gt; 1)</code>&nbsp;(new in MySQL-5.0)</td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">BIT</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">byte[]</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">TINYINT</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">TINYINT</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.Boolean</code>&nbsp;if the configuration property&nbsp;<code style="margin:0px; padding:0px">tinyInt1isBit</code>&nbsp;is set to&nbsp;<code style="margin:0px; padding:0px">true</code>&nbsp;(the default) and the storage size is 1, or&nbsp;<code style="margin:0px; padding:0px">java.lang.Integer</code>&nbsp;if not.</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">BOOL</code>,&nbsp;<code style="margin:0px; padding:0px">BOOLEAN</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">TINYINT</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> See&nbsp;<code style="margin:0px; padding:0px">TINYINT</code>, above as these are aliases for&nbsp;<code style="margin:0px; padding:0px">TINYINT(1)</code>, currently.</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">SMALLINT[(M)] [UNSIGNED]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">SMALLINT [UNSIGNED]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.Integer</code>&nbsp;(regardless of whether it is&nbsp;<code style="margin:0px; padding:0px">UNSIGNED</code>&nbsp;or not)</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">MEDIUMINT[(M)] [UNSIGNED]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">MEDIUMINT [UNSIGNED]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.Integer</code>&nbsp;(regardless of whether it is&nbsp;<code style="margin:0px; padding:0px">UNSIGNED</code>&nbsp;or not)</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">INT,INTEGER[(M)] [UNSIGNED]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">INTEGER [UNSIGNED]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.Integer</code>, if&nbsp;<code style="margin:0px; padding:0px">UNSIGNED</code>&nbsp;<code style="margin:0px; padding:0px">java.lang.Long</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">BIGINT[(M)] [UNSIGNED]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">BIGINT [UNSIGNED]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.Long</code>, if UNSIGNED&nbsp;<code style="margin:0px; padding:0px">java.math.BigInteger</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">FLOAT[(M,D)]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">FLOAT</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.Float</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">DOUBLE[(M,B)]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">DOUBLE</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.Double</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">DECIMAL[(M[,D])]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">DECIMAL</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.math.BigDecimal</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">DATE</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">DATE</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.sql.Date</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">DATETIME</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">DATETIME</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.sql.Timestamp</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">TIMESTAMP[(M)]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">TIMESTAMP</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.sql.Timestamp</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">TIME</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">TIME</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.sql.Time</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">YEAR[(2|4)]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">YEAR</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> If&nbsp;<code style="margin:0px; padding:0px">yearIsDateType</code>&nbsp;configuration property is set to&nbsp;<code style="margin:0px; padding:0px">false</code>, then the returned object type is&nbsp;<code style="margin:0px; padding:0px">java.sql.Short</code>. If set to&nbsp;<code style="margin:0px; padding:0px">true</code>&nbsp;(the default), then the returned object is of type&nbsp;<code style="margin:0px; padding:0px">java.sql.Date</code>with the date set to January 1st, at midnight.</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">CHAR(M)</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">CHAR</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.String</code>&nbsp;(unless the character set for the column is&nbsp;<code style="margin:0px; padding:0px">BINARY</code>, then&nbsp;<code style="margin:0px; padding:0px">byte[]</code>&nbsp;is returned.</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">VARCHAR(M) [BINARY]</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">VARCHAR</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.String</code>&nbsp;(unless the character set for the column is&nbsp;<code style="margin:0px; padding:0px">BINARY</code>, then&nbsp;<code style="margin:0px; padding:0px">byte[]</code>&nbsp;is returned.</td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">BINARY(M)</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">BINARY</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">byte[]</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">VARBINARY(M)</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">VARBINARY</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">byte[]</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">TINYBLOB</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">TINYBLOB</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">byte[]</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">TINYTEXT</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">VARCHAR</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.String</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">BLOB</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">BLOB</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">byte[]</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">TEXT</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">VARCHAR</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.String</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">MEDIUMBLOB</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">MEDIUMBLOB</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">byte[]</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">MEDIUMTEXT</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">VARCHAR</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.String</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">LONGBLOB</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">LONGBLOB</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">byte[]</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">LONGTEXT</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">VARCHAR</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.String</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">ENUM('value1','value2',...)</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">CHAR</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.String</code></td> 
  </tr> 
  <tr style="margin:0px; padding:0px"> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse; width:237px"> <code style="margin:0px; padding:0px">SET('value1','value2',...)</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">CHAR</code></td> 
   <td style="margin:0px; padding:3px; border:1px solid silver; border-collapse:collapse"> <code style="margin:0px; padding:0px">java.lang.String</code></td> 
  </tr> 
 </tbody> 
</table>

# 3. 类型转换 #

上面我们看到MySQL的BIGINT默认转为Java的java.lang.Long，那是不是就不能转为String或其他数值类型了？答案是否定的！MySQL的JDBC(Connector/J)在字段类型与Java类型之间的转换是比较灵活的。一般来说，任何字段类型都可以转换为java.lang.String，任何数值字段类型都可以转换为Java的数据类型（当然会出现四舍五入、溢出、精度丢失的问题）。

下图展示了MySQL JDBC允许的跨类型相互转换。

#  4. 总结 #

MySQL JDBC对每种字段类型，都有相应的Java类型与之对应，也可以转换为其他Java类型。但这种转换还不够灵活，如TIMESTAMP与java.util.Date就转换不了，只能人工转换。这里推荐使用MyBatis，它内置了许多TypeHander，支持各种基础数据类型间的转换（xxxTypeHandler），也支持自定义数据类型转换。

![721070-20161227171030398-37839074.png][]

[5.3 Java_ JDBC and MySQL Types]: http://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html
[721070-20161227171030398-37839074.png]: /images/20220607/f130177f70c34889aaa5c816911b2ce0.png