Convert database to utf8-CodePudding

Hello I'm trying to convert my database, one table and field to utf using this script

-- 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;

But I have a SQL error. Please help me

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

I'm using mysql version v8.0.31 on ubuntu 22.10

CodePudding user response：

When you change a column, you need to provide the old name and the new name, even if the name is the same. See the syntax in the documentation:

CHANGE [COLUMN] old_col_name new_col_name column_definition

In your case it should be

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

CodePudding user response：

You don't need to change the individual column if you use ALTER TABLE ... CONVERT TO CHARACTER SET .... That ALTER TABLE automatically converts all string-based columns.

The documentation describes:

To change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;
The statement also changes the collation of all character columns.

Paul Spiegel's answer about the CHANGE COLUMN syntax is correct; that syntax allows you to change a column's name, so you need to specify the column name twice.

An alternative is to use MODIFY COLUMN instead of CHANGE COLUMN. This allows you to change the column type and options, including character set, but not to change the column name. So there's no need to include the column name twice.

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

But again, in your example, there's no need to use either CHANGE COLUMN or MODIFY COLUMN. The character set conversion should be achieved by using the CONVERT TO CHARACTER SET action.