write "ENGINE=INNODB DEFAULT CHARSET=UTF8MB4" once for the whole script

Question

When exporting my database structure there is this ENGINE=INNODB DEFAULT CHARSET=UTF8MB4 after every table creation. Do you have to write it for every table? Or is there a way to write it once for every table like (pseudo SQL)

CREATE DATABASE IF NOT EXISTS `mydb`;
USE `mydb`;
SET (ENGINE, `INNODB`);
SET (DEFAULT CHARSET, `UTF8MB4`);

Answer 1

You can do it relatively easily by following these steps:

• 1) Take a dump of your database - for the sake of this example, I've used the MySQL sakila sample database "designed to represent a DVD rental store". The command I used was:

  mysqldump -u root -p sakila > sakila.sql

  You will then be asked for the password for the user that you are using - in this case I used root - this is not best practice!

Your dumpfile will contain many sections (2 examples shown) like the following (16 for the Sakila schema - one for each table in your database/schema):

  DROP TABLE IF EXISTS `actor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `actor` (
  `actor_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`actor_id`),
  KEY `idx_actor_last_name` (`last_name`)
) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

and

DROP TABLE IF EXISTS `category`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `category` (
  `category_id` tinyint unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(25) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

• 2) The key lines in these sections are at the ends of the table definitions:

) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8;

and

) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;

• 3) use your editor of choice to change utf8 to utf8mb4. Or, if you're on Linux, you can use the sed utility:

sed 's/utf8/utf8mb4/g' sakila.sql > sakila.sql.bis // the "g" is for "global"

• 4) Check your output file (belt and braces!) using your editor.

• 5) Drop your Sakila schema using your mysql prompt or from MySQL Workbench (or your tool of choice):

DROP SCHEMA sakila;  -- "DROP DATABASE" does the same thing

• 6) Recreate an empty Sakila schema.

CREATE SCHEMA sakila;  -- ditto "CREATE DATABASE"

• 7) Having verified sakila.sql.bis, rename it sakila.sql overwriting the old file.

• 8) Load your modified SQL file, again using mysqldump (in reverse so to speak - note the change of > to < - output to input).

mysqldump -u root -p sakila < sakila.sql

Your final step is to set the default character set to utf8mb4 in my.cnf/my.ini.

[client]
default-character-set=utf8mb4

[mysql]
default-character-set=utf8mb4


[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8mb4'
character-set-server = utf8mb4

So now, all your tables will be UTF8MB4 and any future tables will be created with the same character set. You can, as mentioned in the last link, also use different collations per table and/or per field, but that's not part of the question - an internet search should be sufficient to deal with this requirement should you need it.

Answer 2

2 different techniques are needed:

SET default_storage_engine = InnoDB;
CREATE DATABASE foo DEFAULT CHARACTER SET utf8mb4;
USE foo;

CREATE TABLE ....