Create a MySQL database with charset UTF-8

All we need is an easy explanation of the problem, so here it is.

I’m new to MySQL and I would like to know:

How can I create a database with charset utf-8 like I did in navicat?

create mydatabase;

…seems to be using some kind of default charset.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Update in 2019-10-29
As mentions by @Manuel Jordan in comments, utf8mb4_0900_ai_ci is the new default in MySQL 8.0, so the following is now again a better practice:

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

Answer before 2019-10-29
Note: The following is now considered a better practice (see bikeman868’s answer):

CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Original answer:

Try this:

CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8_general_ci;

For more information, see Database Character Set and Collation in the MySQL Reference Manual.

Method 2

You should use:

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Note that utf8_general_ci is no longer recommended best practice. See the related Q & A:

What’s the difference between utf8_general_ci and utf8_unicode_ci on Stack Overflow.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply