welcome to xlongwei.com

欢迎大家一起学习、交流、分享


QQ:9167702333 邮箱:admin@xlongwei.com

使用sql过程为数据库表添加基础字段


分类 MySQL   关键字 分享   标签 mysql   发布 hongwei  1427551013687
注意 转载须保留原文链接,译文链接,作者译者等信息。  
设计数据库表结构时通常会有一些基础字段是所有表都可以有的,例如id、version等字段,这时可以使用存储过程来批量添加。而实体类映射时就可以有BaseEntity来对应这些基础字段,按需要还可以酌情增加字段:
  • id,推荐使用bigint(20),推荐手动生成id值,下文的IdService生成的值在整个数据库中唯一
  • version,版本号用于hibernate乐观锁标记,写入数据时如果版本号校验失败会抛出异常
  • create_at,数据生成时间,插入数据时自动填充值
  • deleted,删除标记,数据标记删除而不是完全删除对以后是留有余地的
  • update_at,update_by,delete_at,delete_by,结合shiro等权限控制系统可以添加listener自动设置这些值,不过笔者没有做那么复杂
drop procedure if exists table_base;
create procedure `table_base`()
begin
	declare done int default 0;
	declare a varchar(50);
	declare rs cursor for select `TABLE_NAME` from information_schema.`TABLES` where `TABLE_SCHEMA`='ite';
	declare continue handler for sqlstate '02000' set done=1;
	open rs;
	repeat fetch rs into a;
	
    IF NOT done THEN
	set @s=concat('alter table ', a, ' add column create_at timestamp default current_timestamp'
		, ', add column deleted bit default 0'
		, ', add column version int(11) default 1;');
	prepare stmt from @s;
	execute stmt;
    END IF;

	until done end repeat;
	close rs;
end
;
call table_base();
drop procedure if exists table_base;
零散的几张表更新时
alter table name add column create_at timestamp default current_timestamp, add column deleted bit default 0, add column version int(11) default 1;
BaseEntity.java
@Id	@GeneratedValue(generator = "ite")    
@GenericGenerator(name = "ite", strategy = "assigned")    
private Long id;

@Temporal(TemporalType.TIMESTAMP) @Column(updatable=false)
private Date createAt;
@Column(insertable=false)
private boolean deleted;
@Version @Column(insertable=false)
private Integer version;
IdService.java,生成全局唯一的id值。
private Long last = Long.MIN_VALUE;
public Result<Long> next() {
	String format = DateUtil.format(new Date(), FormatType.MICROSECONDS);//yyyyMMddHHmmssSSS,length=17
	Long parseLong = NumberUtil.parseLong(format, last);
	if(parseLong>last) {
		last = parseLong;
		return Result.newSuccess(parseLong);
	}else {
		last++;
		return Result.newSuccess(last);
	}
}
实际上还是有两种情况下id值会重复:
1,基础数据,id值自增,数据多不过上百条,例如:CodeList常量,BankType银行,Category分类等,这时id=1,2,3,4,5就是重复出现的
2,相关数据,id值相同,例如:User.id=UseProfile.id=UserStatus.id,这时使用相同的id值会比较方便一点,id标识了唯一的用户,而不仅仅是User记录