MySQL 5.6正常的INSERT语句在8.0版本报错，求排查原因

错误原因 #

核心问题是你使用了MySQL 8.0的保留关键字作为字段名：

• 错误提示指向的System字段，在MySQL 8.0中被列为保留关键字，而MySQL 5.6中不属于保留关键字，因此在5.6中可以直接使用，8.0中未转义会触发语法错误。
• 此外，语句中的Date、Time也是MySQL的保留关键字，MySQL 8.0对保留关键字的语法检查比5.6更严格，这也是潜在的问题点。

修复方案 #

用反引号（`）包裹所有作为字段名的保留关键字，修改后的INSERT语句如下：

INSERT INTO images(SystemRoot, BaseBuildRevisionNumber, BuildBranch, BuildGUID, BuildLab, BuildLabEx, CompositionEditionID, CurrentBuild, CurrentBuildNumber, CurrentMajorVersionNumber, CurrentMinorVersionNumber, CurrentType, CurrentVersion, EditionID, EditionSubManufacturer, EditionSubstring, EditionSubVersion, InstallationType, InstallDate, ProductName, ReleaseId, SoftwareType, UBR, PathName, ProductId, DigitalProductId, DigitalProductId4, PendingInstall, DisplayVersion, InstallTime, Software, `System`, Manufacturer, Model, Systype, Serial, HDD1, HDD2, HDD3, HDD4, Partitions, OurParts, `Date`, `Time`, FileName, CurrentOperation, Progress, Status, TotalParts, CurrentPartition, DiskRead, DiskWrite, NetBandwidth, NetUp, NetDown, CPU, PartitionType, PartitionDetails, ImageByParts, ImageFileNames) Values ("", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "UEFI", "Dell Inc.", "Latitude 3510", "Notebook", "BFVS303", "128 GB", "None", "None", "None", "sda1 sda2 sda3 sda4 sda5 sda6", "1", "2023-11-10", "18:32:57", "Dell_Inc._Latitude_3510_Notebook_wj2GQdFq", "", "", "", "", "", "", "", "", "", "", "", "", "gpt", "1", "True", "Dell_Inc._Latitude_3510_Notebook_wj2GQdFq-sda1.img.gz");

验证说明 #

修改后重新执行语句，MySQL 8.0即可正常处理该INSERT操作，和5.6中的执行效果一致。

内容的提问来源于stack exchange，提问作者user2107349