Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Getting MySQL to display the encoding used for a particular column

Tags:

mysql

encoding

The MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/charset-syntax.html) says:

There are default settings for character sets and collations at four levels: server, database, table, and column. The description in the following sections may appear complex, but it has been found in practice that multiple-level defaulting leads to natural and obvious results.

I'd like to interrogate a specific CHAR/VARCHAR/TEXT column and find out what encoding MySQL thinks it is. Is there an easy way to do this? I know I can use SHOW CREATE TABLE <table> to see the default charset for the table, but I'd like to do the same thing on the column level, as the docs suggest that it might not be the same as the table default.

like image 434
grumbler Avatar asked Dec 29 '11 00:12

grumbler


People also ask

How do I fetch a specific column in MySQL?

If you want to select only specific columns, replace the * with the names of the columns, separated by commas. The following statement selects just the name_id, firstname and lastname fields from the master_name table.

How do I find the charset of a table in MySQL?

To see the default character set and collation for a given database, use these statements: USE db_name; SELECT @@character_set_database, @@collation_database; Alternatively, to display the values without changing the default database: SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.

How do I find the character set in SQL?

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


1 Answers

You can do this in the information_schema.COLUMNS table.

SELECT 
  COLUMN_NAME,
  TABLE_NAME,
  CHARACTER_SET_NAME,
  COLUMN_TYPE,
  COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'your_database_name'

Example output from a Gallery2 database:

+-------------------------+--------------------------+--------------------+--------------+-----------------+
| COLUMN_NAME             | TABLE_NAME               | CHARACTER_SET_NAME | COLUMN_TYPE  | COLLATION_NAME  |
+-------------------------+--------------------------+--------------------+--------------+-----------------+
| g_accessListId          | g2_AccessMap             | NULL               | int(11)      | NULL            |
| g_userOrGroupId         | g2_AccessMap             | NULL               | int(11)      | NULL            |
| g_permission            | g2_AccessMap             | NULL               | int(11)      | NULL            |
| g_itemId                | g2_AccessSubscriberMap   | NULL               | int(11)      | NULL            |
| g_accessListId          | g2_AccessSubscriberMap   | NULL               | int(11)      | NULL            |
| g_id                    | g2_AlbumItem             | NULL               | int(11)      | NULL            |
| g_theme                 | g2_AlbumItem             | utf8               | varchar(32)  | utf8_general_ci |
| g_orderBy               | g2_AlbumItem             | utf8               | varchar(128) | utf8_general_ci |
| g_orderDirection        | g2_AlbumItem             | utf8               | varchar(32)  | utf8_general_ci |
like image 128
Michael Berkowski Avatar answered Nov 14 '22 05:11

Michael Berkowski