Create database (MySQL)

Wat ik meestal zoek (bijgewerkt aug. 2020 met nieuwe collate-clause):

create database tmp collate utf8mb4_0900_ai_ci;

Syntaxis

Just for fun, let's have a look at the complete syntaxis for MySQL 5.7 [1]. Actually MySQL 8.0 has an extra option (ENCRYPTION) that won't be addressed here [2]:

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
   [create option] ...

   create_option:
   {
      [DEFAULT] [CHARACTER SET [=] charset_name
      |
      [DEFAULT] COLLATE [=] collation_name
   }

• Appearantly, CREATE DATABASE is a statement. I'm always confused if you would call this a command, keyword, function, or whatever. Glad to have this solved
• CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database
• The keywords database and schema are identical. Personally, I prefer the term database, as you can see in the example above
• It's unclear for me from the syntaxis above, if there are actually only two create_options, or more. The reference manual doesn't address this - Maybe only two indeed
• Each create_option specifies a database characteristic. Database characteristics are stored in the db.opt file in the database directory. The CHARACTER SET option specifies the default database character set. The COLLATE option specifies the default database collation [3]
• To see the available character sets and collations, use the SHOW CHARACTER SET and SHOW COLLATION statements
• As you can see in the syntaxis above, you have to choose between specifying the CHARACTER SET and the COLLATION. The reason for this: When specifying the collation, you always include the character set - You can't specify the collation without also naming the associated character set - See examples below
• A database in MySQL is implemented as a directory containing files that correspond to tables in the database. Because there are no tables in a database when it is initially created, the CREATE DATABASE statement creates only a directory under the MySQL data directory and the db.opt file
• See [4] for rules concerning permissible database names. If a database name contains special characters, the name for the database directory contains encoded versions of those characters as described here
• If you manually create a directory under the data directory (for example, with mkdir), the server considers it a database directory and it shows up in the output of SHOW DATABASES
• When you create a database, let the server manage the directory and the files in it. Manipulating database directories and files directly can cause inconsistencies and unexpected results
• MySQL has no limit on the number of databases. The underlying file system may have a limit on the number of directories.
• You can also use the mysqladmin program to create databases.

Examples

Some of these examples in action

Let's start simple:

create database tmp;

or

create database if not exists tmp;

While specifying a character encoding. I suspect the word 'DEFAULT' is entirely optional. If I can't to be really sure, I could check the file that's written with the specs.

create database 
   tmp
character set 
   utf8

And with a collation:

create database
   tmp
collate
   utf8_general_ci

Zie ook

• Karaktersets & collation (MySQL)

Bronnen

• https://dev.mysql.com/doc/refman/5.7/en/create-database.html
• https://dev.mysql.com/doc/refman/8.0/en/create-database.html