Mysql – convert default charset utf8 tables to utf8mb4 thesql 5.7.17

character-setMySQL

I tried the following steps to upgrade CHARSET to utf8mb4 and to connect it to the existing backend server.

  1. ALTER TABLE users CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  2. update the DB connection parameters.

"user:pass@tcp(server:3306)/mydbcharset=utf8mb4&collation=utf8mb4_unicode_ci"

This is the users table schema before migration.

CREATE TABLE IF NOT EXISTS `users` (
    `id`               BIGINT(20) NOT NULL AUTO_INCREMENT,
    `email`            VARCHAR(255) NOT NULL DEFAULT '',
    `password`         VARCHAR(255) NOT NULL DEFAULT '',
    `country_code`     CHAR(4) NOT NULL DEFAULT '',
    `industry`         VARCHAR(255) NOT NULL DEFAULT '',
    `company_size`     VARCHAR(255) NOT NULL DEFAULT '',
    `company_name`     VARCHAR(255) NOT NULL DEFAULT '',
    `last_name`        VARCHAR(255) NOT NULL DEFAULT '',
    `first_name`       VARCHAR(255) NOT NULL DEFAULT '',
    `newsletter`       TINYINT(1) NOT NULL DEFAULT 0,
    `company_website`  VARCHAR(255) NOT NULL DEFAULT '',
    `job_title`        VARCHAR(255) NOT NULL DEFAULT '',
    `profile_picture`  VARCHAR(255) NOT NULL DEFAULT '',
    `verified`         TINYINT(1) NOT NULL DEFAULT 0,
    `p_updated_time`   INT NOT NULL DEFAULT 0,
    `created`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated`          TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY (`email`)
)ENGINE = InnoDB DEFAULT CHARSET=UTF8;

Everything works fine so far.

My worry is that some of the VARCHAR columns have character constrain as 255. In this article https://mathiasbynens.be/notes/mysql-utf8mb4 and the accepted answer here How to easily convert utf8 tables to utf8mb4 in MySQL 5.5 says updating varchar(255) to 191 is needed. However I did not encounter issues without doing that, existing DB has around 100 user records and a dry run of the above migration worked perfectly.

Question are

  1. Is it mandatory to check and update column lengths when moving from
    utf8 to utf8mb4, if not what kind of issues could arise?
  2. Is it also mandatory to change the CHARSET of the database? Why not change only the charset of the table?

UPDATE

For someone who runs into foreign key violations can use the below

SET FOREIGN_KEY_CHECKS=0;

-- Insert your other SQL Queries here...
 ALTER TABLE tble_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS=1;

Best Answer

5.5 and 5.6 had the 191 problem. 5.7 solved the problem.

See this for discussion of multiple workarounds in 5.5/5.6. Both articles you reference is dated, since they discusses 5.5. (Note that I have already commented on both.)

I recommend you change 255 to some reasonable limit for each column, anyway.

A caveat with ALTER .. CONVERT TO .. -- If country_code were what it should be, namely CHARACTER SET ascii; the ALTER would change it to utf8mb4.

Q2...

The CHARSET of a column matters. The CHARSET of a table is just the default for the columns. Ditto for the database and any newly created table.

I prefer not to depend on such defaulting, and I explicitly state the CHARSET on any table I CREATE. Others may prefer to do a ALTER DATABASE.