mysql工作台输出错误1064中的数据库转发工程

uidvcgyl  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(394)

这是mysql workbench在正向工程时创建的脚本：

  1. -- MySQL Workbench Forward Engineering
  3. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  4. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  5. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
  7. -- -----------------------------------------------------
  8. -- Schema DialogueFlowDB
  9. -- -----------------------------------------------------
  11. -- -----------------------------------------------------
  12. -- Schema DialogueFlowDB
  13. -- -----------------------------------------------------
  14. CREATE SCHEMA IF NOT EXISTS `DialogueFlowDB` DEFAULT CHARACTER SET utf8 ;
  15. USE `DialogueFlowDB` ;
  17. -- -----------------------------------------------------
  18. -- Table `DialogueFlowDB`.`Status`
  19. -- -----------------------------------------------------
  20. CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`Status` (
  21.   `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  22.   `Status` CHAR(255) NOT NULL,
  23.   PRIMARY KEY (`ID`))
  24. ENGINE = InnoDB;
  26. -- -----------------------------------------------------
  27. -- Table `DialogueFlowDB`.`MultiStep_Dialogues`
  28. -- -----------------------------------------------------
  29. CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`MultiStep_Dialogues` (
  30.   `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  31.   `MultiStep_Dialogue` CHAR(255) NOT NULL,
  32.   `Data_Items` VARCHAR(1000) NULL,
  33.   `Nodes` VARCHAR(1000) NULL,
  34.   `Node_Links` VARCHAR(1000) NULL,
  35.   `Comments` VARCHAR(1000) NULL,
  36.   `Status` INT UNSIGNED NOT NULL,
  37.   `Issue` INT NOT NULL,
  38.   `LO_Name` CHAR(255) NULL,
  39.   `LO_Description` CHAR(255) NULL,
  40.   `LO_Condition` VARCHAR(1000) NULL,
  41.   `LO_Variables` VARCHAR(1000) NULL,
  42.   `LO_List` INT NULL,
  43.   PRIMARY KEY (`ID`),
  44.   INDEX `fk_MultiStep_Dialogues_Status1_idx` (`Status` ASC) VISIBLE,
  45.   CONSTRAINT `fk_MultiStep_Dialogues_Status1`
  46.     FOREIGN KEY (`Status`)
  47.     REFERENCES `DialogueFlowDB`.`Status` (`ID`)
  48.     ON DELETE NO ACTION
  49.     ON UPDATE NO ACTION)
  50. ENGINE = InnoDB;
  52. -- -----------------------------------------------------
  53. -- Table `DialogueFlowDB`.`Node_Types`
  54. -- -----------------------------------------------------
  55. CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`Node_Types` (
  56.   `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  57.   `Node_Type` CHAR(255) NOT NULL,
  58.   PRIMARY KEY (`ID`))
  59. ENGINE = InnoDB;
  61. -- -----------------------------------------------------
  62. -- Table `DialogueFlowDB`.`Variables`
  63. -- -----------------------------------------------------
  64. CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`Variables` (
  65.   `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  66.   `Variable` CHAR(255) NOT NULL,
  67.   `Variable_ID` CHAR(255) NOT NULL,
  68.   `System_Variable` CHAR(255) NULL,
  69.   `Values_per_Item` ENUM('Single', 'Multiple') NOT NULL DEFAULT 'Single',
  70.   `MultiStep_Dialogue` INT UNSIGNED NOT NULL,
  71.   `Input_Values` VARCHAR(1000) NULL,
  72.   `Comments` VARCHAR(1000) NULL,
  73.   PRIMARY KEY (`ID`),
  74.   INDEX `fk_Variables_MultiStep_Dialogues1_idx` (`MultiStep_Dialogue` ASC) VISIBLE,
  75.   CONSTRAINT `fk_Variables_MultiStep_Dialogues1`
  76.     FOREIGN KEY (`MultiStep_Dialogue`)
  77.     REFERENCES `DialogueFlowDB`.`MultiStep_Dialogues` (`ID`)
  78.     ON DELETE NO ACTION
  79.     ON UPDATE NO ACTION)
  80. ENGINE = InnoDB;
  82. -- -----------------------------------------------------
  83. -- Table `DialogueFlowDB`.`Nodes`
  84. -- -----------------------------------------------------
  85. CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`Nodes` (
  86.   `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  87.   `Node` CHAR(255) NOT NULL,
  88.   `Node_ID` CHAR(255) NOT NULL,
  89.   `Node_Sequence` ENUM('End', 'Middle', 'Start') NOT NULL,
  90.   `Node_Type` INT UNSIGNED NOT NULL,
  91.   `XPosition` INT NOT NULL DEFAULT 0,
  92.   `YPosition` INT NOT NULL DEFAULT 0,
  93.   `Answer` VARCHAR(1000) NOT NULL,
  94.   `Data_Item` INT UNSIGNED NULL,
  95.   `Status` INT UNSIGNED NOT NULL DEFAULT 'Created',
  96.   `MultiStep_Dialogue` INT UNSIGNED NOT NULL,
  97.   `Comments` VARCHAR(1000) NULL,
  98.   `Previous_Node_Links` VARCHAR(1000) NULL,
  99.   `Next_Node_Link` VARCHAR(100) NULL,
  100.   PRIMARY KEY (`ID`),
  101.   INDEX `fk_Nodes_MultiStep_Dialogues_idx` (`MultiStep_Dialogue` ASC) VISIBLE,
  102.   INDEX `fk_Nodes_Node_Types1_idx` (`Node_Type` ASC) VISIBLE,
  103.   INDEX `fk_Nodes_Variables1_idx` (`Data_Item` ASC) VISIBLE,
  104.   INDEX `fk_Nodes_Status1_idx` (`Status` ASC) VISIBLE,
  105.   CONSTRAINT `fk_Nodes_MultiStep_Dialogues`
  106.     FOREIGN KEY (`MultiStep_Dialogue`)
  107.     REFERENCES `DialogueFlowDB`.`MultiStep_Dialogues` (`ID`)
  108.     ON DELETE NO ACTION
  109.     ON UPDATE NO ACTION,
  110.   CONSTRAINT `fk_Nodes_Node_Types1`
  111.     FOREIGN KEY (`Node_Type`)
  112.     REFERENCES `DialogueFlowDB`.`Node_Types` (`ID`)
  113.     ON DELETE NO ACTION
  114.     ON UPDATE NO ACTION,
  115.   CONSTRAINT `fk_Nodes_Status1`
  116.     FOREIGN KEY (`Status`)
  117.     REFERENCES `DialogueFlowDB`.`Status` (`ID`)
  118.     ON DELETE NO ACTION
  119.     ON UPDATE NO ACTION,
  120.   CONSTRAINT `fk_Nodes_Variables1`
  121.     FOREIGN KEY (`Data_Item`)
  122.     REFERENCES `DialogueFlowDB`.`Variables` (`ID`)
  123.     ON DELETE NO ACTION
  124.     ON UPDATE NO ACTION)
  125. ENGINE = InnoDB;
  127. -- -----------------------------------------------------
  128. -- Table `DialogueFlowDB`.`Node_Links`
  129. -- -----------------------------------------------------
  130. CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`Node_Links` (
  131.   `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  132.   `Node_Link` CHAR(255) NOT NULL,
  133.   `MultiStep_Dialogue` INT UNSIGNED NOT NULL,
  134.   `From_Node` INT UNSIGNED NOT NULL,
  135.   `To_Node` INT UNSIGNED NOT NULL,
  136.   `Status` INT UNSIGNED NOT NULL,
  137.   `Comments` VARCHAR(1000) NULL,
  138.   PRIMARY KEY (`ID`),
  139.   INDEX `fk_Node_Links_Nodes1_idx` (`From_Node` ASC) VISIBLE,
  140.   INDEX `fk_Node_Links_MultiStep_Dialogues1_idx` (`MultiStep_Dialogue` ASC) VISIBLE,
  141.   INDEX `fk_Node_Links_Nodes2_idx` (`To_Node` ASC) VISIBLE,
  142.   INDEX `fk_Node_Links_Status1_idx` (`Status` ASC) VISIBLE,
  143.   CONSTRAINT `fk_Node_Links_Nodes1`
  144.     FOREIGN KEY (`From_Node`)
  145.     REFERENCES `DialogueFlowDB`.`Nodes` (`ID`)
  146.     ON DELETE NO ACTION
  147.     ON UPDATE NO ACTION,
  148.   CONSTRAINT `fk_Node_Links_MultiStep_Dialogues1`
  149.     FOREIGN KEY (`MultiStep_Dialogue`)
  150.     REFERENCES `DialogueFlowDB`.`MultiStep_Dialogues` (`ID`)
  151.     ON DELETE NO ACTION
  152.     ON UPDATE NO ACTION,
  153.   CONSTRAINT `fk_Node_Links_Nodes2`
  154.     FOREIGN KEY (`To_Node`)
  155.     REFERENCES `DialogueFlowDB`.`Nodes` (`ID`)
  156.     ON DELETE NO ACTION
  157.     ON UPDATE NO ACTION,
  158.   CONSTRAINT `fk_Node_Links_Status1`
  159.     FOREIGN KEY (`Status`)
  160.     REFERENCES `DialogueFlowDB`.`Status` (`ID`)
  161.     ON DELETE NO ACTION
  162.     ON UPDATE NO ACTION)
  163. ENGINE = InnoDB;
  165. SET SQL_MODE=@OLD_SQL_MODE;
  166. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  167. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

