MySQL 工作台和 phpMyadmin [英] MySQL Workbench and phpMyadmin

问题描述

1064 - 您的 SQL 语法有错误；检查手册对应于您的 MySQL 服务器版本以使用正确的语法'VISIBLE, CONSTRAINT fk_TeamStatistik_Team 附近外键 (Team_id) ' 在第 10 行

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 'VISIBLE, CONSTRAINT fk_TeamStatistik_Team FOREIGN KEY (Team_id) ' at line 10

我使用mysql工作台创建了我的数据库并出现了这个错误，下面我发布了由mysql工作台生成的sql代码...当我试图在phpmyadmin中输入sql代码时出现了这个错误.任何人都可以提供帮助?提前致谢.

I created my database using mysql workbench and got this error, below I posted the sql code generated by mysql workbench...I got this error as i tried to input the sql code in phpmyadmin. Anyone that can help? Thanks in advance.

-- MySQL Script generated by MySQL Workbench
-- Sun Oct 21 14:37:37 2018
-- Model: New Model    Version: 1.0
-- 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='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema dbpws18db16
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema dbpws18db16
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `dbpws18db16` DEFAULT CHARACTER SET utf8 ;
USE `dbpws18db16` ;

-- -----------------------------------------------------
-- Table `dbpws18db16`.`Team`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dbpws18db16`.`Team` ;

CREATE TABLE IF NOT EXISTS `dbpws18db16`.`Team` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(45) NULL,
  `Stadt` VARCHAR(45) NULL,
  `Staat` VARCHAR(45) NULL,
  `Besitzer` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `dbpws18db16`.`Spieler`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dbpws18db16`.`Spieler` ;

CREATE TABLE IF NOT EXISTS `dbpws18db16`.`Spieler` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `Alter` INT NULL,
  `Vorname` VARCHAR(45) NULL,
  `Nachname` VARCHAR(45) NULL,
  `Position` VARCHAR(45) NULL,
  `Größe` DECIMAL NULL,
  `Team_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Spieler_Team1_idx` (`Team_id` ASC) VISIBLE,
  CONSTRAINT `fk_Spieler_Team1`
    FOREIGN KEY (`Team_id`)
    REFERENCES `dbpws18db16`.`Team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `dbpws18db16`.`Individuelle Statistik`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dbpws18db16`.`Individuelle Statistik` ;

CREATE TABLE IF NOT EXISTS `dbpws18db16`.`Individuelle Statistik` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `PPG` VARCHAR(45) NULL,
  `RPG` VARCHAR(45) NULL,
  `SPG` VARCHAR(45) NULL,
  `APG` VARCHAR(45) NULL,
  `GP` VARCHAR(45) NULL,
  `BPG` VARCHAR(45) NULL,
  `FG` INT NULL,
  `Spieler_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Individuelle Statistik_Spieler1_idx` (`Spieler_id` ASC) VISIBLE,
  CONSTRAINT `fk_Individuelle Statistik_Spieler1`
    FOREIGN KEY (`Spieler_id`)
    REFERENCES `dbpws18db16`.`Spieler` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `dbpws18db16`.`Saison`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dbpws18db16`.`Saison` ;

CREATE TABLE IF NOT EXISTS `dbpws18db16`.`Saison` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `Beginn` DATETIME NULL,
  `Ende` DATETIME NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `dbpws18db16`.`Spiele`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dbpws18db16`.`Spiele` ;

CREATE TABLE IF NOT EXISTS `dbpws18db16`.`Spiele` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `hd` VARCHAR(45) NULL,
  `Spielecol` VARCHAR(45) NULL,
  `Saison_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_Spiele_Saison1_idx` (`Saison_id` ASC) VISIBLE,
  CONSTRAINT `fk_Spiele_Saison1`
    FOREIGN KEY (`Saison_id`)
    REFERENCES `dbpws18db16`.`Saison` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `dbpws18db16`.`TeamStatistik`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dbpws18db16`.`TeamStatistik` ;

CREATE TABLE IF NOT EXISTS `dbpws18db16`.`TeamStatistik` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `BPG` DECIMAL NULL,
  `APG` DECIMAL NULL,
  `SPG` DECIMAL NULL,
  `GP` INT NULL,
  `PPG` DECIMAL NULL,
  `RPG` DECIMAL NULL,
  `Team_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_TeamStatistik_Team_idx` (`Team_id` ASC) VISIBLE,
  CONSTRAINT `fk_TeamStatistik_Team`
    FOREIGN KEY (`Team_id`)
    REFERENCES `dbpws18db16`.`Team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `dbpws18db16`.`Team_has_Spiele`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `dbpws18db16`.`Team_has_Spiele` ;

