> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/platform-specific-features/mysql/data-types/collation.md). # Collation ## About Collation is a set of **rules** that define how strings are **sorted** and **compared**. These rules are applied when performing operations like: * `ORDER BY` to sort results * `WHERE name = 'John'` to compare values * `GROUP BY name` to group similar records The rules include: * **Case sensitivity** (is `a` equal to `A`?) * **Accent sensitivity** (is `é` equal to `e`?) * **Language-specific sorting** (does `ä` come before or after `z`?) **Naming Convention** MySQL collation names follow this format: ``` character_set_name + _ + collation_name ``` Examples: * `utf8mb4_general_ci` → character set: `utf8mb4`, collation: `general_ci` * `latin1_swedish_ci` → character set: `latin1`, collation: `swedish_ci` **Common Suffixes** * `_ci` → Case Insensitive (e.g., `a = A`) * `_cs` → Case Sensitive * `_bin` → Binary comparison (exact byte-by-byte match) **How to Check Available Collations** ```sql SHOW COLLATION; ``` {% file src="/files/00BzpvGdFamgB7fDSDqC" %} ## **How Collation Works Internally** MySQL converts characters into **binary code points** using the character set. Then, collation defines how those code points are **evaluated** for equality and order: * `Binary collation`: Compares the raw byte values directly. * `General collation`: Uses simplified sorting rules. * `Unicode collation`: Follows the Unicode Collation Algorithm (UCA), more accurate and language-aware. ## **Character Set vs Collation**

Feature	Character Set	Collation
What it defines	Encoding and storage of text	Comparison and sorting rules
Example	`utf8mb4`, `latin1`, `ascii`	`utf8mb4_unicode_ci`, `latin1_swedish_ci`
Storage impact	Affects byte size of characters	No impact on storage, only on string logic

## **Types of Collations**


Suffix	Meaning
`_ci`	Case-insensitive: `'a' = 'A'`
`_cs`	Case-sensitive: `'a' ≠ 'A'`
`_ai`	Accent-insensitive: `'é' = 'e'`
`_as`	Accent-sensitive: `'é' ≠ 'e'`
`_bin`	Binary comparison (byte-by-byte): strictest and fastest
`_0900`	Unicode version 9.0 (used in MySQL 8.0 and later)
`_520`	Unicode version 5.2 (used for more accurate ordering)

### **a. Case Insensitive Collations (`_ci`)** * `'A' = 'a'` * Common default in MySQL * Good for user-facing applications ### **b. Case Sensitive Collations (`_cs`)** * `'A' != 'a'` * Used when case must be preserved (e.g., passwords) ### **c. Accent Sensitive Collations (`_as`)** * `'e' != 'é'` * Important for supporting multiple languages ### **d. Binary Collation (`_bin`)** * Compares bytes directly * Strictest comparison * Fastest performance * Used for internal keys, identifiers ## **Common MySQL Collations and Their Meanings**


Collation Name	Character Set	Meaning / Behavior
`utf8mb4_general_ci`	`utf8mb4`	Case-insensitive, fast, simplified Unicode collation. Suitable for general use.
`utf8mb4_unicode_ci`	`utf8mb4`	Case-insensitive, full Unicode-compliant. Slower but accurate multilingual sorting.
`utf8mb4_unicode_520_ci`	`utf8mb4`	Same as above, but based on Unicode 5.2 standard. Better for newer Unicode versions.
`utf8mb4_bin`	`utf8mb4`	Binary collation: case-sensitive and accent-sensitive. Fastest and strict comparison.
`utf8mb4_general_cs`	`utf8mb4`	Case-sensitive variant of `general_ci`. `'a' != 'A'`.
`utf8mb4_0900_ai_ci`	`utf8mb4`	Unicode 9.0, accent-insensitive, case-insensitive. Used in MySQL 8.0+.
`utf8mb4_0900_as_cs`	`utf8mb4`	Unicode 9.0, accent-sensitive, case-sensitive.
`utf8mb4_0900_bin`	`utf8mb4`	Unicode 9.0 binary collation. Strictest match.
`latin1_swedish_ci`	`latin1`	Default for `latin1`, case-insensitive.
`latin1_general_ci`	`latin1`	Case-insensitive, faster for general use in Western languages.
`latin1_general_cs`	`latin1`	Case-sensitive version of `latin1_general_ci`.
`ascii_general_ci`	`ascii`	Case-insensitive ASCII collation.
`ascii_bin`	`ascii`	Binary collation for ASCII characters. Case-sensitive and fast.
`utf8_general_ci`	`utf8`	Same as `utf8mb4_general_ci` but with older 3-byte character support only.
`utf8_unicode_ci`	`utf8`	Unicode-compliant collation for `utf8` character set.
`utf8_bin`	`utf8`	Binary collation for 3-byte `utf8`.

## **How to Set & Check Collation** ### **Set Collation** **1. Server Level (default)**\ Set in configuration file (`my.cnf` or `my.ini`): ```ini [mysqld] collation-server = utf8mb4_general_ci ``` **2. Database Level** ```sql CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; ``` **3. Table Level** ```sql CREATE TABLE users ( name VARCHAR(100) ) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; ``` **4. Column Level** ```sql CREATE TABLE users ( name VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ); ``` **5. Query Level (for comparison)** ```sql SELECT * FROM users WHERE name COLLATE utf8mb4_general_ci = 'john'; ``` **6. In application.properties file (Spring)** ``` spring.datasource.url=jdbc:mysql://localhost:3306/your_database_name?useUnicode=true&characterEncoding=UTF-8&useSSL=false spring.datasource.username=your_username spring.datasource.password=your_password spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver ``` ### **Check Current Collation** ```sql SELECT @@collation_database; SELECT @@collation_server; SELECT COLLATION(name) FROM users; ``` ### **Changing Collation** Change collation for a column: ```sql ALTER TABLE users MODIFY name VARCHAR(100) COLLATE utf8mb4_unicode_ci; ``` ## **Practical Scenarios** **a. Usernames Must Be Unique and Case-Sensitive** ```sql VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin ``` **b. Searching Names Without Case Sensitivity** ```sql VARCHAR(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ``` **c. Sorting Multilingual Content Correctly** ```sql utf8mb4_unicode_ci` or `utf8mb4_unicode_520_ci ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://www.pranaypourkar.co.in/the-programmers-guide/database/platform-specific-features/mysql/data-types/collation.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.