MySQL Workbench and phpMyadmin

2020-02-16 01:19发布

站内文章 / MySQL
95 0 0

问题:

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

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;

回答1:

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.

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

to

INDEX `fk_TeamStatistik_Team_idx` (`Team_id` ASC),

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

Additional Details

From the MySQL Server 8.0 Docs, the syntax for CREATE INDEX is:

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}

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}


回答2:

MySQL Workbench allows you to set the MySQL target version it uses when generating a schema.

Edit / Preferences / Modeling / MySQL / set the *Default Target MySQL Version" to 5.5.60.

It will then refrain from using new Version 8 features when it generates SQL for you.



标签： mysql phpmyadmin workbench
举报

收藏的人(0)

Ta的文章 更多文章
登录 后发表评论
0条评论
还没有人评论过~