mysql - MSSQL 到 MySQL - 读取十进制时为 "Invalid date literal detected"

Question

我正在尝试从 MSSQL 迁移到 MySQL，但在读取某些十进制列时不断遇到“检测到无效的日期文字”错误。我正在运行以下命令(截至发布时均为最新):

Windows 7
MSSQL 2008 R2 
MySQL 5.6.21 Community 
Workbench 6.2.3 

我尝试多次使用相同的行/表重新运行迁移，导致相同的问题。例如下面的“Times”表:

INSERT INTO `GenshenHR2`.`Times` (`TimesID`, `TimesheetID`, `StaffID`, `StoreID`, `JobID`, `Hours`, `Holiday`, `Overtime`, `Unpaid`) 
VALUES (1,1,1,1,1,'97.0','.0','.0','.0'),(2,1,2,1,2,'152','.0','.0','.0'),
(3,1,3,1,3,'152','.0','.0','.0'),(4,1,4,1,4,'152','.0','.0','.0'),
(5,6,5,6,6,'.0','.0','.0','.0'),(6,6,8,6,9,'.0','.0','.0','.0'),
(7,6,9,6,10,'80.0','8.0','8.0','.0'),(8,6,10,6,11,'.0','.0','.0','.0'),
(9,6,11,6,12,'.0','.0','.0','.0'),(10,6,12,6,13,'.0','.0','.0','.0'),
(11,6,13,6,14,'.0','.0','.0','.0'),(12,6,14,6,15,'.0','.0','.0','.0'),
(13,6,15,6,16,'.0','.0','.0','.0'),(14,6,16,6,17,'.0','.0','.0','.0'),
(15,6,17,6,18,'.0','.0','.0','.0'),(16,6,18,6,19,'.0','.0','.0','.0'),
(17,6,19,6,20,'.0','.0','.0','.0'),(18,6,20,6,21,'.0','.0','.0','.0'),
(19,6,21,6,22,'.0','.0','.0','.0'),(20,6,22,6,23,'.0','.0','.0','.0'),
(21,6,23,6,24,'.0','.0','.0','.0'),(22,6,24,6,25,'.0','.0','.0','.0'),
(23,6,25,6,26,'.0','.0','.0','.0'),(24,6,26,6,27,'.0','.0','.0','.0'),
(25,6,27,6,28,'.0','.0','.0','.0'),(26,6,28,6,29,'.0','.0','.0','.0'),
(27,6,29,6,30,'.0','.0','.0','.0'),(28,2,30,2,31,'177','8.0','.0','.0'),
(29,2,31,2,32,'179','4.0','.0','.0'),(30,2,32,2,33,'179','8.0','.0','.0'),
(31,2,33,2,34,'177','4.0','.0','.0'),(32,2,34,2,35,'177','.0','.0','.0'),
(33,2,35,2,36,'24.0','.0','.0','.0'),(34,2,36,2,37,'177\0\0','.0','.0','.0'),
(35,2,37,2,38,'64.5','.0','.0','.0'),(36,2,38,2,39,'114\0\0','.0','57.5','.0'),
(37,4,39,4,40,'.0','.0','.0','.0'),(38,4,40,4,41,'.0','.0','.0','.0'),
(39,4,41,4,42,'.0','.0','.0','.0'),(40,4,42,4,43,'.0','.0','.0','.0'),
(41,4,43,4,44,'.0','.0','.0','.0'),(42,4,44,4,45,'.0','.0','.0','.0'),
(43,4,45,4,46,'.0','.0','.0','.0'),(44,4,46,4,47,'.0','.0','.0','.0'),
(45,4,47,4,48,'.0','.0','.0','.0'),(46,4,48,4,49,'.0','.0','.0','.0'),
(47,4,49,4,50,'.0','.0','.0','.0'),(48,4,50,4,51,'.0','.0','.0','.0'),
(49,4,51,4,52,'.0','.0','.0','.0'),(50,4,52,4,53,'.0','.0','.0','.0'),
(51,4,53,4,54,'.0','.0','.0','.0'),(52,4,54,4,55,'.0','.0','.0','.0'),
(53,4,55,4,56,'.0','.0','.0','.0'),(54,4,56,4,59,'.0','.0','.0','.0'),
(55,4,57,4,60,'.0','.ERROR:`GenshenHR2`.`Times`:Inserting Data: Incorrect decimal value: '177' for column 'Hours' at row 34 

出于某种原因，它在第 34 和 36 行上用斜杠(例如 177\0\0)而不是正确的十进制格式(例如 177.5)解释第 6 列“小时”中的数据

在 MSSQL 中，该列为:[Hours] NUMERIC(4,1) NULL DEFAULT 0它在迁移中对应的行是:'Hours' DECIMAL(4,1) NULL DEFAULT 0

具有讽刺意味的是，以下 3 列(假期、加类和未付)都是相同的数据类型，并且查看日志提取似乎插入没有问题(参见第 36 行/57.5)。

有人知道为什么会发生这种情况吗？

Answer 1

问题出在您的字符串文字上:177\0\0。在 MySQL 中，反斜杠是转义字符。 SQL Server 不允许这样做，因此它尝试将 177\0\0 作为文字字符串转换为数字。但是为什么在迁移过程中要将数字字段导出为字符串文字呢？

此外，删除后面的勾号 (`)。 SQL Server 无法识别它们。