MySQL 工作台:查询错误 (1064):第 1 行“可见"附近的语法错误

2021-11-20 00:00:00 mysql mysql-workbench

任何想法为什么下面的 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;

错误:

CREATE UNIQUE INDEX name_UNIQUE ON setting (code ASC) VISIBLE查询错误 (1064):第 1 行可见"附近的语法错误

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

CREATE UNIQUE INDEX uuid_UNIQUE ON setting (uuid ASC) VISIBLE查询错误 (1064):第 1 行可见"附近的语法错误

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}

然而，{VISIBLE |的这个选项INVISIBLE} 在 MySQL Server 5.7 中不可用.来自文档:

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 工作台中:

In MySQL Workbench:

前往:

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

Edit > Preferences > Modeling > MySQL.

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

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

检查下面的屏幕截图:

相关文章