- MySQL Basics
- MySQL - Home
- MySQL - Introduction
- MySQL - Features
- MySQL - Versions
- MySQL - Variables
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Node.js Syntax
- MySQL - Java Syntax
- MySQL - Python Syntax
- MySQL - Connection
- MySQL - Workbench
- MySQL Databases
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Show Database
- MySQL - Copy Database
- MySQL - Database Export
- MySQL - Database Import
- MySQL - Database Info
- MySQL Users
- MySQL - Create Users
- MySQL - Drop Users
- MySQL - Show Users
- MySQL - Change Password
- MySQL - Grant Privileges
- MySQL - Show Privileges
- MySQL - Revoke Privileges
- MySQL - Lock User Account
- MySQL - Unlock User Account
- MySQL Tables
- MySQL - Create Tables
- MySQL - Show Tables
- MySQL - Alter Tables
- MySQL - Rename Tables
- MySQL - Clone Tables
- MySQL - Truncate Tables
- MySQL - Temporary Tables
- MySQL - Repair Tables
- MySQL - Describe Tables
- MySQL - Add/Delete Columns
- MySQL - Show Columns
- MySQL - Rename Columns
- MySQL - Table Locking
- MySQL - Drop Tables
- MySQL - Derived Tables
- MySQL Queries
- MySQL - Queries
- MySQL - Constraints
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Replace Query
- MySQL - Insert Ignore
- MySQL - Insert on Duplicate Key Update
- MySQL - Insert Into Select
- MySQL Indexes
- MySQL - Indexes
- MySQL - Create Index
- MySQL - Drop Index
- MySQL - Show Indexes
- MySQL - Unique Index
- MySQL - Clustered Index
- MySQL - Non-Clustered Index
- MySQL Operators and Clauses
- MySQL - Where Clause
- MySQL - Limit Clause
- MySQL - Distinct Clause
- MySQL - Order By Clause
- MySQL - Group By Clause
- MySQL - Having Clause
- MySQL - AND Operator
- MySQL - OR Operator
- MySQL - Like Operator
- MySQL - IN Operator
- MySQL - ANY Operator
- MySQL - EXISTS Operator
- MySQL - NOT Operator
- MySQL - NOT EQUAL Operator
- MySQL - IS NULL Operator
- MySQL - IS NOT NULL Operator
- MySQL - Between Operator
- MySQL - UNION Operator
- MySQL - UNION vs UNION ALL
- MySQL - MINUS Operator
- MySQL - INTERSECT Operator
- MySQL - INTERVAL Operator
- MySQL Joins
- MySQL - Using Joins
- MySQL - Inner Join
- MySQL - Left Join
- MySQL - Right Join
- MySQL - Cross Join
- MySQL - Full Join
- MySQL - Self Join
- MySQL - Delete Join
- MySQL - Update Join
- MySQL - Union vs Join
- MySQL Keys
- MySQL - Unique Key
- MySQL - Primary Key
- MySQL - Foreign Key
- MySQL - Composite Key
- MySQL - Alternate Key
- MySQL Triggers
- MySQL - Triggers
- MySQL - Create Trigger
- MySQL - Show Trigger
- MySQL - Drop Trigger
- MySQL - Before Insert Trigger
- MySQL - After Insert Trigger
- MySQL - Before Update Trigger
- MySQL - After Update Trigger
- MySQL - Before Delete Trigger
- MySQL - After Delete Trigger
- MySQL Data Types
- MySQL - Data Types
- MySQL - VARCHAR
- MySQL - BOOLEAN
- MySQL - ENUM
- MySQL - DECIMAL
- MySQL - INT
- MySQL - FLOAT
- MySQL - BIT
- MySQL - TINYINT
- MySQL - BLOB
- MySQL - SET
- MySQL Regular Expressions
- MySQL - Regular Expressions
- MySQL - RLIKE Operator
- MySQL - NOT LIKE Operator
- MySQL - NOT REGEXP Operator
- MySQL - regexp_instr() Function
- MySQL - regexp_like() Function
- MySQL - regexp_replace() Function
- MySQL - regexp_substr() Function
- MySQL Fulltext Search
- MySQL - Fulltext Search
- MySQL - Natural Language Fulltext Search
- MySQL - Boolean Fulltext Search
- MySQL - Query Expansion Fulltext Search
- MySQL - ngram Fulltext Parser
- MySQL Functions & Operators
- MySQL - Date and Time Functions
- MySQL - Arithmetic Operators
- MySQL - Numeric Functions
- MySQL - String Functions
- MySQL - Aggregate Functions
- MySQL Misc Concepts
- MySQL - NULL Values
- MySQL - Transactions
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - SubQuery
- MySQL - Comments
- MySQL - Check Constraints
- MySQL - Storage Engines
- MySQL - Export Table into CSV File
- MySQL - Import CSV File into Database
- MySQL - UUID
- MySQL - Common Table Expressions
- MySQL - On Delete Cascade
- MySQL - Upsert
- MySQL - Horizontal Partitioning
- MySQL - Vertical Partitioning
- MySQL - Cursor
- MySQL - Stored Functions
- MySQL - Signal
- MySQL - Resignal
- MySQL - Character Set
- MySQL - Collation
- MySQL - Wildcards
- MySQL - Alias
- MySQL - ROLLUP
- MySQL - Today Date
- MySQL - Literals
- MySQL - Stored Procedure
- MySQL - Explain
- MySQL - JSON
- MySQL - Standard Deviation
- MySQL - Find Duplicate Records
- MySQL - Delete Duplicate Records
- MySQL - Select Random Records
- MySQL - Show Processlist
- MySQL - Change Column Type
- MySQL - Reset Auto-Increment
- MySQL - Coalesce() Function
MySQL - SHOW COLLATION Statement
MySQL SHOW COLLATION Statement
MySQL collation is a set of rules to verify/compare each character of a character set. Each character set have at least one collation (it can have more) and a default collation. No two-character sets can have same collation.
The SHOW COLLATION statement displays the list of collations supported by the server. In addition to the names of the available collations this statement also provides the following details −
Charset − The charset name.
Id − The Id of the collation
Default − A Boolean value representing whether the given collation is default value of the character set.
Compiled − A Boolean value specifying whether the character set is compiled into the server.
Sortlen − An integer value specifying the amount of memory required to sort strings in the character set.
Syntax
Following is the syntax of the SHOW COLLATION statement −
SHOW COLLATION [LIKE 'pattern' | WHERE expr]
Example
You can get all the collations (and other details mention above) available in the current database using the following query −
SHOW COLLATION\G;
Output
Following is the output of the above query −
************ 1. row ************ Collation: armscii8_bin Charset: armscii8 Id: 64 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 2. row ************ Collation: armscii8_general_ci Charset: armscii8 Id: 32 Default: Yes Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 3. row ************ Collation: ascii_bin Charset: ascii Id: 65 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 4. row ************ Collation: ascii_general_ci Charset: ascii Id: 11 Default: Yes Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 5. row ************ Collation: big5_bin Charset: big5 Id: 84 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 6. row ************ Collation: big5_chinese_ci Charset: big5 Id: 1 Default: Yes Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 7. row ************ Collation: binary Charset: binary Id: 63 Default: Yes Compiled: Yes Sortlen: 1 Pad_attribute: NO PAD ************ 8. row ************ Collation: cp1250_bin Charset: cp1250 Id: 66 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 9. row ************ Collation: cp1250_croatian_ci Charset: cp1250 Id: 44 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 10. row ************ Collation: cp1250_czech_cs Charset: cp1250 Id: 34 Default: Compiled: Yes Sortlen: 2 Pad_attribute: PAD SPACE ************ 11. row ************ Collation: cp1250_general_ci Charset: cp1250 Id: 26 Default: Yes Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 12. row ************ Collation: cp1250_polish_ci Charset: cp1250 Id: 99 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 13. row ************ Collation: cp1251_bin Charset: cp1251 Id: 50 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 14. row ************ Collation: cp1251_bulgarian_ci Charset: cp1251 Id: 14 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 15. row ************ Collation: cp1251_general_ci Charset: cp1251 Id: 51 Default: Yes Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 16. row ************ Collation: cp1251_general_cs Charset: cp1251 Id: 52 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 17. row ************ Collation: cp1251_ukrainian_ci Charset: cp1251 Id: 23 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 18. row ************ Collation: cp1256_bin Charset: cp1256 Id: 67 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 19. row ************ Collation: cp1256_general_ci Charset: cp1256 Id: 57 Default: Yes Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************ 20. row ************ Collation: cp1257_bin Charset: cp1257 Id: 58 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
The LIKE clause
Using the LIKE clause, you can specify a pattern to retrieve names other details of the collations that match the given pattern.
SHOW COLLATION LIKE 'utf32%'\G;
Output
The above query produces the following output −
************* 1. row ************* Collation: utf32_bin Charset: utf32 Id: 61 Default: Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************* 2. row ************* Collation: utf32_croatian_ci Charset: utf32 Id: 181 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 3. row ************* Collation: utf32_czech_ci Charset: utf32 Id: 170 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 4. row ************* Collation: utf32_danish_ci Charset: utf32 Id: 171 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 5. row ************* Collation: utf32_esperanto_ci Charset: utf32 Id: 177 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 6. row ************* Collation: utf32_estonian_ci Charset: utf32 Id: 166 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 7. row ************* Collation: utf32_general_ci Charset: utf32 Id: 60 Default: Yes Compiled: Yes Sortlen: 1 Pad_attribute: PAD SPACE ************* 8. row ************* Collation: utf32_german2_ci Charset: utf32 Id: 180 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 9. row ************* Collation: utf32_hungarian_ci Charset: utf32 Id: 178 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 10. row ************* Collation: utf32_icelandic_ci Charset: utf32 Id: 161 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 11. row ************* Collation: utf32_latvian_ci Charset: utf32 Id: 162 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 12. row ************* Collation: utf32_lithuanian_ci Charset: utf32 Id: 172 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 13. row ************* Collation: utf32_persian_ci Charset: utf32 Id: 176 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 14. row ************* Collation: utf32_polish_ci Charset: utf32 Id: 165 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 15. row ************* Collation: utf32_romanian_ci Charset: utf32 Id: 163 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 16. row ************* Collation: utf32_roman_ci Charset: utf32 Id: 175 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 17. row ************* Collation: utf32_sinhala_ci Charset: utf32 Id: 179 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 18. row ************* Collation: utf32_slovak_ci Charset: utf32 Id: 173 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 19. row ************* Collation: utf32_slovenian_ci Charset: utf32 Id: 164 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 20. row ************* Collation: utf32_spanish2_ci Charset: utf32 Id: 174 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 21. row ************* Collation: utf32_spanish_ci Charset: utf32 Id: 167 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 22. row ************* Collation: utf32_swedish_ci Charset: utf32 Id: 168 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 23. row ************* Collation: utf32_turkish_ci Charset: utf32 Id: 169 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 24. row ************* Collation: utf32_unicode_520_ci Charset: utf32 Id: 182 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 25. row ************* Collation: utf32_unicode_ci Charset: utf32 Id: 160 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE ************* 26. row ************* Collation: utf32_vietnamese_ci Charset: utf32 Id: 183 Default: Compiled: Yes Sortlen: 8 Pad_attribute: PAD SPACE
The WHERE clause
You can use the WHERE clause of the SHOW STATUS statements to retrieve names of the collations which match the specified condition.
SHOW COLLATION WHERE Charset = 'cp1251';
Output
After executing the above query, it will generate the following output −
Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
---|---|---|---|---|---|---|
cp1251_bin | cp1251 | 50 | Yes | 1 | PAD SPACE | |
cp1251_bulgarian_ci | cp1251 | 14 | Yes | 1 | PAD SPACE | |
cp1251_general_ci | cp1251 | 51 | Yes | Yes | 1 | PAD SPACE |
cp1251_general_cs | cp1251 | 52 | Yes | 1 | PAD SPACE | |
cp1251_ukrainian_ci | cp1251 | 23 | Yes | 1 | PAD SPACE |
To Continue Learning Please Login
Login with Google