2019黑马OA-master中使用mysql替换orcle创建表的一种方法

最新推荐文章于 2020-08-26 17:57:12 发布

EEEEEEcho

最新推荐文章于 2020-08-26 17:57:12 发布

阅读量236

点赞数

分类专栏：数据库文章标签： mysql 触发器

本文链接：https://blog.csdn.net/EEEEEEcho/article/details/107612276

版权

数据库专栏收录该内容

16 篇文章 1 订阅

订阅专栏

原方法

CREATE TABLE product(  
id varchar2(32) default SYS_GUID() PRIMARY KEY,  
productNum VARCHAR2(50) NOT NULL,  
productName VARCHAR2(50),  
cityName VARCHAR2(50),  
DepartureTime timestamp,  
productPrice Number,  
productDesc VARCHAR2(500),  
productStatus INT,  
CONSTRAINT product UNIQUE (id, productNum) 
)

insert into PRODUCT (id, productnum, productname, cityname, departuretime, productprice, productdesc, productstatus) values ('676C5BD1D35E429A8C2E114939C5685A', 'itcast-002', '北京三日游', '北京', to_timestamp('1010-2018 10:10:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 1200, '不错的旅行', 1);
insert into PRODUCT (id, productnum, productname, cityname, departuretime, productprice, productdesc, productstatus) values ('12B7ABF2A4C544568B0A7C69F36BF8B7', 'itcast-003', '上海五日游', '上海', to_timestamp('2504-2018 14:30:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 1800, '魔都我来了', 0); 
insert into PRODUCT (id, productnum, productname, cityname, departuretime, productprice, productdesc, productstatus) values ('9F71F01CB448476DAFB309AA6DF9497F', 'itcast-001', '北京三日游', '北京', to_timestamp('1010-2018 10:10:00.000000', 'dd-mm-yyyy hh24:mi:ss.ff'), 1200, '不错的旅行', 1);

修改后，使用触发器生成UUID，在表外建立联合唯一关系

use oamaster;
CREATE TABLE product(
id varchar(32) PRIMARY KEY, 
productNum VARCHAR(50) NOT NULL,  
productName VARCHAR(50),  
cityName VARCHAR(50),
DepartureTime timestamp,  
productPrice double,  
productDesc VARCHAR(500),
productStatus int
)
alter table product add unique index(id,productNum);
create trigger id_trigger
before insert on product
for each row
begin
	set new.id = REPLACE(UUID(),'-','');
end

insert into product (id, productnum, productname, cityname, departuretime, productprice, productdesc, productstatus) values ('676C5BD1D35E429A8C2E114939C5685A', 'itcast-002', '北京三日游', '北京', timestamp('2018-11-17 10:10:00'), 1200, '不错的旅行', 1); 
insert into PRODUCT (id, productnum, productname, cityname, departuretime, productprice, productdesc, productstatus) values ('12B7ABF2A4C544568B0A7C69F36BF8B7', 'itcast-003', '上海五日游', '上海', timestamp('2018-04-25 14:30:00'), 1800, '魔都我来了', 0); 
insert into PRODUCT (id, productnum, productname, cityname, departuretime, productprice, productdesc, productstatus) values ('9F71F01CB448476DAFB309AA6DF9497F', 'itcast-001', '北京三日游', '北京', timestamp('2018-10-10 10:10:00'), 1200, '不错的旅行', 1);