Character Set và Collation trong MySQL: Từ Lý Thuyết Đến Sự Cố Dữ Liệu Tiếng Nhật Thực Tế

Character Set Và Collation Trong MySQL Là Gì?

Trong hầu hết các dự án, charset/collation chỉ là hai dòng cấu hình mà framework đã set sẵn (utf8mb4, utf8mb4_unicode_ci) rồi không ai động vào nữa. Nhưng khi hệ thống bắt đầu xử lý:

• Japanese applications (ERP, e-commerce, product management)
• CSV import/export giữa nhiều nguồn dữ liệu
• Database migration từ hệ thống legacy sang hệ thống mới
• Integration giữa nhiều database khác version, khác charset

thì hai khái niệm tưởng chừng "chỉ là config" này có thể trở thành một business-critical concern. Một lựa chọn sai có thể gây ra:

❌ Cannot store character
❌ Garbled text (mojibake)
❌ Duplicate key không mong muốn
❌ Wrong search / JOIN result
❌ Illegal mix of collations
❌ Migration data inconsistency

Bài viết này đi qua toàn bộ chuỗi khái niệm — từ character, encoding, charset, collation — và kết thúc bằng một case study thực tế: một sự cố dữ liệu tiếng Nhật bị trùng lặp âm thầm trên một hệ thống legacy MySQL, và cách phân tích root cause để đưa ra quyết định migrate đúng đắn.

Có thể hình dung toàn bộ chuỗi khái niệm như sau:

Character → Encoding → Character Set → Collation → Comparison/Sorting → Index/UNIQUE/JOIN → Application Behavior

Character, Unicode Và Encoding: Nền Tảng Cần Hiểu Trước

Một character là một ký tự con người sử dụng: A, あ, 漢, 😀. Nhưng máy tính không lưu trực tiếp khái niệm "A" hay "漢" — nó chỉ lưu numbers/bytes. Do đó cần một cơ chế mapping: character → code point → bytes → lưu trong database.

Unicode là một standard gán một code point cho mọi character trên thế giới, ví dụ A là U+0041, あ là U+3042, 😀 là U+1F600. Unicode không phải là một encoding cụ thể — nó là "character vocabulary". Còn UTF-8, UTF-16, UTF-32 mới là các cách encode Unicode code points thành bytes thực tế.

Encoding quyết định một Unicode character được biểu diễn thành bao nhiêu bytes. UTF-8 là variable-length: một character có thể dùng từ 1 đến 4 bytes tùy loại ký tự:

Đây chính là lý do tên gọi utf8mb3 và utf8mb4 có ý nghĩa: mb3 = tối đa 3 bytes/character, mb4 = tối đa 4 bytes/character.

utf8 Trong MySQL: Một Cái Tên Dễ Gây Hiểu Nhầm

Đây là một trong những trap kinh điển nhất của MySQL. Charset tên là utf8 trong MySQL (đặc biệt các version cũ) không phải là UTF-8 đầy đủ — nó thực chất là utf8mb3, chỉ hỗ trợ tối đa 3 bytes/character. Trong khi UTF-8 chuẩn cần tối đa 4 bytes để biểu diễn đầy đủ Unicode (bao gồm emoji và một số ký tự CJK hiếm).

utf8mb3   ≠   UTF-8 đầy đủ
utf8mb4   =   UTF-8 với tối đa 4 bytes (UTF-8 thật)

So sánh trực tiếp:

Hello, こんにちは, 東京 hoạt động được với cả hai. Nhưng 😀, 🚀, 🎉 bắt buộc cần utf8mb4.

Character Set Được Định Nghĩa Ở Những Level Nào?

Trong MySQL, character set có thể được định nghĩa ở nhiều tầng khác nhau: Server → Database → Table → Column → Connection.

-- Database level
CREATE DATABASE shop CHARACTER SET utf8mb4;

-- Table level
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255)
) CHARACTER SET utf8mb4;

-- Column level (override table default)
CREATE TABLE products (
    id BIGINT PRIMARY KEY,
    name VARCHAR(255) CHARACTER SET utf8mb4
);

Điểm quan trọng: default ở table không có nghĩa mọi column chắc chắn đang dùng cùng một character set — column có thể override. Ngoài ra, connection charset của application cũng quan trọng không kém. Nếu Laravel/PHP connect với utf8mb4 nhưng table lại là utf8mb3 (hoặc ngược lại), có thể phát sinh conversion, data corruption, hoặc unexpected comparison behavior. Vì vậy khi debug encoding issue, cần kiểm tra toàn bộ chuỗi Application → Connection → Database → Table → Column, không chỉ riêng table.

