初始化数据库

基于mariadb初始化数据库脚本

/*创建用户表*/
create table if not exists t_user(
   id int primary key auto_increment,
   loginname varchar(50) not null comment '登录名',
   password varchar(100) not null comment '登录密码',
   createTime datetime not null comment '创建时间'
)engine=InnoDB default charset=utf8;

delimiter |
begin not atomic
	if not exists(select * from information_schema.`COLUMNS` where 
               TABLE_SCHEMA=database() and 
               TABLE_NAME='t_user' and 
               COLUMN_NAME='ticket') then
      alter table t_user add column ticket varchar(50) comment '登陆成功后，系统分配uuid身份凭证';
   end if;
   
   if not exists(select * from information_schema.TABLE_CONSTRAINTS where
                           CONSTRAINT_SCHEMA=database() and
                           CONSTRAINT_NAME='t_user_unique_1' and 
                           CONSTRAINT_TYPE='UNIQUE') then
      alter table t_user add constraint t_user_unique_1 unique(loginname);
   end if;
end|
delimiter ;

/*初始化插入用户数据*/
insert into t_user(loginname,password,createTime)
select 'root','10d66ccecdf01ccfff2b86cfb1fd2b76',now() from dual
where not exists(select id from t_user where loginname='root');

delimiter |
begin not atomic
	if not exists(select * from information_schema.TABLE_CONSTRAINTS where
                           CONSTRAINT_SCHEMA=database() and
                           CONSTRAINT_NAME='t_notification_fk_userId' and 
                           CONSTRAINT_TYPE='FOREIGN KEY') then
      alter table t_notification add constraint t_notification_fk_userId
      foreign key (userId) references t_user(id);
   end if;
end|
delimiter ;