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

How to determine the character set of MySQL database, table and column

In MySQL, the character set and collation determine how text data is encoded and processed. This article explores methods of obtaining character set details for databases, tables, and their columns.

Database character set

To retrieve the default character set for a database, execute the following query:

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

Replace "mydatabasename" with the actual database name you want to query.

Table character set

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>

Replace "mydatabasename" and "tablename" with the corresponding database and table names.

Column character set

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>

Be sure to replace the three placeholder names ("mydatabasename", "tablename", and "columnname") with their corresponding values.

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