数据库事务，JDBC操作和数据类型

目录

1 数据库事务

1.1 事务特性

事务的特性：

  1. 原子性（atomicity）：事务是数据库的逻辑工作单位，而且是必须是原子工作单位，对于其数据修改，要么全部执行，要么全部不执行。
  2. 一致性（consistency）：事务在完成时，必须是所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。
    事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据的完整性保持稳定
  3. 隔离性（isolation）：一个事务的执行不能被其他事务所影响。
  4. 持久性（durability）：一个事务一旦提交，事物的操作便永久性的保存在DB中。即使此时再执行回滚操作也不能撤消所做的更改

点击了解MySQL是如何通过日志保证ACID特性的

事务(Transaction):是并发控制的单元，是用户定义的一个操作序列。这些操作要么都做，要么都不做，是一个不可分割的工作单位。通过事务，能将逻辑相关的一组操作绑定在一起，以便服务器 保持数据的完整性。事务通常是以begin transaction开始，以commit或rollback结束。Commint表示提交，即提交事务的所有操作。具体地说就是将事务中所有对数据的更新写回到磁盘上的物理数据库中去，事务正常结束。Rollback表示回滚，即在事务运行的过程中发生了某种故障，事务不能继续进行，系统将事务中对数据库的所有已完成的操作全部撤消，滚回到事务开始的状态。

  • 自动提交事务：每条单独的语句都是一个事务。每个语句后都隐含一个commit （默认）
  • 显式事务：以begin transaction显示开始，以commit或rollback结束。
  • 隐式事务：当连接以隐式事务模式进行操作时，数据库引擎实例将在提交或回滚当前事务后自动启动新事务。无须描述事物的开始，只需提交或回滚每个事务。但每个事务仍以commit或rollback显式结束。
    连接将隐性事务模式设置为打开之后，当数据库引擎实例首次执行下列任何语句时，都会自动启动一个隐式事务：alter table，insert，create，open ，delete，revoke ，drop，select， fetch ，truncate table，grant，update在发出commit或rollback;语句之前，该事务将一直保持有效。在第一个事务被提交或回滚之后，下次当连接执行以上任何语句时，数据库引擎实例都将自动启动一个新事务。该实例将不断地生成隐性事务链，直到隐性事务模式关闭为止。

1.2 事务并发问题

并发问题可归纳为以下几类:

  1. 丢失更新：撤销一个事务时，把其他事务已提交的更新数据覆盖（A和B事务并发执行，A事务执行更新后，提交；B事务在A事务更新后，B事务结束前也做了对该行数据的更新操作，然后回滚，则两次更新操作都丢失了）
  2. 脏读：一个事务读到另一个事务未提交的更新数据（A和B事务并发执行，B事务执行更新后，A事务查询B事务没有提交的数据，B事务回滚，则A事务得到的数据不是数据库中的真实数据。也就是脏数据，即和数据库中不一致的数据）
  3. 不可重复读：一个事务读到另一个事务已提交的更新数据（A和B事务并发执行，A事务查询数据，然后B事务更新该数据，A再次查询该数据时，发现该数据变化了）
  4. 覆盖更新：这是不可重复读中的特例，一个事务覆盖另一个事务已提交的更新数据（即A事务更新数据，然后B事务更新该数据，A事务查询发现自己更新的数据变了）
  5. 虚读(幻读)：一个事务读到另一个事务已提交的新插入的数据（A和B事务并发执行，A事务查询数据，B事务插入或者删除数据，A事务再次查询发现结果集中有以前没有的数据或者以前有的数据消失了）

1.3 四种事务隔离级别

