Question 38742 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Convert database to utf8 – Ubuntu

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

Tags:

3

Answers


  1. 0

    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;

    Demonstration: . db-fiddle.com/f/qFuvFqP5PEGbsc8F8DfiaN/0

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
  3. 0

    ALTER DATABASE hbtn_0c_0 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    USE hbtn_0c_0;
    –The above command will converts hbtn_0c_0 database to UTF8 and selects hbtn_0c_0 databas
    — convert first_table to UTF8
    ALTER TABLE first_table
    CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    —the above one converts your table to utf-8

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search