MySQL 8.0.31转换数据库至utf8mb4时SQL语法错误求助

尝试将hbtn_0c_0数据库、first_table表及其中的name字段转换为utf8mb4（排序规则为utf8mb4_unicode_ci），使用的SQL脚本如下：

-- Write a script that converts hbtn_0c_0 database to UTF8
-- (utf8mb4, collate utf8mb4_unicode_ci) in your MySQL server.

-- You need to convert all of the following to UTF8:

--     Database hbtn_0c_0
--     Table first_table
--     Field name in first_table

ALTER DATABASE
      `hbtn_0c_0`
      CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci;

USE `hbtn_0c_0`;

ALTER TABLE
      `first_table`
      CONVERT TO CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci;

ALTER TABLE
      `first_table`
      CHANGE `name`
      VARCHAR(256)
      CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci;

执行脚本时出现SQL语法错误，命令及错误信息如下：

black_genius@genius:~/Documents/ALX_Task/alx-higher_level_programming/0x0D-SQL_introduction$ cat 100-move_to_utf8.sql | mysql -hlocalhost -uroot -p 
Enter password: 
ERROR 1064 (42000) at line 22: 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 'VARCHAR(256)
      CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci' at line 4

环境：Ubuntu 22.10，MySQL v8.0.31

错误原因是ALTER TABLE ... CHANGE语句的语法不符合要求。CHANGE关键字需要重复指定字段名（即使字段名不修改），正确的语法结构是：

ALTER TABLE table_name CHANGE old_column_name new_column_name column_definition;

你的脚本中，CHANGE后只写了原字段名name，没有重复写字段名作为新字段名，导致MySQL无法解析语法。

修正后的完整脚本如下：

-- Write a script that converts hbtn_0c_0 database to UTF8
-- (utf8mb4, collate utf8mb4_unicode_ci) in your MySQL server.

-- You need to convert all of the following to UTF8:

--     Database hbtn_0c_0
--     Table first_table
--     Field name in first_table

ALTER DATABASE
      `hbtn_0c_0`
      CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci;

USE `hbtn_0c_0`;

ALTER TABLE
      `first_table`
      CONVERT TO CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci;

ALTER TABLE
      `first_table`
      CHANGE `name` `name`
      VARCHAR(256)
      CHARACTER SET utf8mb4
      COLLATE utf8mb4_unicode_ci;

额外说明：执行ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4已经会将表中所有字符类型字段的字符集和排序规则转换为指定值，所以单独修改name字段的语句其实可以省略。如果需要保留该语句，确保语法正确即可。

内容的提问来源于stack exchange，提问作者Dev Beginner