Charsets, Collations, and utf8mb4 from Day One

MySQL's utf8 is not UTF-8. It is a three-byte encoding officially named utf8mb3, and it cannot store a four-byte codepoint. Emoji, many music symbols, and a slice of supplementary-plane characters fail or become replacement characters. utf8mb4 is actual UTF-8. If you are creating a database in 2026 and the charset is utf8 or latin1, you are booking a migration.

Collation is the extra decision people skip. It controls comparison and sort order, including whether a and A are equal and how Hindi, English, and emoji compare. Mixing collations in a JOIN is a runtime error or a silent full-scan conversion. Set server, database, table, and connection to the same pair on day one.

Set the server, then create databases with an explicit pair

A drop-in makes the default honest. New tables inherit the database default, which inherits the server default, which is only used if you did not override it. Be explicit at CREATE DATABASE anyway. Future you will thank present you when a restore lands on a box with different defaults.

# /etc/mysql/mariadb.conf.d/94-charset.cnf
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4

utf8mb4_unicode_ci is the collation we still recommend for general applications. utf8mb4_general_ci is faster and dumber about Unicode rules. utf8mb4_uca1400_ai_ci (and friends) appear on recent MariaDB and are better Unicode defaults if every client and replica speaks the same series. Pick one collation and do not mix it inside a schema.

CREATE DATABASE appdb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
USE appdb;
CREATE TABLE notices (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(191) NOT NULL,
  body TEXT NOT NULL,
  PRIMARY KEY (id),
  KEY title (title)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

VARCHAR(255) in utf8mb4 is 1020 bytes, which exceeds the 767-byte index prefix on older InnoDB ROW_FORMAT=COMPACT. VARCHAR(191) is the classic workaround. On modern MariaDB with innodb_large_prefix and DYNAMIC row format, 255 is fine. Know which you are on before you copy a schema from 2016.

Connections lie if the client disagrees

A utf8mb4 table with a latin1 connection will convert on the way in and out. That is how you store mojibake that looks fine in the app that wrote it and broken in every other client. Set the client charset in the DSN or immediately after connect. In the official connectors that is charset=utf8mb4. In PHP PDO it is a SET NAMES in the options. In the CLI it is --default-character-set=utf8mb4.

Check all four layers when a string looks wrong:

SHOW VARIABLES WHERE Variable_name IN (
  'character_set_server','collation_server',
  'character_set_database','collation_database',
  'character_set_client','character_set_connection',
  'character_set_results','collation_connection'
);
SELECT id, HEX(title), title FROM notices ORDER BY id DESC LIMIT 5;

HEX is the ground truth. If you expected an emoji and you see bytes that decode as ? or as C3 xx from a double-encoded UTF-8 sequence, the client layer is wrong. Fix the connection, then rewrite the bad rows. Do not CONVERT TO CHARSET in a panic on production without a dump; conversion of already-mojibake data bakes the garbage in.

For Indian language content, utf8mb4 is required, not optional. Hindi, Tamil, and mixed English strings live happily in utf8mb4_unicode_ci. If you need language-specific sort order, that is a column-level collation, documented and tested, not a different database charset.

Convert existing schemas with a plan, not a loop

ALTER TABLE ... CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci rebuilds the table. It locks, it takes disk, and it will fail if an index prefix no longer fits. Work table by table:

• Dump first.
• Check index lengths and shorten VARCHAR if needed.
• Convert on a replica or a restored copy, then measure time.
• Convert production in a window, or use a tool that copies the table.

Never rely on SET NAMES to "make the old latin1 table work." It will not. The bytes on disk are latin1. Application frameworks that default to utf8 (the three-byte alias) will keep writing the wrong encoding even after the table is converted, so change the DSN in the same window as the ALTER. If you serve Hindi and English in the same VARCHAR, test a round trip of a known string from the app, from the CLI, and from a dump restore, and compare HEX() on all three before you call the migration done.

utf8mb4 plus one collation, set at server, database, table, and connection, is the whole charset strategy for a new VPS. Mixing layers is how you get a migration. Create the next database the right way on an Ubuntu instance in Lucknow — you can be in the SQL prompt a minute after checkout at netbayhosts.in.