Collation Là Gì Và Ảnh Hưởng Đến Những Gì?

Nếu Character Set trả lời câu hỏi "có thể biểu diễn character nào?", thì Collation trả lời câu hỏi "so sánh và sắp xếp các character như thế nào?".

SELECT * FROM users WHERE name = 'ABC';

Database phải quyết định ABC và abc có được coi là giống nhau không — đó là công việc của collation. Collation ảnh hưởng trực tiếp đến:

=    <>    LIKE    JOIN    GROUP BY    DISTINCT    ORDER BY    UNIQUE INDEX

Giải Mã Tên Một Collation

Ví dụ utf8mb4_unicode_ci có thể tách thành 3 phần:

utf8mb4  → Character Set
unicode  → Collation family
ci       → Case Insensitive

Các suffix phổ biến:

• ci (Case Insensitive): ABC và abc thường được coi là equivalent.
• cs (Case Sensitive): ABC và abc được phân biệt rõ ràng.
• bin (Binary): so sánh dựa trên binary representation/code point thay vì linguistic rules — dùng khi cần exact comparison.

general_ci vs unicode_ci: Khác Biệt Không Chỉ Là Tên Gọi

Đây là câu hỏi phổ biến nhất khi làm việc với charset/collation: utf8_general_ci và utf8mb4_unicode_ci khác nhau thế nào? Thực ra chúng khác nhau ở cả hai chiều: character set (utf8mb3 vs utf8mb4) lẫn collation (general vs unicode), nên không nên coi đây chỉ đơn giản là hai collation cùng họ.

Về mặt comparison semantics:

• general_ci so sánh ký tự theo weight đơn giản, không áp dụng đầy đủ Unicode Collation Algorithm (UCA).
• unicode_ci áp dụng UCA đầy đủ hơn, "thông minh hơn" về mặt ngôn ngữ học — nhưng cái thông minh đó đôi khi lại là nguồn gốc của sự cố, như case study dưới đây sẽ cho thấy.

Điều quan trọng cần nhớ: Unicode collation "hiện đại hơn" không có nghĩa là "business correct hơn". Một legacy system tiếng Nhật có thể đã chạy utf8_general_ci suốt 10-15 năm; toàn bộ business logic, unique keys, search, import/export đều được xây dựng dựa trên hành vi so sánh đó. Khi migrate sang utf8mb4_unicode_ci chỉ vì đó là default hiện đại, database có thể có hành vi so sánh khác — và "modern" không đồng nghĩa với "compatible".

Case Study Thực Tế: Sự Cố Trùng Lặp Dữ Liệu Tiếng Nhật Trên Hệ Thống Legacy

Đây là một case study có thật, rất điển hình cho các dự án legacy replacement tại Nhật Bản, minh họa chính xác vì sao phần lý thuyết ở trên không phải là kiến thức hàn lâm.

Bối cảnh sự cố

Một hệ thống legacy PDB (Product Database) chạy trên MySQL, dữ liệu được export ra để migrate sang một hệ thống mới xây bằng Laravel. Trong quá trình migrate, một bảng master mới là product_category được tạo với collation mặc định của framework:

