MySQL Workbench:查询错误(1064):第1行"VISIBLE"附近的语法错误 [英] MySQL Workbench: Error in query (1064): Syntax error near 'VISIBLE' at line 1

问题描述

有什么想法为什么下面的VISIBLE会引起问题?

Any ideas why VISIBLE below is causing an issue?

CREATE TABLE IF NOT EXISTS `setting` (
  `uuid` INT(10) NOT NULL,
  `type` VARCHAR(255) NOT NULL,
  `code` VARCHAR(255) NOT NULL COMMENT 'An unique name.',
  `value` MEDIUMTEXT NULL DEFAULT NULL,
  `comment` LONGTEXT NULL DEFAULT NULL,
  `created_on` INT UNSIGNED NOT NULL,
  `updated_on` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`uuid`))
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8;

CREATE UNIQUE INDEX `name_UNIQUE` ON `setting` (`code` ASC) VISIBLE;

CREATE UNIQUE INDEX `uuid_UNIQUE` ON `setting` (`uuid` ASC) VISIBLE;

错误:

在setting(code ASC)上创建唯一索引name_UNIQUE 查询错误(1064):第1行"VISIBLE"附近的语法错误

CREATE UNIQUE INDEX name_UNIQUE ON setting (code ASC) VISIBLE Error in query (1064): Syntax error near 'VISIBLE' at line 1

在setting(uuid ASC)上创建唯一索引uuid_UNIQUE 查询错误(1064):第1行"VISIBLE"附近的语法错误

CREATE UNIQUE INDEX uuid_UNIQUE ON setting (uuid ASC) VISIBLE Error in query (1064): Syntax error near 'VISIBLE' at line 1

如果删除VISIBLE，但 MySQL Workbench 8.0.12 自动生成该文件，则没有错误.我该如何阻止MySQL Workbench?

No error if I remove VISIBLE but MySQL Workbench 8.0.12 auto generates that. How can I stop MySQL Workbench from doing that?

我的Ubuntu 18.04中的MySQL信息:

My MySQL info in my Ubuntu 18.04:

MySQL版本:5.7.23-0ubuntu0.18.04.1通过PHP扩展MySQLi

MySQL version: 5.7.23-0ubuntu0.18.04.1 through PHP extension MySQLi

推荐答案

此处的问题是不同MySQL服务器版本之间的语法差异.似乎 MySQL Workbench 8.0.12 正在为MySQL服务器版本8.0 自动生成CREATE UNIQUE INDEX语句.

The problem here is the difference in syntax across different MySQL server versions. It seems that MySQL Workbench 8.0.12 is auto-generating CREATE UNIQUE INDEX statement for the MySQL server version 8.0.

从 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}

但是， MySQL Server 5.7 中不提供此{VISIBLE | INVISIBLE}选项.来自文档:

However, this option of {VISIBLE | INVISIBLE} is not available in the MySQL Server 5.7. 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'   /* No option of VISIBLE / INVISIBLE */

index_type:
    USING {BTREE | HASH}

---

如果您不希望升级到最新版本的MySQL，请执行以下操作:您可以禁用使用VISIBLE / INVISIBLE索引自动生成的功能:

---

If you are not looking to upgrade to latest version of MySQL; you can disable this feature of auto-generating with VISIBLE / INVISIBLE index:

在MySQL Workbench中:

In MySQL Workbench:

转到:

编辑>首选项>建模> MySQL.

Edit > Preferences > Modeling > MySQL.

然后，将默认目标MySQL版本"设置为 5.7

Then, set the "Default Target MySQL Version" to 5.7

检查以下屏幕截图:

这篇关于MySQL Workbench:查询错误(1064):第1行"VISIBLE"附近的语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！