数据库系统提供了四种事务隔离级别供用户选择：

  1. ISOLATION_DEFAULT（默认隔离级别 ）：这是一个PlatfromTransactionManager默认的隔离级别，使用数据库默认的事务隔离级别，
    oracle默认的是：READ_COMMITTED ，mysql默认的是：REPEATABLE_READ
  2. Read Uncommitted（读未提交数据）：一个事务在执行过程中可以看到其他事务没有提交的新插入的记录，而且能看到其他事务没有提交的对已有记录的更新
    这种隔离级别会产生脏读，不可重复读和幻像读
  3. Read Commited（读已提交数据）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且能看到其他事务已经提交的对已有记录的更新
    这种事务隔离级别可以避免脏读出现，但是可能会出现不可重复读和幻像读
  4. Repeatable Read（可重复读）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他其他事务对已有记录的更新
    这种事务隔离级别可以防止脏读，不可重复读。但是可能出现幻像读
  5. Serializable（串行化）：一个事务在执行过程中完全看不到其他事务对数据库所做的更新（事务执行的时候不允许别的事务并发执行。事务串行化执行，事务只能一个接着一个地执行，而不能并发执行）
    除了防止脏读，不可重复读外，还避免了幻像读
丢失更新 脏读 非重复读 覆盖更新 幻像读
未提交读 Y Y Y Y Y
已提交读 N N Y Y Y
可重复读 N N N N Y
串行化 N N N N N

1.4 保存点提交和回滚操作

保存点(SavePoint)，JDBC定义了SavePoint接口，提供在一个更细粒度的事务控制机制。当设置了一个保存点后，可以rollback到该保存点处的状态，而不是rollback整个事务。
Connection接口的setSavepoint和releaseSavepoint方法可以设置和释放保存点。
JDBC规范虽然定义了事务的以上支持行为，但是各个JDBC驱动，数据库厂商对事务的支持程度可能各不相同。如果在程序中任意设置，可能得不到想要的效果。为此，JDBC提供了DatabaseMetaData接口，提供了一系列JDBC特性支持情况的获取方法。比如，通过DatabaseMetaData.supportsTransactionIsolationLevel方法可以判断对事务隔离级别的支持情况，通过DatabaseMetaData.supportsSavepoints方法可以判断对保存点的支持情况

rollback 和 commit的作用都完成对数据库的一次操作，并且释放当前的一些资源， 对于commit之后的事务,是不能够再进行回滚 ，但是对于回滚之后的失误，在不同的情况下却可以选择提交，rollback()和rollback(Savepoint)的区别也就在于此
这两方面方法会抛出SQLException,如果该事务为自动提交，即:
connection.setAutoCommint(true),或者是默认设置.

参看以下代码:

private String sql = "insert into t_transaction_test (name,value)  values(?,?)";
      pstm = conn.prepareStatement(sql);
        
        pstm.setString(1, "test9");
        pstm.setString(2, "test9");
        pstm.execute();
        Savepoint sp = conn.setSavepoint();
        
        pstm.setString(1, "test10");
        pstm.setString(2, "test10");
        pstm.execute();
        Savepoint sp2 = conn.setSavepoint();
  
        conn.rollback();
        conn.commit();
        System.out.println("Insert OK " + sp.getSavepointId());

在这段代码中,程序已经被rollback，因此接下来的commit()是没有任何意义的,但是如果将程序改为:

conn.rollback();
conn.commit(sp);

那么接下来的commit()将是有意义的，数据库中会记录第一条数据

2 JDBC操作

2.1 使用Batch批量处理数据库

当需要向数据库发送一批SQL语句执行时，应避免向数据库一条条的发送执行，而应采用JDBC的批处理机制，以提升执行效率

2.1.1 Statement批处理

使用Statement批处理

Statement.addBatch(sql) list 执行批处理SQL语句
executeBatch()方法：执行批处理命令
clearBatch()方法：清除批处理命令

Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JDBCManager.getConnection();
String sql1 = "insert into user(name,password,email,birthday) 
values('kkk','123','abc@sina.com','1978-08-08')";
String sql2 = "update user set pw='123456' where id=3";
st = conn.createStatement();
st.addBatch(sql1);  //把SQL语句加入到批命令中
st.addBatch(sql2);  //把SQL语句加入到批命令中
st.executeBatch();
} finally{
JDBCManager.DBclose(con,st,rs);
}

采用Statement.addBatch(sql)方式实现批处理：
优点：可以向数据库发送多条不同的SQL语句。
缺点：SQL语句没有预编译。
当向数据库发送多条语句相同，但仅参数不同的SQL语句时，需重复写上很多条SQL语句。例如：

