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`);
Best Answer
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: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):
and
and
utf8
toutf8mb4
. Or, if you're on Linux, you can use thesed
utility:mysql
prompt or from MySQL Workbench (or your tool of choice):mysqldump
(in reverse so to speak - note the change of>
to<
- output to input).Your final step is to set the default character set to
utf8mb4
inmy.cnf/my.ini
.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.