mysql - Simple column change query doesn' work in MySQL 5.7
问题描述
I have an update query created by MySQL Workbench 8 and it looks as follows:
-- MySQL Workbench Synchronization
-- Generated: 2019-12-16 00:30
-- Model: New Model
-- Version: 1.0
-- Project: Name of the project
-- Author: user0
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='ALLOW_INVALID_DATES';
ALTER TABLE `best_bpgallery`.`k96qw_bpgallery_images`
CHANGE COLUMN `language` `language` NCHAR(7) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL DEFAULT '*' ;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
It looks perfectly fine for me, but when I run it fails:
Executing SQL script in server
ERROR: Error 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 'CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL DEFAULT '*'' at line 2
SQL Code:
ALTER TABLE `best_bpgallery`.`k96qw_bpgallery_images`
CHANGE COLUMN `language` `language` NCHAR(7) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL DEFAULT '*'
SQL script execution finished: statements: 3 succeeded, 1 failed
Fetching back view definitions in final form.
Nothing to fetch
I even compared the query to ALTER TABLE documentation and it looks fine for me. So what's going on? What am I missing?
解决方案
例如,您必须将 nchar 更改为 varchar。
ALTER TABLE `best_bpgallery`.`k96qw_bpgallery_images`
CHANGE COLUMN `language` `language` VARCHAR(7) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_unicode_ci' NOT NULL DEFAULT '*' ;
nchar 不支持字符集
推荐阅读
- php - 想对 php 中组合的 3 个数组使用 foreach 循环
- spring-boot - 两个日期的 Spring Boot 重叠
- tensorflow - 在 jupyter 环境中导入 tensorflow 时出错
- r - 为每个站点排序前 n 个值的列名并在数据框中生成结果
- python - 在损失函数中使用 autograd 时 PyTorch 不更新权重
- flutter - 如何更改 PopScreen 动画的持续时间?
- spring-boot - 基于控制器在 Spring Boot 中隐藏/显示 Json 响应中的字段
- python - 如何在numpy中获得由另外两列分组的列的部分累积总和?
- python - QLineEdit 中的自动格式化字节数组
- shopware - Shopware 6.3 - 如何在将商店从一个域转移到另一个域时更改域名