Insert into user(name,password) values(‘aa’,’111’);
Insert into user(name,password) values(‘bb’,’222’);
Insert into user(name,password) values(‘cc’,’333’);
Insert into user(name,password) values(‘dd’,’444’);

2.1.2 PreparedStatement批处理

PreparedStatement批处理

PreparedStatement.addBatch();

conn = JDBCManager.getConnection();//获取工具；
String sql = "insert into user(name,password,email,birthday) values(?,?,?,?)";
st = conn.prepareStatement(sql);//预处理sql语句；
for(int i=0;i<50000;i++){
st.setString(1, "aaa" + i);
st.setString(2, "123" + i);
st.setString(3, "aaa" + i + "@sina.com");
st.setDate(4,new Date(1980, 10, 10));
 
st.addBatch();//将一组参数添加到此 PreparedStatement 对象的批处理命令中。
if(i%1000==0){
st.executeBatch();
st.clearBatch();清空此 Statement 对象的当前 SQL 命令列表。 
}
}

st.executeBatch();将一批命令提交给数据库来执行，如果全部命令执行成功，则返回更新计数组成的数组。返回数组的 int 元素的排序对应于批中的命令，批中的命令根据被添加到批中的顺序排序

采用PreparedStatement.addBatch()实现批处理
优点：发送的是预编译后的SQL语句，执行效率高。
缺点：只能应用在SQL语句相同，但参数不同的批处理中。因此此种形式的批处理经常用于在同一个表中批量插入数据，或批量更新表的数据。

点击了解Mybatis之批处理讲解

2.2 获得数据库自动生成的主键