CREATE TABLE `product_category` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_cd` VARCHAR(20)  NOT NULL COMMENT 'Mã category, đồng bộ từ legacy',
    `category_nm` VARCHAR(255) NOT NULL COMMENT 'Tên category hiển thị (tiếng Nhật)',
    `created_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `UK_product_category` (`category_cd`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Trong khi một bảng khác đã tồn tại từ trước, category_group, dùng collation legacy-compatible:

CREATE TABLE `category_group` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `category_name` VARCHAR(255) NOT NULL COMMENT 'Tên category gốc, đồng bộ nguyên bản từ legacy',
    `created_at`    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

Dữ liệu minh họa: hai category hợp lệ và khác nhau hoàn toàn về nghiệp vụ, chỉ khác nhau ở dấu dakuten (ソ so với ゾ):

INSERT INTO `category_group` (`category_name`) VALUES
    ('ソース類'),      -- Nhóm "Sauce" (nước sốt)
    ('ゾーン雑貨'),    -- Nhóm "Zone goods" (đồ tạp hoá theo khu vực)
    ('はさみ・文具'),  -- Nhóm "Kéo & văn phòng phẩm"
    ('ばんそうこう');  -- Nhóm "Băng cá nhân"

INSERT INTO `product_category` (`category_cd`, `category_nm`) VALUES
    ('CTG-001', 'ソース類'),
    ('CTG-002', 'ゾーン雑貨');

Khi JOIN hai bảng này theo tên category:

SELECT ...
FROM product_category i
JOIN category_group c
    ON i.category_nm = c.category_name;

MySQL không thể tự resolve collation nào nên dùng để so sánh, và ném ra lỗi:

ERROR 1267 (HY000): Illegal mix of collations

Phần nổi và phần chìm của vấn đề

Lỗi 1267 thực ra là phần dễ thấy nhất của vấn đề — nó xảy ra ngay lập tức, có exception rõ ràng, dễ vá tại điểm phát sinh bằng COLLATE explicit:

SELECT ...
FROM product_category i
JOIN category_group c
    ON i.category_nm COLLATE utf8mb4_general_ci = c.category_name;

Nhưng nguyên nhân gốc thật sự nghiêm trọng hơn nhiều, và nó không hề ném ra lỗi nào cả: khi cả hai vế so sánh đều nằm trên unicode_ci (không có xung đột collation family để MySQL báo lỗi), hai giá trị tiếng Nhật khác nhau về mặt business — ví dụ ソ và ゾ (khác nhau ở dấu dakuten) — có thể bị collation coi là TRÙNG NHAU một cách âm thầm.

Team đã verify bằng thực nghiệm trực tiếp trên chính engine đang chạy production (MariaDB 10.5.28):

Áp trực tiếp vào bộ dữ liệu vừa insert ở trên để thấy rõ hậu quả:

-- Với collation legacy (general_ci): 2 category được đếm đúng, tách biệt
SELECT category_name, COUNT(*) AS total
FROM category_group
GROUP BY category_name COLLATE utf8mb4_general_ci;
-- ソース類       | 1
-- ゾーン雑貨     | 1

-- Nếu column/collation là unicode_ci: 2 category bị GỘP LÀM MỘT một cách âm thầm
SELECT category_name, COUNT(*) AS total
FROM category_group
GROUP BY category_name COLLATE utf8mb4_unicode_ci;
-- ソース類 / ゾーン雑貨  | 2   ← không hề có lỗi, nhưng dữ liệu đã sai

Không có exception nào được ném ra ở ví dụ thứ hai — kết quả vẫn chạy trơn tru, chỉ là sai về mặt nghiệp vụ: "Sauce" và "Zone goods" bị đếm chung thành một nhóm. Đây chính là nguyên nhân gốc thật sự của một sự cố trước đó trên bảng category_group: hai dòng dữ liệu hợp lệ và khác nhau về mặt nghiệp vụ (chỉ khác nhau ở dấu dakuten) bị unicode_ci coi là trùng lặp — dẫn đến de-dup nhầm, làm mất một trong hai category thật khỏi hệ thống. Trường hợp này không liên quan gì đến JOIN hay lỗi 1267 — nó có thể xảy ra ở bất kỳ đâu trong hàng chục bảng khác đang dùng unicode_ci làm default: một WHERE category_nm = ?, một GROUP BY, một check trùng trước khi INSERT... tất cả đều có thể âm thầm khớp nhầm hai giá trị tiếng Nhật khác nhau mà không có exception nào để mà đi vá.

Truy vết về nguồn gốc collation thật sự

Để quyết định đúng collation cho hệ thống mới, team đã truy ngược lại file export gốc của database legacy:

-- MySQL dump 10.11
-- Host: localhost    Database: shop_db
-- Server version    5.0.95-log

Chuỗi 5.0.95-log là format thuần MySQL Community — khác hẳn format version của MariaDB (luôn có dạng 5.5.5-10.x.x-MariaDB). Toàn bộ CREATE TABLE trong dump chỉ ghi DEFAULT CHARSET=utf8 mà không khai báo COLLATE rõ ràng. Trong MySQL, khi không khai báo collation, server tự áp default collation của charset đó — và default của utf8 trong MySQL (từ 4.1 đến 8.0) luôn là utf8_general_ci.

Một chi tiết quan trọng khác: MySQL 5.0.95 ra đời trước khi utf8mb4 tồn tại (charset này chỉ được thêm từ MySQL 5.5.3). Điều đó có nghĩa unicode_ci chưa từng là một lựa chọn khả dụng khi dữ liệu gốc này được thiết kế — general_ci là default duy nhất tồn tại tại thời điểm đó, không phải một lựa chọn có chủ đích để tối ưu cho tiếng Nhật, mà đơn giản là "cái duy nhất có sẵn".

Quyết định: vì sao chọn general_ci chứ không phải unicode_ci

Kết luận cuối cùng của việc phân tích root cause, dựa trên 5 lý do:

1. Legacy 100% dùng general_ci — audit thực tế trên toàn bộ các database liên quan đều cho ra utf8_general_ci. Nguyên tắc cốt lõi của một dự án legacy replacement là replicate đúng hành vi hệ thống cũ, không "cải tiến" ngầm một cách vô tình.
2. Khác biệt kỹ thuật rõ ràng, không phải lý thuyết suông — general_ci coi hai ký tự khác code point (như ソ và ゾ, vốn là hai code point Unicode riêng biệt) là khác nhau — đúng với kỳ vọng của người dùng nghiệp vụ. unicode_ci thì gộp chúng lại vì áp dụng UCA đầy đủ hơn.
3. Đây không phải giả thuyết, mà là sự cố đã xảy ra thật — nếu tiếp tục dùng unicode_ci làm default, hệ thống mới sẽ tiếp tục "nuốt" mất dữ liệu category hợp lệ mỗi khi có cặp ký tự dakuten/handakuten trùng nhau về mặt weight.
4. Sự "thông minh hơn" của unicode_ci không phải thứ hệ thống này cần — UCA được thiết kế để tối ưu sort/match cho đa ngôn ngữ (dấu Latin, ligature...), nhưng dữ liệu ở đây là dữ liệu nghiệp vụ tiếng Nhật thuần túy, với nguồn sự thật là legacy MySQL cũ chỉ biết đến general_ci.
5. Nhất quán với những gì đã pin sẵn — một số bảng đã được pin utf8mb4_general_ci thủ công từ trước để né sự cố tương tự; đổi default toàn cục chỉ là generalize hoá lại đúng quyết định đó, tránh phải nhớ thủ công "bảng nào cần pin".

Ba lựa chọn khi gặp lỗi Illegal Mix Of Collations

Khi gặp lỗi 1267 giữa hai bảng có collation khác nhau, có ba hướng xử lý:

Option 1 — Đổi collation của column đang gây lỗi. Không nên làm chỉ vì một query — có thể ảnh hưởng dây chuyền đến UNIQUE, search, JOIN khác, và GROUP BY đang phụ thuộc vào collation cũ của column đó.

Option 2 — Đổi collation của bảng còn lại. Cũng không nên nếu collation hiện tại của bảng đó được chọn có chủ đích để preserve legacy semantics (như trường hợp category_group ở trên).

Option 3 — Explicit COLLATE ngay tại query.

SELECT ...
FROM product_category i
JOIN category_group c
    ON i.category_nm COLLATE utf8mb4_general_ci = c.category_name;

Nếu business requirement là "JOIN này phải compare theo đúng legacy semantics", đây thường là giải pháp tốt nhất — nhưng cần hiểu rõ: nó chỉ vá được trường hợp hai bảng bị đụng độ trực tiếp trong một câu query cụ thể, chứ không giải quyết được rủi ro lớn hơn là dữ liệu bị so sánh sai một cách âm thầm ở những nơi khác không hề ném exception. Muốn triệt tận gốc, phải đồng bộ default collation ở tầng connection/schema, không thể chờ lỗi rồi mới vá từng chỗ.

Áp dụng fix ở tầng connection (Laravel)

Sau khi xác định general_ci là lựa chọn đúng, fix tận gốc được áp dụng ngay ở config kết nối, để mọi migration mới về sau tự động đúng chuẩn mà không cần khai báo thủ công:

// config/database.php
'mysql' => [
    ...
    'charset' => env('DB_CHARSET', 'utf8mb4'),
    'collation' => env('DB_COLLATION', 'utf8mb4_general_ci'), // đổi từ utf8mb4_unicode_ci
],

Với các bảng dữ liệu lớn (hàng triệu dòng), ALTER TABLE ... CONVERT TO CHARACTER SET sẽ rewrite toàn bộ bảng và khóa rất lâu trên môi trường dùng chung — nên với nhóm bảng này, cách an toàn hơn là chạy lại script full-reload (drop + migrate --force để bảng được tạo lại tự động đúng collation mới + reload dữ liệu từ nguồn legacy), thay vì ALTER trực tiếp. Chỉ nhóm bảng nhỏ (dưới vài nghìn dòng) mới nên dùng ALTER TABLE trực tiếp vì mức ảnh hưởng chấp nhận được.

Checklist Kiểm Tra Và Audit Charset/Collation

Trước khi kết luận một sự cố encoding, cần audit ở cấp column, không chỉ database hay table, vì một database có thể default utf8mb4_unicode_ci trong khi một table bên trong lại là utf8mb4_general_ci, và thậm chí một column trong table đó lại override khác cả hai.

-- Kiểm tra charset/collation mặc định của database
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = 'shop_db';

-- Kiểm tra collation của từng table
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop_db'
ORDER BY TABLE_NAME;

-- Kiểm tra charset/collation ở cấp column (quan trọng nhất)
SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'shop_db'
  AND COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME, ORDINAL_POSITION;

-- Tìm các table còn dùng legacy utf8 (utf8mb3)
SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'shop_db'
  AND TABLE_COLLATION LIKE 'utf8_%';

-- Kiểm tra charset của connection hiện tại
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

SHOW CREATE TABLE <table> vẫn là cách nhanh nhất để xem đầy đủ column definition, charset, collation, index và constraint cùng lúc — đặc biệt hữu ích khi schema có nhiều override rải rác.

Best Practices Khi Thiết Kế Và Migrate

• Hệ thống mới: ưu tiên utf8mb4 với collation phù hợp business requirement, không mặc định chọn unicode_ci chỉ vì đó là default hiện đại của framework.
• Không normalize data chỉ để né lỗi collation. Nếu ソ và ゾ là hai giá trị nghiệp vụ khác nhau, không được biến đổi dữ liệu chỉ để database dễ so sánh — hãy giữ nguyên dữ liệu gốc và định nghĩa đúng comparison rule ở đúng layer (schema-level nếu rule áp dụng toàn cục, query-level COLLATE nếu chỉ áp dụng cho một tích hợp cụ thể).
• Không đổi collation mà chưa test dữ liệu thật, đặc biệt với các cột có UNIQUE, tham gia JOIN, hoặc chứa text tiếng Nhật — test bằng dữ liệu thật (ソ/ゾ, は/ば/ぱ, ｶﾀｶﾅ/カタカナ) chứ không chỉ ABC/abc.
• Audit ở cấp column, không dừng lại ở database hay table.
• CSV import/export là một vấn đề khác biệt hoàn toàn với charset của database — nếu file CSV thực tế là Shift_JIS/CP932 nhưng application decode như UTF-8, thì collation của database không thể cứu được dữ liệu đã bị decode sai từ trước.
• Migrate không chỉ là chuyển dữ liệu, mà là migrate cả hành vi so sánh (behavior). Đừng mặc định rằng đổi từ legacy utf8_general_ci sang utf8mb4_unicode_ci sẽ cho ra cùng kết quả — phải verify bằng thực nghiệm trên chính bộ dữ liệu và ngôn ngữ thật của hệ thống.

Kết Luận

Character Set quyết định database có thể biểu diễn character nào; Collation quyết định database so sánh và sắp xếp các character đó như thế nào. Trong một hệ thống Nhật hoặc một dự án legacy replacement, hai khái niệm này không chỉ là database configuration đơn thuần — chúng là một phần của data semantics và business behavior.

Case study ở trên cho thấy rõ: một migration tốt không phải là "đổi utf8 legacy sang utf8mb4 hiện đại rồi xong", mà là quy trình Phân tích dữ liệu và hành vi so sánh legacy → Định nghĩa compatibility strategy → Migrate → Test bằng dữ liệu thật → Modernize an toàn. Đó mới là cách tiếp cận bền vững khi xây dựng hệ thống mới trên nền dữ liệu legacy, đặc biệt với các hệ thống Japanese enterprise và e-commerce nơi một dấu dakuten nhỏ cũng có thể là ranh giới giữa hai giá trị nghiệp vụ hoàn toàn khác nhau.