MYSQL

最新推荐文章于 2024-09-22 17:00:19 发布

梨麦科技

最新推荐文章于 2024-09-22 17:00:19 发布

阅读量152

点赞数

分类专栏： MYSQL 文章标签： mysql 数据库 servlet

本文链接：https://blog.csdn.net/g18397/article/details/123088248

版权

MYSQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MYSQL的安装目录：

show variables like 'datadir';

//从一个数据库导入数据到另一个数据库

insert into newDatabase.table1(field1,field2)

select field1,field2 from table1;

BEGIN

START TRANSACTION;

set @N := (now());
set @days := 90;

INSERT INTO cfaphis.datatablehis ( Barcode, DeviceID, DatiumName, DatiumType, DatiumValue, DatiumTopLimit, DatiumBottomLimit, PartDeviceID)
SELECT Barcode, DeviceID, DatiumName, DatiumType, DatiumValue, DatiumTopLimit, DatiumBottomLimit, PartDeviceID
FROM datatable
WHERE Barcode in (
SELECT Barcode
FROM maintable
WHERE Mfgtime <DATE_SUB( @N, INTERVAL @days DAY)
);

DELETE FROM datatable
WHERE Barcode in (
SELECT Barcode
FROM maintable
WHERE Mfgtime <DATE_SUB(@N, INTERVAL @days DAY)
);

INSERT INTO cfaphis.partdevicehis (Barcode,DeviceID,OperatorID,StartTime,EndTime,Result,DefectID,plcdata)
SELECT Barcode,DeviceID,OperatorID,StartTime,EndTime,Result,DefectID,plcData
FROM partdevice
-- WHERE StartTime <DATE_SUB(@N, INTERVAL @days DAY);
WHERE Barcode in (
SELECT Barcode
FROM maintable
WHERE Mfgtime <DATE_SUB( @N, INTERVAL @days DAY)
);

DELETE FROM partdevice
WHERE Barcode in (
SELECT Barcode
FROM maintable
WHERE Mfgtime <DATE_SUB( @N, INTERVAL @days DAY)
);

INSERT INTO cfaphis.maintablehis (Barcode,PartID,ChuteBarcode,SkinBarcode,CustomerBarcode,ProcessID,CurrentProcess,CncID,MfgStatusID,MfgResultID,DeviceFailureReasonID,MfgTime,Import,parttype)
SELECT Barcode,PartID,ChuteBarcode,SkinBarcode,CustomerBarcode,ProcessID,CurrentProcess,CncID,MfgStatusID,MfgResultID,DeviceFailureReasonID,MfgTime,Import,parttype
FROM maintable
WHERE MfgTime < DATE_SUB( @N, INTERVAL @days DAY);

DELETE FROM maintable
WHERE Mfgtime <DATE_SUB( @N, INTERVAL @days DAY );

COMMIT;

END