mysql - ms sql数据库导入期间出现错误1064

Question

我正在尝试使用 MySQL Workbench 将 MS Sql 导入 MySQL，其中 4 个表在创建目标时出现错误。在所有表中，它都发生在 upsize_ts TIMESTAMP(0) NULL 下方。

ERROR: Error executing 'CREATE TABLE IF NOT EXISTS `2000`.`Booking Table` (
  `BookingID` INT NOT NULL AUTO_INCREMENT,
  `LoadNumber` INT NULL DEFAULT 0,
  `NumberPickUps` INT NULL DEFAULT 0,
  `NumberDrops` INT NULL DEFAULT 0,
  `StopRate` DECIMAL(19,4) NULL DEFAULT 0,
  `EqpType` VARCHAR(100) NULL,
  `LoadedWeight` INT NULL DEFAULT 0,
  `Miles` DOUBLE NULL DEFAULT 0,
  `RateType` VARCHAR(100) NULL,
  `RateVolume` FLOAT(24,0) NULL DEFAULT 0,
  `Rate` DECIMAL(19,4) NULL DEFAULT 0,
  `Load` TINYINT(1) NOT NULL DEFAULT 0,
  `UnLoad` TINYINT(1) NOT NULL DEFAULT 1,
  `Pallets` INT NULL DEFAULT 0,
  `BaseCharge` DECIMAL(19,4) NULL DEFAULT 0,
  `StopCharges` DECIMAL(19,4) NULL DEFAULT 0,
  `OtherCharges` DECIMAL(19,4) NULL DEFAULT 0,
  `SpecialInstructions` VARCHAR(510) NULL,
  `GrossCharge` DECIMAL(19,4) NULL DEFAULT 0,
  `GrossToTruck` DECIMAL(19,4) NULL DEFAULT 0,
  `Advance` DECIMAL(19,4) NULL DEFAULT 0,
  `TruckChg1` DECIMAL(19,4) NULL DEFAULT 0,
  `TruckChg1Exp` VARCHAR(510) NULL,
  `TruckChg2` DECIMAL(19,4) NULL DEFAULT 0,
  `TruckChg2Exp` VARCHAR(510) NULL,
  `DPalletChg` DECIMAL(19,4) NULL DEFAULT 0,
  `NetToTruck` DECIMAL(19,4) NULL DEFAULT 0,
  `GrossToBroker` DECIMAL(19,4) NULL DEFAULT 0,
  `Pieces` INT NULL DEFAULT 0,
  `OtherChargesExp` VARCHAR(510) NULL,
  `CustName` VARCHAR(200) NULL,
  `CustPIN_NUM` INT NULL DEFAULT 0,
  `TotalMiles` DOUBLE NULL DEFAULT 0,
  `ExToCompAcct` TINYINT(1) NOT NULL DEFAULT 0,
  `Tractor` VARCHAR(100) NULL,
  `Trailer` VARCHAR(100) NULL,
  `Carrier` VARCHAR(100) NULL,
  `CarrPIN_NUM` INT NULL DEFAULT 0,
  `Driver` VARCHAR(100) NULL,
  `PaperRcvd` TINYINT(1) NOT NULL DEFAULT 0,
  `CompAcctExpd` TINYINT(1) NOT NULL DEFAULT 0,
  `CompletedDate` VARCHAR(100) NULL,
  `ExpDate` VARCHAR(100) NULL,
  `PaperRcvdDate` DATETIME NULL,
  `PerDiem` DECIMAL(19,4) NULL DEFAULT 0,
  `DStopPay` DECIMAL(19,4) NULL DEFAULT 0,
  `VendEmp` VARCHAR(100) NULL DEFAULT 'VEND',
  `UnLoading` DECIMAL(19,4) NULL DEFAULT 0,
  `Detention` DECIMAL(19,4) NULL DEFAULT 0,
  `DDetention` DECIMAL(19,4) NULL DEFAULT 0,
  `DUnloading` DECIMAL(19,4) NULL DEFAULT 0,
  `LTL` TINYINT(1) NOT NULL DEFAULT 0,
  `LTLBooked` TINYINT(1) NOT NULL DEFAULT 0,
  `CommodityNumber` INT NULL DEFAULT 1,
  `PickUpNumber` INT NULL DEFAULT 1,
  `Commodity` VARCHAR(200) NULL,
  `BillingType` VARCHAR(100) NULL DEFAULT 'Per Load',
  `LTLNumber` INT NULL DEFAULT 0,
  `OriginCity` VARCHAR(100) NULL,
  `OriginState` VARCHAR(100) NULL,
  `DestinationCity` VARCHAR(100) NULL,
  `DestinationState` VARCHAR(100) NULL,
  `DeliverDate` DATETIME NULL,
  `Dispatcher` VARCHAR(100) NULL,
  `CarrierNumber` INT NULL DEFAULT 1,
  `OKToInvoice` TINYINT(1) NOT NULL DEFAULT 0,
  `OKToPay` TINYINT(1) NOT NULL DEFAULT 0,
  `ExportedToPay` TINYINT(1) NOT NULL DEFAULT 0,
  `Printed` TINYINT(1) NOT NULL DEFAULT 0,
  `VendEmpBill` VARCHAR(100) NULL DEFAULT 'VEND',
  `IVNotes` VARCHAR(510) NULL,
  `CS` TINYINT(1) NOT NULL DEFAULT 0,
  `FSurCharge` DECIMAL(19,4) NULL DEFAULT 0,
  `PalletCharges` DECIMAL(19,4) NULL DEFAULT 0,
  `OtherCharges2` DECIMAL(19,4) NULL DEFAULT 0,
  `OtherChargesExp2` VARCHAR(200) NULL,
  `DFSurCharge` DECIMAL(19,4) NULL DEFAULT 0,
  `upsize_ts` TIMESTAMP(0) NULL,
  `RFSurcharge` DECIMAL(19,4) NULL DEFAULT 0,
  `DRFSurcharge` DECIMAL(19,4) NULL DEFAULT 0,
  `EDIInvoiced` TINYINT(1) NULL DEFAULT 0,
  `LastInvoiceAN` INT NULL DEFAULT 0,
  `EDIReadyToInvoice` TINYINT(1) NULL DEFAULT 0,
  PRIMARY KEY (`BookingID`),
  INDEX `BillingType` (`BillingType` ASC),
  INDEX `BookingId` (`BookingID` ASC),
  INDEX `CarrierNumber` (`CarrierNumber` ASC),
  INDEX `CarrPIN_NUM` (`CarrPIN_NUM` ASC),
  INDEX `CommodityNumber` (`CommodityNumber` ASC),
  INDEX `CompletedDate` (`CompletedDate` ASC),
  INDEX `CustPIN_NUM` (`CustPIN_NUM` ASC),
  INDEX `DeliverDate` (`DeliverDate` ASC),
  INDEX `Dispatcher` (`Dispatcher` ASC),
  INDEX `EqpType` (`EqpType` ASC),
  INDEX `ExpDate` (`ExpDate` ASC),
  INDEX `LoadNumber` (`LoadNumber` ASC),
  INDEX `LTLNumber` (`LTLNumber` ASC),
  INDEX `NumberDrops` (`NumberDrops` ASC),
  INDEX `NumberPickUps` (`NumberPickUps` ASC),
  INDEX `PaperRcvdDate` (`PaperRcvdDate` ASC),
  INDEX `PickUpNumber` (`PickUpNumber` ASC),
  INDEX `VendEmp` (`VendEmp` ASC),
  INDEX `VendEmpBill` (`VendEmpBill` ASC))'

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(0) NULL,

  `RFSurcharge` DECIMAL(19,4) NULL DEFAULT 0,

  `DRFSurcharge` DECIMAL' at line 79.

