213

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.

Improve this question

2 Answers 2

Reset to default
371

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.

Improve this answer
8
  • 7
    MySQL's utf8mb4 is what the rest of us call utf8. So what is MySQL's utf8 you ask? It's a limited version of utf-8 that only works for a subset of the characters but fails for stuff like emoji. Later they added utf8mb4 which is the correct implementation, but MySQL has to stay backwards compatible to it's old mistakes so that is why the added a new encoding instead of fixing the old one. All new databases should use utf8mb4.
    – Stijn de Witt
    Nov 28, 2018 at 20:38
  • If you want to go down the rabbit hole: COLLATE utf8mb4_unicode_520_ci or utf8mb4_0900_ai_ci or even locale specific, for example: utf8mb4_vi_0900_ai_ci. For MariaDB 10.2.2+, you have "nopad" collations utf8mb4_unicode_520_nopad_ci. dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html
    – Frank Forte
    Mar 22, 2019 at 14:09
  • @shellbye Consider to update your answer to do mention about utf8mb4_0900_ai_ci. Seems is better than utf8mb4_unicode_ci
    – Manuel Jordan
    Oct 9, 2019 at 17:58
  • 1
    @ManuelJordan Updated as you suggested.
    – shellbye
    Oct 29, 2019 at 8:31
  • Do you know whether this also applies to MariaDB?
    – Manngo
    Mar 10, 2020 at 3:06
82

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.

Improve this answer
1
  • 2
    Consider to update your answer to do mention about utf8mb4_0900_ai_ci. Seems is better than utf8mb4_unicode_ci
    – Manuel Jordan
    Oct 9, 2019 at 17:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.