Mybatis数据库的类型与java类型对应

亦凉 2023-10-10 12:21 30阅读 0赞

## **Mybatis与数据库的类型对应** ##

--------------------

**1：Java数据类型与MySql数据类型对照表**

<table> 
 <tbody> 
 <tr> 
 <td>类型名称</td> 
 <td>显示长度</td> 
 <td>数据库类型</td> 
 <td>JAVA类型</td> 
 <td>JDBC类型索引(int)</td> 
 </tr> 
 <tr> 
 <td>VARCHAR</td> 
 <td>L+N</td> 
 <td>VARCHAR</td> 
 <td>java.lang.String</td> 
 <td>12</td> 
 </tr> 
 <tr> 
 <td>CHAR</td> 
 <td>N</td> 
 <td>CHAR</td> 
 <td>java.lang.String</td> 
 <td>1</td> 
 </tr> 
 <tr> 
 <td>BLOB</td> 
 <td>L+N</td> 
 <td>BLOB</td> 
 <td>java.lang.byte[]</td> 
 <td>-4</td> 
 </tr> 
 <tr> 
 <td>TEXT</td> 
 <td>65535</td> 
 <td>VARCHAR</td> 
 <td>java.lang.String</td> 
 <td>-1</td> 
 </tr> 
 <tr> 
 <td>INTEGER</td> 
 <td>4</td> 
 <td>INTEGER UNSIGNED</td> 
 <td>java.lang.Long</td> 
 <td>4</td> 
 </tr> 
 <tr> 
 <td>TINYINT</td> 
 <td>3</td> 
 <td>TINYINT UNSIGNED</td> 
 <td>java.lang.Integer</td> 
 <td>-6</td> 
 </tr> 
 <tr> 
 <td>SMALLINT</td> 
 <td>5</td> 
 <td>SMALLINT UNSIGNED</td> 
 <td>java.lang.Integer</td> 
 <td>5</td> 
 </tr> 
 <tr> 
 <td>MEDIUMINT</td> 
 <td>8</td> 
 <td>MEDIUMINT UNSIGNED</td> 
 <td>java.lang.Integer</td> 
 <td>4</td> 
 </tr> 
 <tr> 
 <td>BIT</td> 
 <td>1</td> 
 <td>BIT</td> 
 <td>java.lang.Boolean</td> 
 <td>-7</td> 
 </tr> 
 <tr> 
 <td>BIGINT</td> 
 <td>20</td> 
 <td>BIGINT UNSIGNED</td> 
 <td>java.math.BigInteger</td> 
 <td>-5</td> 
 </tr> 
 <tr> 
 <td>FLOAT</td> 
 <td>4+8</td> 
 <td>FLOAT</td> 
 <td>java.lang.Float</td> 
 <td>7</td> 
 </tr> 
 <tr> 
 <td>DOUBLE</td> 
 <td>22</td> 
 <td>DOUBLE</td> 
 <td>java.lang.Double</td> 
 <td>8</td> 
 </tr> 
 <tr> 
 <td>DECIMAL</td> 
 <td>11</td> 
 <td>DECIMAL</td> 
 <td>java.math.BigDecimal</td> 
 <td>3</td> 
 </tr> 
 <tr> 
 <td>BOOLEAN</td> 
 <td>1</td> 
 <td>同TINYINT</td> 
 <td>&nbsp;</td> 
 <td>&nbsp;</td> 
 </tr> 
 <tr> 
 <td>ID</td> 
 <td>11</td> 
 <td>PK (INTEGER UNSIGNED)</td> 
 <td>java.lang.Long</td> 
 <td>4</td> 
 </tr> 
 <tr> 
 <td>DATE</td> 
 <td>10</td> 
 <td>DATE</td> 
 <td>java.sql.Date</td> 
 <td>91</td> 
 </tr> 
 <tr> 
 <td>TIME</td> 
 <td>8</td> 
 <td>TIME</td> 
 <td>java.sql.Time</td> 
 <td>92</td> 
 </tr> 
 <tr> 
 <td>DATETIME</td> 
 <td>19</td> 
 <td>DATETIME</td> 
 <td>java.sql.Timestamp</td> 
 <td>93</td> 
 </tr> 
 <tr> 
 <td>TIMESTAMP</td> 
 <td>19</td> 
 <td>TIMESTAMP</td> 
 <td>java.sql.Timestamp</td> 
 <td>93</td> 
 </tr> 
 <tr> 
 <td>YEAR</td> 
 <td>4</td> 
 <td>YEAR</td> 
 <td>java.sql.Date</td> 
 <td>91 &nbsp;</td> 
 </tr> 
 </tbody> 
