Create integer only table with a default charset, bad idea?

user_10980607 0
Viewed 249

When I create a table, even if it's using integers only, I set the default charset to utf8 (because I copy paste the code and because in case I introduce a string column in the future).

Example:

CREATE TABLE IF NOT EXISTS `articles` (
  `id` smallint(6) unsigned NOT NULL,
  `disabled` tinyint(1) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

However, i'm wondering if it's affecting "performances" to have a default charset in a table that do not make use of it.

percona mysql
2 Answers

Tables have a character set no matter what, so no, there's no performance issue, and UTF-8 is a good default choice (but utf8 is not). But you still shouldn't do that.

It is a bad practice to add a default character set unless you need to specify one. This overrides the default character set of the database which might not be utf8. You're risking making a table with a different character set than every other table causing confusion.

Instead, make sure the server and database character set are set correctly. Then let your tables use the default, unless you have a specific reason to do otherwise.

For example, UTF-8 is a good default choice, but MySQL got UTF-8 wrong. utf8 cannot handle all of UTF-8. You should instead be using utf8mb4 (UTF-8 4-byte). The database might correctly use utf8mb4, but you're overriding that with a less capable character set.

See Specifying Character Sets and Collations and Unicode Support.

user_146600

The DEFAULT CHARSET clause at the bottom of your table creation is only metadata. It is only used if you add a CHAR/VARCHAR/TEXT column and don't explicitly define the column's character set. Then the table's default character set is used.

Tables don't have any performance characteristic — they are just storage. Queries have performance.

Since your table has no columns with character sets, there can be no query against this table that is affected by the character set. Therefore the default character set has no effect.

Bill_Karwin_208600
Related