如何在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();

}

}