Connection con=null;
PreparedStatement ps=null;
con = JDBCManager.getConnection();
String sql="insert into users(name,password) values(?,?)";
try {
ps = con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//获取返回的主键；
ps.setString(1, "qq");
ps.setString(2, "123");
ps.executeUpdate();
ResultSet rs=ps.getGeneratedKeys();//返回一个结果集，保存着产生的key的结果集，
while(rs.next()){
 System.out.println(rs.getObject(1));//结果集只有一个值；
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCManager.DBclose(con, ps, null);
}

2.3 JDBC调用存储过程

点击了解MySQL存储过程创建
点击了解Oracle存储过程创建

JDBC调用存数过程(创建好存储过程体)：
当值是输入函数时：

Connection con=null;
       CallableStatement cs=null;
       con=JDBCManager.getConnection();
       try {
cs=con.prepareCall("{call pd(?,?)}");//存储过程语句；
cs.setString(1, "yy");
cs.setString(2, "msn");
cs.execute();//执行
System.out.println("执行成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCManager.DBclose(con, cs, null);
}

当输入和输出同时：

Connection con=null;
      CallableStatement cs=null;
      con=JDBCManager.getConnection();
      try {
cs=con.prepareCall("{call pcall(?,?)}");
cs.setInt(1, 10);
cs.registerOutParameter(2,Types.CHAR);//获取一下注册类型；
cs.execute();//执行
System.out.println(cs.getString(2));//获取第二个String类型的参数值；
cs.execute();
System.out.println("执行成功");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
JDBCManager.DBclose(con, cs, null);
}

2.4 JDBC连接ORACLE

2.4.1 thin连接

由于thin驱动都是纯Java代码，并且使用TCP/IP技术通过java的Socket连接上Oracle数据库，所以thin驱动是与平台无关的，无需安装Oracle客户端，只需要下载一个thin驱动的jar包，并且将环境变量中的CLASS_PATH变量中加入thin驱动的路径就可以了。Thin驱动虽然与平台无关，也无需安装Oracle客户端，但是有一个致命的缺陷就是性能一般，达不到如OCI方式的企业级的要求。另外，如果一个oracle数据库对应一台主机，可以使用thin连接；如果一个oracle数据库对应四五台主机（集群服务器），使用thin时，需要把tnsnames.ora文件中的相关数据库的整个连接字符串都拷贝下来，如此才能连接上oracle集群数据库。而这种方法也是和数位同事讨论并Google了大量资料后才发现的。之前我一直以为thin不支持集群数据库的方式。
然后就是设置连接字符串了。
这个是固定的写法，如下所示：

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn =DriverManager.getConnection ("jdbc:oracle:thin:@127.0.0.1:1521:ora9","sms" , "zzsms");

在上面的连接字符串中，如果不是集群数据库就在@后直接输入数据库IP地址即可；如果是集群数据库，则需要在@后输入tnsnames.ora文件中有关该数据库的所有描述内容（通常是=后边的所有的内容）

public class TestConnection implements DBTest{    
    public void SelectUser(){    
        //设定数据库驱动，数据库连接地址、端口、名称，用户名，密码  
        String driverName="oracle.jdbc.driver.OracleDriver";  
        String url="jdbc:oracle:thin:@localhost:1521:BJPOWERNODE";  //test为数据库名称，1521为连接数据库的默认端口  
        String user="system";   //aa为用户名  
        String pass="test";  //123为密码  
          
        PreparedStatement pstmt = null;  
        ResultSet rs = null;            
        //数据库连接对象  
        Connection conn = null;            
        try {  
            //反射Oracle数据库驱动程序类  
            Class.forName(driverName);                
            //获取数据库连接  
            conn = DriverManager.getConnection(url, user, pass);               
            //输出数据库连接  
            System.out.println(conn);                
            //定制sql命令  
            String sql = "select * from t_user where user_id = ?";                
            //创建该连接下的PreparedStatement对象  
            pstmt = conn.prepareStatement(sql);  
              
            //传递第一个参数值 root，代替第一个问号  
            pstmt.setString(1, "root");  
              
            //执行查询语句，将数据保存到ResultSet对象中  
            rs = pstmt.executeQuery();  
              
            //将指针移到下一行，判断rs中是否有数据  
            if(rs.next()){  
                //输出查询结果  
                System.out.println("查询到名为【" + rs.getString("user_id") + "】的信息，其密码为：" + rs.getString("password"));  
            }else{  
                //输出查询结果  
                System.out.println("未查询到用户名为【" + rs.getString("user_id") + "】的信息");  
            }  
              
        } catch (ClassNotFoundException e) {  
            e.printStackTrace();  
        } catch (SQLException e) {  
            e.printStackTrace();  
        }finally{  
            try{  
                if(rs != null){  
                    rs.close();  
                }  
                if(pstmt != null){  
                    pstmt.close();  
                }  
                if(conn != null){  
                    conn.close();  
                }  
            } catch (SQLException e) {  
                e.printStackTrace();  
            }     
        }        
    }        
    public static void main(String[] args){  
        new TestConnection().SelectUser();  
    }  
}

2.4.2 使用oci连接(Oracle Call Interface)

使用OCI连接数据库是企业级的做法，适应于单个数据库和集群数据库，性能优越，尤其是连接池功能大大提高了应用程序的性能和并发量。唯一的缺点是，若想使用OCI必须要安装Oracle客户端。
安装完Oracle客户端后，里面有个jdbc的文件夹，该文件夹下就包含了OCI驱动和THIN驱动。所以是不需要去网上下载的。这个驱动在jdbc/lib文件夹下，主要有classes12.jar、nls_charset12.jar等等。其中以12结尾的驱动包适应于jdk1.1以上的版本。以11结尾的适应于jdk1.1以下的版本。文件名中含有classes的jar包就是驱动程序，文件名中含有nls的jar包是与国际化有关的类。
找到文件后，就需要把class和nls的jar包的绝对路径加入CLASS_PATH环境变量，否则会报ClassNotFound的异常。
设置环境变量后，就可以直接使用OCI驱动了（驱动就是一个可执行文件和一个连接字符串）。很简单，无非是连接字符串的问题。标准的连接字符串如下所示：

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:oci:@xxzc","duansiyuan", "oracle_password");

只要这两行，就可以保证连接到数据库，而这个数据库不管是单个数据库还是集群数据库。其中xxzc就是数据库名，duansiyuan就是用户名，oralce_password就是密码。

public class DbConnection 
{
final static String sDBDriver = "oracle.jdbc.driver.OracleDriver";
final static String sConnStr = "jdbc:oracle:oci8:sr/sr@ora199";

/**
* 
*/
public DbConnection() 
{
}

/**
* 获得Oracle数据库连接 
*/
public java.sql.Connection connectDbByOci() 
{ 
java.sql.Connection conn=null; 
try
{
Class.forName(sDBDriver);
conn = DriverManager.getConnection(sConnStr);
}
catch (Exception e) 
{
System.out.println("ERROR:"+e.getMessage()); 
} 
return conn;
}
}

2.4.3 JdbcOdbc桥方式

先通过管理工具中的数据源来添加本地对Oracle数据库的连接，然后通过以下的数据库连接类，在本地通过JdbcOdbc桥方式获得Oracle数据库连接

public class DbConnection 
{ 
/**
* 
*/
public DbConnection() 
{
}

/**
* 获得Oracle数据库连接 
*/
public java.sql.Connection connectDbByJdbcOdbcBridge() 
{ 
java.sql.Connection conn=null; 
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc:odbc:ora199","sr","sr");
}
catch (Exception e) 
{
System.out.println("ERROR:"+e.getMessage()); 
} 
return conn;
}
}

JDBC-ODBC指定编码格式

// Load the JDBC-ODBC bridge driver 
Class.forName(sun.jdbc.odbc.JdbcOdbcDriver) ;
// setup the properties 
java.util.Properties prop = new java.util.Properties();
prop.put( " charSet " , " Big5 " );
prop.put( " user " , username);
prop.put( " password " , password);

// Connect to the database 
con = DriverManager.getConnection(url, prop);

3 Oracle和MySQL的数据类型

3.1 Oracle数据类型

数据类型是列或存储过程中的一个属性。
Oracle支持的数据类型可以分为三个基本种类：字符数据类型、数字数据类型以及表示其它数据的数据类型。

  • CHAR
    char数据类型存储固定长度的字符值。一个CHAR数据类型可以包括1到2000个字符。如果对CHAR没有明确地说明长度，它的默认长度则设置为1。如果对某个CHAR类型变量赋值，其长度小于规定的长度，那么Oracle自动用空格填充。
  • VARCHAR2
    存储可变长度的字符串。虽然也必须指定一个VARCHAR2数据变量的长度，但是这个长度是指对该变量赋值的最大长度而非实际赋值长度。不需用空格填充。最多可设置为4000个字符。因为VARCHAR2数据类型只存储为该列所赋的字符（不加空格），所以VARCHAR2需要的存储空间比CHAR数据类型要小。
    Oracle推荐使用VARCHAR2
  • NCHAR和NVARCHAR2
    NCHAR和NVARCHAR2数据类型分别存储固定长度与可变长度的字符串，但是它们使用的是和数据库其他类型不同的字符集。在创建数据库时，需要指定所使用的字符集，以便对数据中数据进行编码。还可以指定一个辅助的字符集[即本地语言集]。NCHAR和NVARCHAR2类型的列使用辅助字符集。
    在Oracle 9i中，可以以字符而不是字节为单位表示NCHAR和NVARCHAR2列的长度。
  • LONG
    long数据类型可以存放2GB的字符数据，它是从早期版本中继承下来的。现在如果存储大容量的数据，Oracle推荐使用CLOB和NCLOB数据类型。在表和SQL语句中使用LONG类型有许多限制。
  • CLOB和NCLOB
    CLOB和NCLOB数据类型可以存储多达4GB的字符数据。NCLOB数据类型可存储NLS数据。
  • 数字数据类型
    Oracle使用标准、可变长度的内部格式来存储数字。这个内部格式精度可以高达38位。
  • NUMBER数据类型可以有两个限定符，如：column NUMBER(precision,scale)。precision表示数字中的有效位。如果没有指定precision的话，Oracle将使用38作为精度。scale表示小数点右边的位数，scale默认设置为0。如果把scale设成负数，Oracle将把该数字取舍到小数点左边的指定位数。
  • 日期数据类型
    Oracle标准日期格式为：DD-MON-YY HH:MI:SS
    通过修改实例的参数NLS_DATE_FORMAT，可以改变实例中插入日期的格式。在一个会话期间，可以通过Alter session SQL命令来修改日期，或者通过使用SQL语句的TO_DATE表达式中的参数来更新一个特定值。
  • RAW和LONG RAW
    RAW数据类型主要用于对数据库进行解释。指定这两种类型时，Oracle以位的形式来存储数据。RAW数据类型一般用于存储有特定格式的对象，如位图。RAW数据类型可占用2KB的空间，而LONG RAW数据类型则可以占用2GB大小。
  • ROWID
    ROWID是一种特殊的列类型，称之为伪列（pseudocolumn）。ROWID伪列在SQL SELECT语句中可以像普通列那样被访问。Oracle数据库中每行都有一个伪列。ROWID表示行的地址，ROWID伪列用ROWID数据类型定义。
    ROWID与磁盘驱动的特定位置有关，因此，ROWID是获得行的最快方法。但是，行的ROWID会随着卸载和重载数据库而发生变化，因此建议不要在事务中使用ROWID伪列的值。例如，一旦当前应用已经使用完记录，就没有理由保存行的ROWID。不能通过任何SQL语句来设置标准的ROWID伪列的值。列或变量可以定义成ROWID数据类型，但是Oracle不能保证该列或变量的值是一个有效的ROWID。
  • LOB
    LOB（大型对象）数据类型，可以保存4GB的信息。LOB有以下3中类型：
    <CLOB>，只能存储字符数据，<NCLOB>保存本地语言字符集数据，<BLOB> 以二进制信息保存数据
    可以指定将一个LOB数据保存在Oracle数据库内，还是指向一个包含次数据的外部文件。
    LOB可以参与事务。管理LOB中的数据必须通过DBMS_LOB PL/SQL内置软件包或者OGI接口。
    为了便于将LONG数据类型转换成LOB，Oracle 9i包含许多同时支持LOB和LONG的函数，还包括一个ALTER TABLE语句的新选择，它允许将LONG数据类型自动转换成LOB。
  • BFILE
    BFILE数据类型用做指向存储在Oracle数据库以外的文件的指针。
  • XML Type
    作为对XML支持的一部分，Oracle 包含了一个新的数据类型XML Type。定义为XMLType的列将存储一个字符LOB列中的XML文档。有许多内置的功能可以使你从文档中抽取单个节点，还可以在XML Type文档中对任何节点创建索引。
  • 用户自定义数据
    从Oracle 8以后，用户可以定义自己的复杂数据类型，它们由Oracle基本数据类型组合而成。
  • AnyType、AnyData和AnyDataSet
    Oracle包括3个新的数据类型，用于定义在现有数据类型之外的数据结构。其中每种数据类型必须用程序单元来定义，以便让Oracle9i知道如何处理这些类型的特定实现。
  • NULL
    NULL值是关系数据库的重要特征之一。实际上，NULL不代表任何值，它表示没有值。如果要创建表的一个列，而这个列必须有值，那么应将它指定为NOT NULL，这表示该列不能包含NULL值。
    任何数据类型都可以赋予NULL值。NULL值引入了SQL运算的三态逻辑。如果比较的一方是NULL值，那么会出现3种状态：TURE、FALSE以及两者都不是。
    因为NULL值不等于0或其他任何值，所以测试某个数据是否为NULL值只能通过关系运算符IS NULL来进行。
    NULL值特别适合以下情况：当一个列还未赋值时。如果选择不使用NULL值，那么必须对行的所有列都要赋值。这实际上也取消了某列不需要值的可能性，同时对它赋的值也很容易产生误解。这种情况则可能误导终端用户，并且导致累计操作的错误结果。
  • number(p,s)
    p:1~38和s:-84~127
    对s分2种情况：
    1. s>0
      精确到小数点右边s位，并四舍五入。然后检验有效数位是否<=p；如果s>p，小数点右边至少有s-p个0填充。
    2. s<0
      精确到小数点左边s位，并四舍五入。然后检验有效数位是否<=p+|s|

123.2564 NUMBER 123.2564
1234.9876 NUMBER(6,2) 1234.99
12345.12345 NUMBER(6,2) Error
1234.9876 NUMBER(6) 1235
12345.345 NUMBER(5,-2) 12300
1234567 NUMBER(5,-2) 1234600
12345678 NUMBER(5,-2) Error
123456789 NUMBER(5,-4) 123460000
1234567890 NUMBER(5,-4) Error
12345.58 NUMBER(*, 1) 12345.6
0.1 NUMBER(4,5) Error
0.01234567 NUMBER(4,5) 0.01235
0.09999 NUMBER(4,5) 0.09999

数据类型 参数 描述
char(n) n=1 to 2000 字节 定长字符串，n字节长，如果不指定长度，缺省为1个字节长（一个汉字为2字节）
varchar2(n) n=1 to 4000 字节 可变长的字符串，具体定义时指明最大长度n，这种数据类型可以放数字、字母以及ASCII码字符集(或者EBCDIC等数据库系统接受的字符集标准)中的所有符号。如果数据长度没有达到最大值n，Oracle会根据数据大小自动调节字段长度，如果你的数据前后有空格，Oracle 会自动将其删去。VARCHAR2是最常用的数据类型。可做索引的最大长度3209。
number(m,n) m=1 to 38n=-84 to 127 可变长的数值列，允许0、正值及负值，m是所有有效数字的位数，n是小数点以后的位数。如：number(5,2)，则这个字段的最大值是99,999，如果数值超出了位数限制就会被截取多余的位数。如：number(5,2)，但在一行数据中的这个字段输入575.316，则真正保存到字段中的数值是575.32。如：number(3,0)，输入575.316，真正保存的数据是575。
date 无 从公元前4712年1月1日到公元4712年12月31日的所有合法日期，Oracle 其实在内部是按7个字节来保存日期数据，在定义中还包括小时、分、秒。缺省格式为DD-MON-YY，如07-11月-00 表示2000年11月7日。
long 无 可变长字符列，最大长度限制是2GB，用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。long是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。
raw(n) n=1 to 2000 可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle 用这种格式来保存较小的图形文件或带格式的文本文件，如Miceosoft Word文档。raw是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。
long raw 无 可变长二进制数据，最大长度是2GB。Oracle 8i用这种格式来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件。在同一张表中不能同时有long类型和long raw类型，long raw也是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。
blob clob nclob 无 三种大型对象(LOB)，用来保存较大的图形文件或带格式的文本文件，如Miceosoft Word文档，以及音频、视频等非文本文件，最大长度是4GB。LOB有几种类型，取决于你使用的字节的类型，Oracle 实实在在地将这些数据存储在数据库内部保存。可以执行读取、存储、写入等特殊操作。
bfile 无 在数据库外部保存的大型二进制对象文件，最大长度是4GB。这种外部的LOB类型，通过数据库记录变化情况，但是数据的具体保存是在数据库外部进行的。Oracle 可以读取、查询BFILE，但是不能写入。大小由操作系统决定。

3.2 MySQL数据类型

3.2.1 datetime和timestamp区别

datetime和timestamp同时能够存储日期时间类型的数据，那么我们是否可以简单地采用timestamp类型而不使用datetime呢，答案肯定是不行的
MySQL的官方文档中对这几种数据类型进行了阐述，可以简单总结如下：

  • DATETIME所支持的时间范围为1000-01-01 00:00:00 到 9999-12-31 23:59:59。MySQL会使用YYYY-MM-DD HH:MM:SS格式来显示DATETIME类型的值；
  • TIMESTAMP所支持的时间范围为1970-01-01 00:00:01 UTC到2038-01-19 03:14:07 UTC。在MySQL中，TIMESTAMP类型的值存储为自1970-01-01 00:00:00UTC起的秒数，但是需要注意的是TIMESTAMP类型并不能表示1970-01-01 00:00:00。

3.3 Oracle和MySQL的数据类型

Oracle和MySQL的数据类型

数据类型 Oracle MySQL
NUMBER(p,s) 存储数值数据类型，如浮点型、整数型、分数、双精度等。其中p为精度,表示数字的总位数(1 <= p <=38) ; s为范围，表示小数点右边的位数,它在-84至127之间。默认38位
TINYINT 微整型，1字节
SMALLINT 小整型，2字节
MEDIUMINT 中整型，3字节
INT或INTEGER 整数数据类型 整型，4字节
BIGINT 大整型，8字节
FLOAT 浮点数数据类型 单精度浮点数值，4字节
DOUBLE 双精度浮点数值，8字节
DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2
CHAR(n) n=1to2000字节,定长字符串，n字节长，如果不指定长度，缺省为1个字节长（一个汉字为2字节） 定长字符串，0-255字节
NCHAR(n) NLS（national language support ， 国际语言支持）的数据类型仅可以存储由数据库 NLS 字符集定义的 Unicode 字符集。Oracle最多可以存储2000Byte NLS（national language support ， 国际语言支持）的数据类型仅可以存储由数据库 NLS 字符集定义的 Unicode 字符集。
VARCHAR(n) 最多可以以可变长度来存储4000B，因此不需要空格来作补充 变长字符串，0-255字节
NVARCHAR(n) n=1to4000字节,最多可以以可变长度来存储4000B，因此不需要空格来作补充 NLS（national language support ， 国际语言支持）的数据类型仅可以存储由数据库 NLS 字符集定义的 Unicode 字符集
VARCHAR2(n) VARCHAR2 比 VARCHAR 更适合使用，由于兼容性的原因，所以仍然在 Oracle 数据库中保留着 VARCHAR
NVARCHAR2 NLS 的数据类型与 VARCHAR2 数据类型等价。这个数据类型最多可存储4000B
CLOB 存储大量的单字节字符数据和多字节字符数据。存储的最大容量为4G
NCLOB 存储可变长度的Unicode字符集字符数据,存储的最大容量为4G
BLOB 存储较大的二进制对象。例：图形，视频，音频等 二进制形式的长文本数据，0-65 535字节
BFILE bfile:文件定位器；指向位于服务器文件系统是二进制文件(存储一个文件路径)
LONG 存储可变长度的字符数据,最多存储2GB
RAW 存储基于字节的数据。最多存储2000个字节，使用需指定大小。raw数据类型可以建立索引
LONG RAW 存储可变长度的二进制数据。最多能存储2GB。不能索引
TINYBLOB 不超过 255 个字符的二进制字符串，0-255字节
TINYTEXT 短文本字符串，0-255字节
TEXT 长文本数据，0-65 535字节
MEDIUMBLOB 二进制形式的中等长度文本数据
MEDIUMTEXT 中等长度文本数据，0-16 777 215字节
LOGNGBLOB 二进制形式的极大文本数据，0-4 294 967 295字节
LONGTEXT 极大文本数据，0-4 294 967 295字节
DATE 存储表的日期和时间数据，使用7个字节固定长度，每个字节分别存储世纪，年，月，日，小时，分和秒；值从公元前4712年1月1日到公元9999年12月31日。 Oracle中的sysdate函数功能是返回当前的日期和时间 日期值，YYYY-MM-DD
TIMESTAMP 存储日期的年，月，日以及时间的小时，分和秒值。其中秒值精确到小数点后6位，同时包含时区信息。 Oracle中的systimestamp函数功能是返回当前日期，时间和时区
TIME 时间值或持续时间，HH:MM:SS
YEAR 年份值，YYYY
DATETIME 混合日期和时间，YYYY-MM-DD HH:MM:SS
TIMESTAMP 混合日期和时间值，时间戳 混合日期和时间值，时间戳，YYYYMMDD HHMMSS
ENUM 一个 ENUM 类型最多可以包含 65536 个元素，其中一个元素被 MySQL 保留，用来存储错误信息，这个错误值用索引 0 或者一个空字符串表示
SET 容器类型，一个 SET 类型最多可以包含 64 项元素，且不可能包含两个相同的元素
posted @ 2022-07-27 10:50  上善若泪  阅读(154)  评论(0)  编辑  收藏  举报