MySQL Change Table Collation and Charset

by | May 19, 2021 | MySQL

PhpStorm is my preferred IDE for all my web projects (PHP, Vue, Node, React, etc.). To manage databases I use PhpStorm’s built-in database tool.
For some reason they have not implemented the ability to change table/column collations and character sets within this tool which means I have to resort to running SQL commands to accomplish this task.

Here are the SQL (MySQL) commands.

First I list all the columns from the table I want to confirm has the correct collation/character set.

SHOW FULL COLUMNS FROM mytablename;

If the collations and/or charset is not correct then I run the following.

ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mytablename CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I then repeat the second command for all other tables.

Sometimes I also need to disable foreign_key_checks

SET foreign_key_checks = 0
-- SQL...
SET foreign_key_checks = 1

MySQL Commands

by | Apr 21, 2021 | MySQL

Create a new database and user that has all permissions to the new database

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'jsmith'@'%' IDENTIFIED BY 'password';
GRANT ALL ON mydatabase.* TO 'jsmith'@'%';