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