How Can I Identify Character Sets in MySQL Databases, Tables, and Columns?

Identify character sets in MySQL databases, tables and columns

This article introduces the method of obtaining the character set used by various MySQL entities.

Default character set for database (schema)

The following query retrieves the default character set for a specified database:

<code class="language-sql">SELECT default_character_set_name FROM information_schema.SCHEMATA
WHERE schema_name = "mydatabasename";</code>

Character set of table

To determine the character set of a table, use the following query:

<code class="language-sql">SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "mydatabasename"
  AND T.table_name = "tablename";</code>

Character set of column

Finally, to get the character set of a specific column, execute the following query:

<code class="language-sql">SELECT character_set_name FROM information_schema.`COLUMNS`
WHERE table_schema = "mydatabasename"
  AND table_name = "tablename"
  AND column_name = "columnname";</code>

By leveraging these queries, developers can effectively manage and use character sets in MySQL databases, tables, and columns.

The above is the detailed content of How Can I Identify Character Sets in MySQL Databases, Tables, and Columns?. For more information, please follow other related articles on the PHP Chinese website!