</table>

\*对于bolb，一般用于对图片的数据库存储，原理是把图片打成二进制，然后进行的一种存储方式，在java中对应byte［］数组。

对于boolen类型，在mysql数据库中，个人认为用int类型代替较好，对bit操作不是很方便，尤其是在具有web页面开发的项目中，表示0/1，对应java类型的Integer较好。

**2：SqlServer数据库与MySql数据类型对照表**

<table> 
 <tbody> 
 <tr> 
 <th>SQL Server 类型</th> 
 <th>JDBC 类型 (java.sql.Types)</th> 
 <th>Java 语言类型</th> 
 </tr> 
 <tr> 
 <td> bigint </td> 
 <td> BIGINT </td> 
 <td> long </td> 
 </tr> 
 <tr> 
 <td> timestamp binary </td> 
 <td> BINARY </td> 
 <td> byte[] </td> 
 </tr> 
 <tr> 
 <td> bit </td> 
 <td> BIT </td> 
 <td> boolean </td> 
 </tr> 
 <tr> 
 <td> char </td> 
 <td> CHAR </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> decimal money smallmoney </td> 
 <td> DECIMAL </td> 
 <td> java.math.BigDecimal </td> 
 </tr> 
 <tr> 
 <td> float </td> 
 <td> DOUBLE </td> 
 <td> double </td> 
 </tr> 
 <tr> 
 <td> int </td> 
 <td> INTEGER </td> 
 <td> int </td> 
 </tr> 
 <tr> 
 <td> image varbinary(max) </td> 
 <td> LONGVARBINARY </td> 
 <td> byte[] </td> 
 </tr> 
 <tr> 
 <td> varchar(max) text </td> 
 <td> LONGVARCHAR </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> nchar </td> 
 <td> CHAR NCHAR (Java SE 6.0) </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> nvarchar </td> 
 <td> VARCHAR NVARCHAR (Java SE 6.0) </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> nvarchar(max) ntext </td> 
 <td> LONGVARCHAR LONGNVARCHAR (Java SE 6.0) </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> numeric </td> 
 <td> NUMERIC </td> 
 <td> java.math.BigDecimal </td> 
 </tr> 
 <tr> 
 <td> real </td> 
 <td> REAL </td> 
 <td> float </td> 
 </tr> 
 <tr> 
 <td> smallint </td> 
 <td> SMALLINT </td> 
 <td> short </td> 
 </tr> 
 <tr> 
 <td> datetime smalldatetime </td> 
 <td> TIMESTAMP </td> 
 <td> java.sql.Timestamp </td> 
 </tr> 
 <tr> 
 <td> varbinary udt </td> 
 <td> VARBINARY </td> 
 <td> byte[] </td> 
 </tr> 
 <tr> 
 <td> varchar </td> 
 <td> VARCHAR </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> tinyint </td> 
 <td> TINYINT </td> 
 <td> short </td> 
 </tr> 
 <tr> 
 <td> uniqueidentifier </td> 
 <td> CHAR </td> 
 <td> String </td> 
 </tr> 
 <tr> 
 <td> xml </td> 
 <td> LONGVARCHAR SQLXML (Java SE 6.0) </td> 
 <td> String SQLXML </td> 
 </tr> 
 <tr> 
 <td> time </td> 
 <td> TIME (1) </td> 
 <td> java.sql.Time (1) </td> 
 </tr> 
 <tr> 
 <td> date </td> 
 <td> DATE </td> 
 <td> java.sql.Date </td> 
 </tr> 
 <tr> 
 <td> datetime2 </td> 
 <td> TIMESTAMP </td> 
 <td> java.sql.Timestamp </td> 
 </tr> 
 <tr> 
 <td> datetimeoffset (2) </td> 
 <td> microsoft.sql.Types.DATETIMEOFFSET </td> 
 <td> microsoft.sql.DateTimeOffset </td> 
 </tr> 
 </tbody> 
</table>

3:sqlserver和Oracle数据类型对应关系