CREATE TABLE IF NOT EXISTS `dbpws18db16`.`Team_has_Spiele` (
  `Team_id` INT NOT NULL,
  `Spiele_id` INT NOT NULL,
  PRIMARY KEY (`Team_id`, `Spiele_id`),
  INDEX `fk_Team_has_Spiele_Spiele1_idx` (`Spiele_id` ASC) VISIBLE,
  INDEX `fk_Team_has_Spiele_Team1_idx` (`Team_id` ASC) VISIBLE,
  CONSTRAINT `fk_Team_has_Spiele_Team1`
    FOREIGN KEY (`Team_id`)
    REFERENCES `dbpws18db16`.`Team` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_Team_has_Spiele_Spiele1`
    FOREIGN KEY (`Spiele_id`)
    REFERENCES `dbpws18db16`.`Spiele` (`id`)
    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;

-- -----------------------------------------------------
-- Data for table `dbpws18db16`.`Team`
-- -----------------------------------------------------
START TRANSACTION;
USE `dbpws18db16`;
INSERT INTO `dbpws18db16`.`Team` (`id`, `Name`, `Stadt`, `Staat`, `Besitzer`) VALUES (1, 'abc', 'cf', 'dd', 'ddd');

COMMIT;


-- -----------------------------------------------------
-- Data for table `dbpws18db16`.`Spieler`
-- -----------------------------------------------------
START TRANSACTION;
USE `dbpws18db16`;
INSERT INTO `dbpws18db16`.`Spieler` (`id`, `Alter`, `Vorname`, `Nachname`, `Position`, `Größe`, `Team_id`) VALUES (1, 12, 'cdcrc', 'crcr', 'crc', 1.89, DEFAULT);

COMMIT;


-- -----------------------------------------------------
-- Data for table `dbpws18db16`.`Individuelle Statistik`
-- -----------------------------------------------------
START TRANSACTION;
USE `dbpws18db16`;
INSERT INTO `dbpws18db16`.`Individuelle Statistik` (`id`, `PPG`, `RPG`, `SPG`, `APG`, `GP`, `BPG`, `FG`, `Spieler_id`) VALUES (1, '2', '2', '2', '2', '2', NULL, NULL, DEFAULT);

COMMIT;


-- -----------------------------------------------------
-- Data for table `dbpws18db16`.`Saison`
-- -----------------------------------------------------
START TRANSACTION;
USE `dbpws18db16`;
INSERT INTO `dbpws18db16`.`Saison` (`id`, `Beginn`, `Ende`) VALUES (1, '01.04.2018', '01.04.2019');

COMMIT;


-- -----------------------------------------------------
-- Data for table `dbpws18db16`.`Spiele`
-- -----------------------------------------------------
START TRANSACTION;
USE `dbpws18db16`;
INSERT INTO `dbpws18db16`.`Spiele` (`id`, `hd`, `Spielecol`, `Saison_id`) VALUES (1, 'dw', 'de', DEFAULT);
INSERT INTO `dbpws18db16`.`Spiele` (`id`, `hd`, `Spielecol`, `Saison_id`) VALUES (, NULL, NULL, DEFAULT);

COMMIT;


-- -----------------------------------------------------
-- Data for table `dbpws18db16`.`TeamStatistik`
-- -----------------------------------------------------
START TRANSACTION;
USE `dbpws18db16`;
INSERT INTO `dbpws18db16`.`TeamStatistik` (`id`, `BPG`, `APG`, `SPG`, `GP`, `PPG`, `RPG`, `Team_id`) VALUES (1, 2.3, 2.2., 2.2, 2.2, 2.2, 2.2, DEFAULT);
INSERT INTO `dbpws18db16`.`TeamStatistik` (`id`, `BPG`, `APG`, `SPG`, `GP`, `PPG`, `RPG`, `Team_id`) VALUES (2, 3, 4, 3, 4, 4, 4, DEFAULT);

COMMIT;

推荐答案

这里的问题是不同 MySQL 服务器版本的语法差异.您的 MySQL 工作台版本似乎是 8.0 及更高版本.自动生成的代码适用于MySQL服务器8.0版.

The problem here is the difference in syntax across different MySQL server versions. It seems that your MySQL workbench version is 8.0 and above. The code which it is auto-generating is applicable for the MySQL server version 8.0.

您需要将 MySQL 服务器 版本升级到 8.0 及更高版本.或者，您可以从所有位置(正在定义索引的位置)删除 VISIBLE 关键字，如下所示:

You will need to either upgrade your MySQL server version to 8.0 and above. Or, you can remove the VISIBLE keyword from all the places (where Index is being defined), like below:

INDEX `fk_TeamStatistik_Team_idx` (`Team_id` ASC) VISIBLE, -- <-- remove VISIBLE

到

INDEX `fk_TeamStatistik_Team_idx` (`Team_id` ASC),

您也需要在查询的其他部分做同样的事情.

You will need to do the same thing at other parts of your queries as well.

其他详细信息

来自 MySQL Server 8.0 文档，CREATE INDEX 的语法是:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part: {col_name [(length)] | (expr)} [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'
  | {VISIBLE | INVISIBLE}  -- Notice the option of VISIBLE / INVISIBLE

index_type:
  USING {BTREE | HASH}

然而，{VISIBLE |的这个选项INVISIBLE} 在 MySQL Server 5.5 中不可用(您的服务器版本).来自文档:

However, this option of {VISIBLE | INVISIBLE} is not available in the MySQL Server 5.5 (your Server version). From Docs:

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (key_part,...)
    [index_option]
    [algorithm_option | lock_option] ...

key_part:
    col_name [(length)] [ASC | DESC]

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

index_type:
    USING {BTREE | HASH}

这篇关于MySQL 工作台和 phpMyadmin的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！