mysql - 转发工程师我的架构时出现错误 1064

Question

我想对我的基础进行正向工程，但我再次遇到此错误。这是我的 sql 代码，接下来是我得到的错误屏幕:

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema imdb_schema
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema imdb_schema
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `imdb_schema` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `imdb_schema` ;

-- -----------------------------------------------------
-- Table `imdb_schema`.`Multimedia`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Multimedia` (
  `id_multi` INT UNSIGNED NOT NULL,
  `title_multi` VARCHAR(45) NOT NULL,
  `category` VARCHAR(45) NOT NULL,
  `release_date` DATE NOT NULL,
  `language` VARCHAR(45) NULL,
  `country_origin` VARCHAR(45) NULL,
  `official_website` VARCHAR(45) NULL,
  `plot` LONGTEXT NOT NULL,
  `bloopers` MEDIUMTEXT NULL,
  `technical_characteristics` VARCHAR(45) NULL,
  `trailer_url` VARCHAR(45) NULL,
  `soundtrack` MEDIUMTEXT NULL,
  PRIMARY KEY (`id_multi`),
  UNIQUE INDEX `id_multi` (`id_multi` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Registered_Users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Registered_Users` (
  `email` VARCHAR(40) NOT NULL,
  `username` VARCHAR(15) NOT NULL,
  `password` VARCHAR(15) NOT NULL,
  `profile_picture` LONGBLOB NULL,
  `gender` VARCHAR(45) BINARY NULL,
  PRIMARY KEY (`email`),
  UNIQUE INDEX `username_UNIQUE` (`username` ASC))
ENGINE = InnoDB
COMMENT = '             ';


