216

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.

John K. N.
  • 17,649
  • 12
  • 51
  • 110
user3397998
  • 2,271
  • 2
  • 12
  • 4

2 Answers2

378

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.

shellbye
  • 3,934
  • 1
  • 12
  • 6
  • 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 '18 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.

    https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-sets.html

     – Frank Forte Mar 22 '19 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 09 '19 at 17:58
  • 1
    @ManuelJordan Updated as you suggested. – shellbye Oct 29 '19 at 08:31
  • Do you know whether this also applies to MariaDB? – Manngo Mar 10 '20 at 03:06
  • @Manngo I haven't tested it, you can try it and publish your result here. – shellbye Mar 11 '20 at 12:02
  • i have found utf8mb4_general_ci better than utf8mb4_unicode_ci – blueDexter Apr 15 '20 at 07:20
  • "COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'utf8mb4 – Erik Aronesty Aug 30 '22 at 15:29
83

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.

Community
  • 1
bikeman868
  • 938
  • 6
  • 6