SQL Error: 1064

还有这个

CREATE TABLE IF NOT EXISTS `2000`.`Driver Table` (
   `PIN_NUM` INT NULL DEFAULT 0,
   `SSN` VARCHAR(100) NULL,
   `PayType` VARCHAR(100) NULL DEFAULT 'Percentage',
   `PayRate` FLOAT(24,0) NULL DEFAULT 0,
   `PerDiem` TINYINT(1) NOT NULL DEFAULT 0,
   `PerDiemPayType` VARCHAR(100) NULL DEFAULT 'Per Mile',
   `PerDiemPayRate` FLOAT(24,0) NULL DEFAULT 0,
   `Tractor` VARCHAR(100) NULL,
   `Trailer` VARCHAR(100) NULL,
   `PaidDeadHead` TINYINT(1) NOT NULL DEFAULT 0,
   `StopPayType` VARCHAR(100) NULL DEFAULT 'Flat',
   `StopPayRate` FLOAT(24,0) NULL DEFAULT 0,
   `DetentionPayType` VARCHAR(100) NULL DEFAULT 'Percentage',
   `DetentionPayRate` FLOAT(24,0) NULL DEFAULT 0,
   `UnloadingPayType` VARCHAR(100) NULL DEFAULT 'Flat',
   `UnloadingPayRate` FLOAT(24,0) NULL DEFAULT 0,
   `LicenseNumber` VARCHAR(100) NULL,
   `LicenseExpDate` DATETIME NULL,
   `BirthDate` DATETIME NULL,
   `HireDate` DATETIME NULL,
   `PhysicalExpires` DATETIME NULL,
   `PagerNumber` VARCHAR(100) NULL,
   `CellNumber` VARCHAR(100) NULL,
   `FuelCardNumber` VARCHAR(100) NULL,
   `EmergencyContact1` VARCHAR(200) NULL,
   `EmergencyContact1phone1` VARCHAR(100) NULL,
   `EmergencyContact1phone2` VARCHAR(100) NULL,
   `EmergencyContact1Notes` VARCHAR(180) NULL,
   `EmergencyContact2` VARCHAR(200) NULL,
   `EmergencyContact2phone1` VARCHAR(100) NULL,
   `EmergencyContact2phone2` VARCHAR(100) NULL,
   `EmergencyContact2Notes` VARCHAR(180) NULL,
   `DeadMilesRate` FLOAT(24,0) NULL DEFAULT 0,
   `OwnerOp` TINYINT(1) NOT NULL DEFAULT 0,
   `EFSCardNumber` INT NULL,
   `AutoNumber` INT NOT NULL AUTO_INCREMENT,
   `upsize_ts` TIMESTAMP(0) NULL,
   PRIMARY KEY (`AutoNumber`),
   INDEX `AutoNumber` (`AutoNumber` ASC),
   INDEX `EFSCardNumber` (`EFSCardNumber` ASC),
   INDEX `LicenseExpDate` (`LicenseExpDate` ASC),
   INDEX `PhysicalExpires` (`PhysicalExpires` ASC),
   INDEX `PIN_NUM` (`PIN_NUM` ASC),
   INDEX `Tractor` (`Tractor` ASC),
   INDEX `Trailer` (`Trailer` ASC))

任何帮助将不胜感激。谢谢

Answer 1

问题出在这一行:

`upsize_ts` TIMESTAMP(0) NULL,

如果您使用的 MySQL 版本早于 5.6.4，TIMESTAMP 数据类型没有精度选项，则不能输入 (0)那里。它应该只是:

`upsize_ts` TIMESTAMP NULL,

由于 0 是默认值，因此这些子句是等效的。