A.SqlServer 2k转换为[Oracle][] 10g

<table> 
 <tbody> 
 <tr> 
 <td>列名</td> 
 <td>SqlServer数据类型</td> 
 <td>SqlServer长度</td> 
 <td>Oracle数据类型</td> 
 </tr> 
 <tr> 
 <td>column1</td> 
 <td>bigint</td> 
 <td>8</td> 
 <td>NUMBER（19）</td> 
 </tr> 
 <tr> 
 <td>column2</td> 
 <td>binary</td> 
 <td>50</td> 
 <td>RAW（50）</td> 
 </tr> 
 <tr> 
 <td>column3</td> 
 <td>bit</td> 
 <td>1</td> 
 <td>NUMBER（2）</td> 
 </tr> 
 <tr> 
 <td>column4</td> 
 <td>char</td> 
 <td>10</td> 
 <td>CHAR（10）</td> 
 </tr> 
 <tr> 
 <td>column5</td> 
 <td>datetime</td> 
 <td>8</td> 
 <td>DATE</td> 
 </tr> 
 <tr> 
 <td>column6</td> 
 <td>decimal</td> 
 <td>9</td> 
 <td>NUMBER（18）</td> 
 </tr> 
 <tr> 
 <td>column7</td> 
 <td>float</td> 
 <td>8</td> 
 <td>BINARY_DOUBLE</td> 
 </tr> 
 <tr> 
 <td>column8</td> 
 <td>image</td> 
 <td>16</td> 
 <td>BLOB</td> 
 </tr> 
 <tr> 
 <td>column9</td> 
 <td>int</td> 
 <td>4</td> 
 <td>NUMBER（10）</td> 
 </tr> 
 <tr> 
 <td>column10</td> 
 <td>money</td> 
 <td>8</td> 
 <td>NUMBER（19，4）</td> 
 </tr> 
 <tr> 
 <td>column11</td> 
 <td>nchar</td> 
 <td>10</td> 
 <td>NCHAR（10）</td> 
 </tr> 
 <tr> 
 <td>column12</td> 
 <td>ntext</td> 
 <td>16</td> 
 <td>NCLOB</td> 
 </tr> 
 <tr> 
 <td>column13</td> 
 <td>numeric</td> 
 <td>9</td> 
 <td>NUMBER（18）</td> 
 </tr> 
 <tr> 
 <td>column14</td> 
 <td>nvarchar</td> 
 <td>50</td> 
 <td>NVARCHAR2（50）</td> 
 </tr> 
 <tr> 
 <td>column15</td> 
 <td>real</td> 
 <td>4</td> 
 <td>BINARY_FLOAT</td> 
 </tr> 
 <tr> 
 <td>column16</td> 
 <td>smalldatetime</td> 
 <td>4</td> 
 <td>DATE</td> 
 </tr> 
 <tr> 
 <td>column17</td> 
 <td>smallint</td> 
 <td>2</td> 
 <td>NUMBER（5）</td> 
 </tr> 
 <tr> 
 <td>column18</td> 
 <td>smallmoney</td> 
 <td>4</td> 
 <td>NUMBER（10，4）</td> 
 </tr> 
 <tr> 
 <td>column19</td> 
 <td>sql_variant</td> 
 <td>&nbsp;</td> 
 <td>BLOB</td> 
 </tr> 
 <tr> 
 <td>column20</td> 
 <td>text</td> 
 <td>16</td> 
 <td>CLOB</td> 
 </tr> 
 <tr> 
 <td>column21</td> 
 <td>timestamp</td> 
 <td>8</td> 
 <td>RAW（8）</td> 
 </tr> 
 <tr> 
 <td>column22</td> 
 <td>tinyint</td> 
 <td>1</td> 
 <td>NUMBER（3）</td> 
 </tr> 
 <tr> 
 <td>column23</td> 
 <td>uniqueidentifier</td> 
 <td>16</td> 
 <td>BLOB</td> 
 </tr> 
 <tr> 
 <td>column24</td> 
 <td>varbinary</td> 
 <td>50</td> 
 <td>RAW（50）</td> 
 </tr> 
 <tr> 
 <td>column25</td> 
 <td>varchar</td> 
 <td>50</td> 
 <td>VARCHAR2（50） &nbsp;</td> 
 </tr> 
 </tbody> 
