如何在MySQL&Oracle下创建自动递增字段

如何在MySQL&Oracle(大型网站数据库平台)下创建自动递增字段

一、在MySQL(和PHP搭配之最佳组合)下创建自动递增字段：

create table article

(

id int Primary key auto_increment,  //主键自动递增

title varchar(255)

);

 

二、Oracle是通过序列(sequence)或者触发器(trigger)可以实现。

假设关键字段为id，建一个序列，代码为：

n        Create sequence
create sequence SEQ_LEAVE_WORDS
minvalue 1
maxvalue 999999
start with 1
increment by 1
cache 20;

n        举例说明：

    //添加记录

    public void update(LeaveWordBean bean) {

       Date date = new Date();

       SimpleDateFormat formater = new SimpleDateFormat("yyyyMMdd");

       String today = formater.format(date);

       String seqName = "SEQ_LEAVE_WORDS";//序列名字

//方法一：通过seq取得主键的值

       String sql1 = "select "+seqName+".NEXTVAL id from dual";

//方法二：通过sql取得主键的值

       //String sql1 = "Select max(id) maxId from leave_words";

       String sql2 = "Insert into leave_words(id, title, content, leavedate) values(?, ?, ?, ?)";

       int maxId = 0;      

       try {

           //stmt = conn.createStatement();

           //rs = stmt.executeQuery(sql1);

           //while(rs.next()) {

              //maxId = rs.getInt(1);

           //}

           stmt = conn.createStatement();

           rs = stmt.executeQuery(sql1);

           while (rs.next()) {

              maxId = rs.getInt(1);

           }

          

           pstmt = conn.prepareStatement(sql2);

           pstmt.setInt(1, maxId);

           pstmt.setString(2, bean.getTitle());

           pstmt.setString(3, bean.getContent());

           pstmt.setString(4, today);

           pstmt.executeUpdate();

       } catch(SQLException e) {

           e.printStackTrace();

       }

    }