这是输出：

  1. Executing SQL script in server
  2. ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '
  3.   CONSTRAINT `fk_MultiStep_Dialogues_Status1`
  4.     FOREIGN KEY (`Status`)
  5.     RE' at line 19
  6. SQL Code:
  7.         -- -----------------------------------------------------
  8.         -- Table `DialogueFlowDB`.`MultiStep_Dialogues`
  9.         -- -----------------------------------------------------
  10.         CREATE TABLE IF NOT EXISTS `DialogueFlowDB`.`MultiStep_Dialogues` (
  11.           `ID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  12.           `MultiStep_Dialogue` CHAR(255) NOT NULL,
  13.           `Data_Items` VARCHAR(1000) NULL,
  14.           `Nodes` VARCHAR(1000) NULL,
  15.           `Node_Links` VARCHAR(1000) NULL,
  16.           `Comments` VARCHAR(1000) NULL,
  17.           `Status` INT UNSIGNED NOT NULL,
  18.           `Issue` INT NOT NULL,
  19.           `LO_Name` CHAR(255) NULL,
  20.           `LO_Description` CHAR(255) NULL,
  21.           `LO_Condition` VARCHAR(1000) NULL,
  22.           `LO_Variables` VARCHAR(1000) NULL,
  23.           `LO_List` INT NULL,
  24.           PRIMARY KEY (`ID`),
  25.           INDEX `fk_MultiStep_Dialogues_Status1_idx` (`Status` ASC) VISIBLE,
  26.           CONSTRAINT `fk_MultiStep_Dialogues_Status1`
  27.             FOREIGN KEY (`Status`)
  28.             REFERENCES `DialogueFlowDB`.`Status` (`ID`)
  29.             ON DELETE NO ACTION
  30.             ON UPDATE NO ACTION)
  31.         ENGINE = InnoDB
  33. SQL script execution finished: statements: 6 succeeded, 1 failed
  35. Fetching back view definitions in final form.
  36. Nothing to fetch

我在网上看到，问题可能与外键有关（尽管我检查过了，看起来不错……），或者与engine=innodb声明有关。我试着用这个问题的答案，但没有成功，或者我不太明白答案（我交换了engine=innodb；使用engine=innodb default charset=utf8 collate=utf8\u unicode\u ci；）我还检查了我的mysql版本，我使用10.1.35-mariadb，它应该有不可见的索引可用（如我链接的问题的另一个答案所述）。
我没有mysql的经验，所以如果这是一个非常简单的问题，我很抱歉。感谢您的帮助：）

mysqlDatabasemysql-workbenchforward-engineer

1条答案

按热度按时间
ep6jt1vc

ep6jt1vc1#

好吧，有两个不同的问题。dialogueflow表中无符号int“status”的默认值是“created”（我从使用枚举改为使用外键，忘记删除旧的默认值），所以 Status int unsigned not null默认值“created”
另一个问题似乎与索引有关。当移除所有组件时：
索引 fk_MultiStep_Dialogues_Status1_idx ( Status asc）可见
索引 fk_Nodes_MultiStep_Dialogues_idx ( MultiStep_Dialogue asc）可见，
索引 fk_Nodes_Node_Types1_idx ( Node_Type asc）可见，
诸如此类，似乎奏效了。我真的不明白为什么会这样，但现在我将删除外键上的索引，并在以后修复它。

赞(0）分享  回复(0）举报  2021-06-18
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com