What does default do in CHARACTER SET and COLLATE when creating a database?

CREATE DATABASE db_name
    [[DEFAULT] CHARACTER SET charset_name]
    [[DEFAULT] COLLATE collation_name]

The mysql docs tell me that you can optionally specify DEFAULT for these two lines, but what does it actually do?

How is DEFAULT CHARACTER SET different from CHARACTER SET? (The same goes for collate)

Answer

There’s no difference. They do the exact same thing, whether you use the DEFAULT keyword or not. It’s just optional syntax. You would use it only for readability or personal preference.

There are other examples of optional syntax in SQL, for example the AS keyword when declaring column aliases or table aliases, or the word COLUMN in ALTER TABLE <name> ADD [COLUMN] <name> ...

Keep in mind SQL was designed by a committee, so there were bound to be some areas where people disagreed on the syntax, and it was easier to make some syntax optional than to get them to agree. 🙂