- 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 - SET NAMES Statement
A character set of MySQL is the set of legal characters that are allowed in a string. MySQL provides several character-sets you can see the list of all the available character sets using the SHOW CHARACTER SET statement.
SHOW CHARACTER SET\G;
Output
Following is the output of the above query −
************* 1. row ************* Charset: armscii8 Description: ARMSCII-8 Armenian Default collation: armscii8_general_ci Maxlen: 1 ************* 2. row ************* Charset: ascii Description: US ASCII Default collation: ascii_general_ci Maxlen: 1 ************* 3. row ************* Charset: big5 Description: Big5 Traditional Chinese Default collation: big5_chinese_ci Maxlen: 2 ************* 4. row ************* Charset: binary Description: Binary pseudo charset Default collation: binary Maxlen: 1 ************* 5. row ************* Charset: cp1250 Description: Windows Central European Default collation: cp1250_general_ci Maxlen: 1 ************* 6. row ************* Charset: cp1251 Description: Windows Cyrillic Default collation: cp1251_general_ci Maxlen: 1 ************* 7. row ************* Charset: cp1256 Description: Windows Arabic Default collation: cp1256_general_ci Maxlen: 1 ************* 8. row ************* Charset: cp1257 Description: Windows Baltic Default collation: cp1257_general_ci Maxlen: 1 ************* 9. row ************* Charset: cp850 Description: DOS West European Default collation: cp850_general_ci Maxlen: 1 ************* 10. row ************* Charset: cp852 Description: DOS Central European Default collation: cp852_general_ci Maxlen: 1 ************* 11. row ************* Charset: cp866 Description: DOS Russian Default collation: cp866_general_ci Maxlen: 1 ************* 12. row ************* Charset: cp932 Description: SJIS for Windows Japanese Default collation: cp932_japanese_ci Maxlen: 2 ************* 13. row ************* Charset: dec8 Description: DEC West European Default collation: dec8_swedish_ci Maxlen: 1 ************* 14. row ************* Charset: eucjpms Description: UJIS for Windows Japanese Default collation: eucjpms_japanese_ci Maxlen: 3 ************* 15. row ************* Charset: euckr Description: EUC-KR Korean Default collation: euckr_korean_ci Maxlen: 2 ************* 16. row ************* Charset: gb18030 Description: China National Standard GB18030 Default collation: gb18030_chinese_ci Maxlen: 4 ************* 17. row ************* Charset: gb2312 Description: GB2312 Simplified Chinese Default collation: gb2312_chinese_ci Maxlen: 2 ************* 18. row ************* Charset: gbk Description: GBK Simplified Chinese Default collation: gbk_chinese_ci Maxlen: 2 ************* 19. row ************* Charset: geostd8 Description: GEOSTD8 Georgian Default collation: geostd8_general_ci Maxlen: 1 ************* 20. row ************* Charset: greek Description: ISO 8859-7 Greek Default collation: greek_general_ci Maxlen: 1 ************* 21. row ************* Charset: hebrew Description: ISO 8859-8 Hebrew Default collation: hebrew_general_ci Maxlen: 1 ************* 22. row ************* Charset: hp8 Description: HP West European Default collation: hp8_english_ci Maxlen: 1 ************* 23. row ************* Charset: keybcs2 Description: DOS Kamenicky Czech-Slovak Default collation: keybcs2_general_ci Maxlen: 1 ************* 24. row ************* Charset: koi8r Description: KOI8-R Relcom Russian Default collation: koi8r_general_ci Maxlen: 1 ************* 25. row ************* Charset: koi8u Description: KOI8-U Ukrainian Default collation: koi8u_general_ci Maxlen: 1 ************* 26. row ************* Charset: latin1 Description: cp1252 West European Default collation: latin1_swedish_ci Maxlen: 1 ************* 27. row ************* Charset: latin2 Description: ISO 8859-2 Central European Default collation: latin2_general_ci Maxlen: 1 ************* 28. row ************* Charset: latin5 Description: ISO 8859-9 Turkish Default collation: latin5_turkish_ci Maxlen: 1 ************* 29. row ************* Charset: latin7 Description: ISO 8859-13 Baltic Default collation: latin7_general_ci Maxlen: 1 ************* 30. row ************* Charset: macce Description: Mac Central European Default collation: macce_general_ci Maxlen: 1 ************* 31. row ************* Charset: macroman Description: Mac West European Default collation: macroman_general_ci Maxlen: 1 ************* 32. row ************* Charset: sjis Description: Shift-JIS Japanese Default collation: sjis_japanese_ci Maxlen: 2 ************* 33. row ************* Charset: swe7 Description: 7bit Swedish Default collation: swe7_swedish_ci Maxlen: 1 ************* 34. row ************* Charset: tis620 Description: TIS620 Thai Default collation: tis620_thai_ci Maxlen: 1 ************* 35. row ************* Charset: ucs2 Description: UCS-2 Unicode Default collation: ucs2_general_ci Maxlen: 2 ************* 36. row ************* Charset: ujis Description: EUC-JP Japanese Default collation: ujis_japanese_ci Maxlen: 3 ************* 37. row ************* Charset: utf16 Description: UTF-16 Unicode Default collation: utf16_general_ci Maxlen: 4 ************* 38. row ************* Charset: utf16le Description: UTF-16LE Unicode Default collation: utf16le_general_ci Maxlen: 4 ************* 39. row ************* Charset: utf32 Description: UTF-32 Unicode Default collation: utf32_general_ci Maxlen: 4 ************* 40. row ************* Charset: utf8mb3 Description: UTF-8 Unicode Default collation: utf8mb3_general_ci Maxlen: 3 ************* 41. row ************* Charset: utf8mb4 Description: UTF-8 Unicode Default collation: utf8mb4_0900_ai_ci Maxlen: 4
You can get the name of the default character set using the following query −
SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = "test";
Output
The above query produces the following output −
DEFAULT_CHARACTER_SET_NAME |
---|
utf8mb4 |
The SET NAMES Statement
The MySQL SET NAMES Statement is used to set values to the character_set_client, character_set_connection, and character_set_results session system variables.
Syntax
Following is the syntax of the MySQL SET NAMES Statement −
SET NAMES {'charset_name' [COLLATE 'collation_name'] | DEFAULT}
Where, 'charset_name' is the name of the character set.
Example
Following query sets the values to the character_set_client, character_set_connection, and character_set_results variables to 'utf8'. −
SET NAMES 'utf8';
Verification
You can verify the current value of the character_set_client and character_set_results variables using the show variables like statement as shown below −
show variables like "character_set_client";
Output
After executing the above mysql query, it produces the output shown below −
Variable_name | Value |
---|---|
character_set_client | utf8 |
show variables like "character_set_results";
Output
Following is the output of the above query −
Variable_name | Value |
---|---|
character_set_results | utf8 |
show variables like "character_set_connection";
Output
The above mysql query produces the following output −
Variable_name | Value |
---|---|
character_set_connection | utf8 |
The collate clause
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. SHOW CHARACTER SET statement. provides the default collation for every character set.
You can specify a collation to a character set explicitly, using the COLLATE clause.
Example
Following query sets the collation of the character set to ascii_bin −
SET NAMES ascii COLLATE ascii_bin;
You can get the all the allowed collations for a character set using the SHOW COLLATION statement as follows −
SHOW COLLATION WHERE Charset = 'ascii';
Output
After executing the above query, it will produce the following output −
Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute |
---|---|---|---|---|---|---|
ascii_bin | ascii | 65 | Yes | 1 | PAD SPACE | |
ascii_general_ci | ascii | 11 | Yes | Yes | 1 | PAD SPACE |
The DEFAULT clause
You can set the collation and mapping to the default values using the DEFAULT clause.
SET NAMES DEFAULT;
To Continue Learning Please Login
Login with Google