</table>

B.Oracle 10g 转换为SqlServer 2k

<table> 
 <tbody> 
 <tr> 
 <td>Oracle列名</td> 
 <td>Oracle数据类型</td> 
 <td>SqlServer列名</td> 
 <td>SqlServer数据类型</td> 
 <td>SqlServer数据长度</td> 
 </tr> 
 <tr> 
 <td>COLUMN1</td> 
 <td>BINARY_DOUBLE</td> 
 <td>COLUMN1</td> 
 <td>float</td> 
 <td>8</td> 
 </tr> 
 <tr> 
 <td>COLUMN2</td> 
 <td>BINARY_FLOAT</td> 
 <td>COLUMN2</td> 
 <td>real</td> 
 <td>4</td> 
 </tr> 
 <tr> 
 <td>COLUMN3</td> 
 <td>BLOB</td> 
 <td>COLUMN3</td> 
 <td>image</td> 
 <td>16</td> 
 </tr> 
 <tr> 
 <td>COLUMN4</td> 
 <td>CLOB</td> 
 <td>COLUMN4</td> 
 <td>ntext</td> 
 <td>16</td> 
 </tr> 
 <tr> 
 <td>COLUMN5</td> 
 <td>CHAR（10）</td> 
 <td>COLUMN5</td> 
 <td>nchar</td> 
 <td>10</td> 
 </tr> 
 <tr> 
 <td>COLUMN6</td> 
 <td>DATE</td> 
 <td>COLUMN6</td> 
 <td>datetime</td> 
 <td>8</td> 
 </tr> 
 <tr> 
 <td>COLUMN12</td> 
 <td>NUMBER</td> 
 <td>COLUMN12</td> 
 <td>numeric</td> 
 <td>13</td> 
 </tr> 
 <tr> 
 <td>COLUMN13</td> 
 <td>NVARCHAR2（10）</td> 
 <td>COLUMN13</td> 
 <td>nvarchar</td> 
 <td>10</td> 
 </tr> 
 <tr> 
 <td>COLUMN14</td> 
 <td>RAW（10）</td> 
 <td>COLUMN14</td> 
 <td>varbinary</td> 
 <td>10</td> 
 </tr> 
 <tr> 
 <td>COLUMN15</td> 
 <td>TIMESP（6）</td> 
 <td>COLUMN15</td> 
 <td>datetime</td> 
 <td>8</td> 
 </tr> 
 <tr> 
 <td>COLUMN16</td> 
 <td>TIMESP（6） WITH LOCAL TIME ZONE</td> 
 <td>COLUMN16</td> 
 <td>datetime</td> 
 <td>8</td> 
 </tr> 
 <tr> 
 <td>COLUMN17</td> 
 <td>TIMESP（6） WITH TIME ZONE</td> 
 <td>COLUMN17</td> 
 <td>datetime</td> 
 <td>8</td> 
 </tr> 
 <tr> 
 <td>COLUMN18</td> 
 <td>VARCHAR2（10）</td> 
 <td>COLUMN18</td> 
 <td>nvarchar</td> 
 <td>10</td> 
 </tr> 
 <tr> 
 <td>COLUMN7</td> 
 <td>INTERVAL DAY（2） TO SECOND（6）</td> 
 <td>COLUMN7</td> 
 <td>nvarchar</td> 
 <td>30</td> 
 </tr> 
 <tr> 
 <td>COLUMN8</td> 
 <td>INTERVAL YEAR（2） TO MONTH</td> 
 <td>COLUMN8</td> 
 <td>nvarchar</td> 
 <td>14</td> 
 </tr> 
 <tr> 
 <td>COLUMN9</td> 
 <td>LONG</td> 
 <td>COLUMN9</td> 
 <td>ntext</td> 
 <td>16</td> 
 </tr> 
 <tr> 
 <td>COLUMN10</td> 
 <td>LONG RAW</td> 
 <td>COLUMN10</td> 
 <td>image</td> 
 <td>16</td> 
 </tr> 
 <tr> 
 <td>COLUMN11</td> 
 <td>NCLOB</td> 
 <td>COLUMN11</td> 
 <td>ntext</td> 
 <td>16</td> 
 </tr> 
 </tbody> 
</table>

[Oracle]: http://lib.csdn.net/base/oracle