MySQL Workbench和phpMyadmin [英] MySQL Workbench and phpMyadmin

问题描述

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

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 Workbench和phpMyadmin的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！