Character Sets

Oracle Usage

Oracle supports most national and international encoded character set standards including extensive support for Unicode.

Oracle provides two scalar string-specific data types:

Character sets in Oracle are defined at the instance level (Oracle 11g) or the pluggable database level (Oracle 12c R2). In Pre-12cR2 Oracle databases, the character set for the root container and all pluggable databases were required to be identical.

Oracle 18c updates AL32UTF8 and AL16UTF16 character sets to Unicode standard version 9.0.

UTF8 Unicode

In Oracle, you can use the AL32UTF8 character set. Oracle provides encoding of ASCII characters as single-byte for Latin characters, two-bytes for some European and Middle-Eastern languages, and three-bytes for certain South and East-Asian characters. Therefore, Unicode storage requirements are usually higher when compared non-Unicode character sets.

Character Set Migration

Two options exist for modifying existing Instance-level or database-level character sets:

As of 2012, use of the CSALTER utility for character set migrations is deprecated.

Oracle Database 12c Release 1 (12.1.0.1) complies with version 6.1 of the Unicode standard.

Oracle Database 12c Release 2 (12.1.0.2) extends the compliance to version 6.2 of the Unicode standard.

UTF-8 is supported through the AL32UTF8 CS and is valid as both the client and database character sets.

UTF-16BE is supported through AL16UTF16 and is valid as the national (NCHAR) character set.

For more information, see Choosing a Character Set, Locale Data, and Supporting Multilingual Databases with Unicode in the Oracle documentation.

MySQL Usage

MySQL supports a variety of different character sets including support for both single-byte and multi-byte languages. The default character set is specified when initializing a MySQL database cluster with initdb. Each individual database created on the MySQL cluster supports individual character sets defined as part of database creation.

To query the available character sets, use the INFORMATION_SCHEMA CHARACTER_SETS table or the SHOW CHARACTER SET statement.

All character sets have at least one collation, and most character sets have more. To list the display collations for a character set, use the INFORMATION_SCHEMA COLLATIONS table or the SHOW COLLATION statement.

Collations have these general characteristics:

Examples

Create a database named test01 which uses the Korean EUC_KR Encoding the and the ko_KR locale.

CREATE DATABASE test01 CHARACTER SET = euckr COLLATE = euckr_korean_ci;

View the character sets configured for each database by querying the System Catalog.

SELECT SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA;

Convert a character set and collation using the ALTER DATABASE command.

ALTER DATABASE test01 CHARACTER SET = ucs2 COLLATE = ucs2_general_ci;

MySQL supports conversion of character sets between server and client for specific character set combinations with the parameter character_set_client and character_set_connection. For more information, see Connection Character Sets and Collations.

In MySQL, you can specify the sort order and character classification behavior on a per-column level. Specify specific collations for individual table columns.

CREATE TABLE lang(
latin1_col CHAR(10) CHARACTER SET latin1 COLLATE latin1_german1_ci,
latin2_col CHAR(10) CHARACTER SET latin2);

Summary

SELECT * FROM NLS_DATABASE_PARAMETERS;

SELECT SCHEMA_NAME,
    DEFAULT_CHARACTER_SET_NAME,
    DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA;

ALTER DATABASE test01
CHARACTER SET = ucs2
COLLATE = ucs2_general_ci;

For more information, see Character Sets, Collations, Unicode and Database Character Set and Collation in the MySQL documentation.