-- -----------------------------------------------------
-- Table `imdb_schema`.`VideoGames`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`VideoGames` (
  `Console` VARCHAR(20) NOT NULL,
  `id_multi` INT NOT NULL,
  PRIMARY KEY (`id_multi`),
  CONSTRAINT `id_multi`
    FOREIGN KEY ()
    REFERENCES `imdb_schema`.`Multimedia` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`TV_Series`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`TV_Series` (
  `tv_station` VARCHAR(20) NOT NULL,
  `episode_number` INT NOT NULL,
  `season_number` INT NOT NULL,
  `id_multi` INT NOT NULL,
  `serie_duration` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id_multi`),
  CONSTRAINT `id_multi`
    FOREIGN KEY ()
    REFERENCES `imdb_schema`.`Multimedia` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Episodes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Episodes` (
  `episode_number` INT NOT NULL,
  `season_number` INT NOT NULL,
  PRIMARY KEY (`episode_number`, `season_number`),
  CONSTRAINT `id_multi`
    FOREIGN KEY ()
    REFERENCES `imdb_schema`.`TV_Series` ()
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Premium`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Premium` (
  `email` VARCHAR(30) NOT NULL,
  `subscription_duration` VARCHAR(45) NOT NULL,
  `occupation` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`email`),
  CONSTRAINT `email`
    FOREIGN KEY ()
    REFERENCES `imdb_schema`.`Registered_Users` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Prof_Profile`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Prof_Profile` (
  `name` VARCHAR(30) NOT NULL,
  `surname` VARCHAR(30) NOT NULL,
  `DOB` DATE NOT NULL,
  `profile_picture` LONGBLOB NULL,
  `personal_url` VARCHAR(45) NULL,
  PRIMARY KEY (`name`, `surname`),
  CONSTRAINT `email`
    FOREIGN KEY ()
    REFERENCES `imdb_schema`.`Premium` ()
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Comments`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Comments` (
  `email` VARCHAR(30) NOT NULL,
  `id_multi` INT NOT NULL,
  `comment_date` DATE NOT NULL,
  `comment_text` LONGTEXT NOT NULL,
  PRIMARY KEY (`email`, `id_multi`),
  INDEX `fk_Registered_Users_has_Multimedia_Multimedia1_idx` (`id_multi` ASC),
  INDEX `fk_Registered_Users_has_Multimedia_Registered_Users1_idx` (`email` ASC),
  CONSTRAINT `fk_Registered_Users_has_Multimedia_Registered_Users1`
    FOREIGN KEY (`email`)
    REFERENCES `imdb_schema`.`Registered_Users` (`email`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Registered_Users_has_Multimedia_Multimedia1`
    FOREIGN KEY (`id_multi`)
    REFERENCES `imdb_schema`.`Multimedia` (`id_multi`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Rates`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Rates` (
  `email` VARCHAR(30) NOT NULL,
  `id_multi` INT NOT NULL,
  `rating` FLOAT NOT NULL,
  PRIMARY KEY (`email`, `id_multi`),
  INDEX `fk_Registered_Users_has_Multimedia_Multimedia2_idx` (`id_multi` ASC),
  INDEX `fk_Registered_Users_has_Multimedia_Registered_Users2_idx` (`email` ASC),
  CONSTRAINT `fk_Registered_Users_has_Multimedia_Registered_Users2`
    FOREIGN KEY (`email`)
    REFERENCES `imdb_schema`.`Registered_Users` (`email`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Registered_Users_has_Multimedia_Multimedia2`
    FOREIGN KEY (`id_multi`)
    REFERENCES `imdb_schema`.`Multimedia` (`id_multi`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Contributes`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Contributes` (
  `email` VARCHAR(30) NOT NULL,
  `id_multi` INT NOT NULL,
  `type_contribution` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`email`, `id_multi`),
  INDEX `fk_Registered_Users_has_Multimedia_Multimedia3_idx` (`id_multi` ASC),
  INDEX `fk_Registered_Users_has_Multimedia_Registered_Users3_idx` (`email` ASC),
  CONSTRAINT `fk_Registered_Users_has_Multimedia_Registered_Users3`
    FOREIGN KEY (`email`)
    REFERENCES `imdb_schema`.`Registered_Users` (`email`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Registered_Users_has_Multimedia_Multimedia3`
    FOREIGN KEY (`id_multi`)
    REFERENCES `imdb_schema`.`Multimedia` (`id_multi`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Personal_List`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Personal_List` (
  `list_tittle` VARCHAR(20) NOT NULL,
  `id_multi` INT NOT NULL,
  PRIMARY KEY (`list_tittle`, `id_multi`),
  CONSTRAINT `email`
    FOREIGN KEY ()
    REFERENCES `imdb_schema`.`Registered_Users` ()
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`List`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`List` (
  `id_multi` INT NOT NULL)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Award`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Award` (
  `award_institution` VARCHAR(20) NOT NULL,
  `award_title` VARCHAR(30) NOT NULL,
  `award_year` YEAR NOT NULL,
  PRIMARY KEY (`award_institution`, `award_title`, `award_year`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Cast`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Cast` (
  `id_cast` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `surname` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id_cast`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Crew`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Crew` (
  `id_crew` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `surname` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id_crew`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Stars_in`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Stars_in` (
  `id_cast` INT NOT NULL,
  `id_multi` INT NOT NULL,
  `role` VARCHAR(45) NOT NULL,
  `description` LONGTEXT NOT NULL,
  PRIMARY KEY (`id_cast`, `id_multi`),
  INDEX `fk_Cast_has_Multimedia_Multimedia1_idx` (`id_multi` ASC),
  INDEX `fk_Cast_has_Multimedia_Cast1_idx` (`id_cast` ASC),
  CONSTRAINT `fk_Cast_has_Multimedia_Cast1`
    FOREIGN KEY (`id_cast`)
    REFERENCES `imdb_schema`.`Cast` (`id_cast`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Cast_has_Multimedia_Multimedia1`
    FOREIGN KEY (`id_multi`)
    REFERENCES `imdb_schema`.`Multimedia` (`id_multi`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Occupation_Categories`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Occupation_Categories` (
  `title_occuption` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`title_occuption`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `imdb_schema`.`Takes_part`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Takes_part` (
  `id_crew` INT NOT NULL,
  `id_multi` INT NOT NULL,
  `title_occupation` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id_crew`, `id_multi`, `title_occupation`),
  INDEX `fk_Crew_has_Multimedia_Multimedia1_idx` (`id_multi` ASC),
  INDEX `title_occupation_idx` (`title_occupation` ASC),
  CONSTRAINT `fk_Crew_has_Multimedia_Crew1`
    FOREIGN KEY (`id_crew`)
    REFERENCES `imdb_schema`.`Crew` (`id_crew`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Crew_has_Multimedia_Multimedia1`
    FOREIGN KEY (`id_multi`)
    REFERENCES `imdb_schema`.`Multimedia` (`id_multi`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `title_occupation`
    FOREIGN KEY (`title_occupation`)
    REFERENCES `imdb_schema`.`Occupation_Categories` (`title_occuption`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

错误屏幕:

Executing SQL script in server ERROR: Error 1064: 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 ') REFERENCES imdb_schema.Multimedia () ON DELETE CASCADE ON UPDA' at line 9

SQL 代码:

-- -----------------------------------------------------
-- Table `imdb_schema`.`Movies`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`Movies` (
  `id_multi` INT NOT NULL,
  `movie_duration` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id_multi`),
  CONSTRAINT `id_multi`
    FOREIGN KEY ()
    REFERENCES `imdb_schema`.`Multimedia` ()
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

SQL script execution finished: statements: 7 succeeded, 1 failed

Fetching back view definitions in final form. Nothing to fetch Executing SQL script in server SQL script execution finished: statements: 6 succeeded, 0 failed

Fetching back view definitions in final form. Nothing to fetch Executing SQL script in server ERROR: Error 1064: 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 ') REFERENCES imdb_schema.Multimedia () ON DELETE NO ACTION ON UP' at line 9

SQL 代码:

-- -----------------------------------------------------
-- Table `imdb_schema`.`VideoGames`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`VideoGames` (
  `Console` VARCHAR(20) NOT NULL,
  `id_multi` INT NOT NULL,
  PRIMARY KEY (`id_multi`),
  CONSTRAINT `id_multi`
    FOREIGN KEY ()
    REFERENCES `imdb_schema`.`Multimedia` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL script execution finished: statements: 7 succeeded, 1 failed

Fetching back view definitions in final form. Nothing to fetch Executing SQL script in server ERROR: Error 1064: 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 ') REFERENCES imdb_schema.Multimedia () ON DELETE NO ACTION ON UP' at line 9

SQL 代码:

-- -----------------------------------------------------
-- Table `imdb_schema`.`VideoGames`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `imdb_schema`.`VideoGames` (
  `Console` VARCHAR(20) NOT NULL,
  `id_multi` INT NOT NULL,
  PRIMARY KEY (`id_multi`),
  CONSTRAINT `id_multi`
    FOREIGN KEY ()
    REFERENCES `imdb_schema`.`Multimedia` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

SQL script execution finished: statements: 7 succeeded, 1 failed

Fetching back view definitions in final form. Nothing to fetch

Answer 1

我怀疑您正在使用某种软件工具(SQL Workbench 或类似的东西)来进行所谓的正向工程。通常的工作方式是该工具生成 SQL，然后通过数据库连接将其传递到 DBMS。

您的工具正在生成有缺陷的 SQL 数据定义语言 (DDL) 代码。这不好。是时候停止信任该工具了。

@Michael Berkowski 诊断出您的 DDL 中的缺陷。在外键定义中有 ()，MySQL 的解析器需要列名。

在彻底废弃该工具之前，您可以回顾一下如何使用它来定义实体之间的关系。当您将设计正向工程到数据库服务器中时，这些关系会转化为外键。在 SQL 中查找外键定义包含 () 的表。这些看起来可能是设计错误的关系。

专业提示。对于错误 1064，标准 MySQL 错误消息总是这样表示:

...for the right syntax to use near 'blah blah blah...

单引号中的文本以 MySQL 无法理解的 SQL 的第一个字符开始。在你的例子中，它以 ) 开头，这意味着 SQL 中的一些右括号导致 MySQL 服务器呕吐。