How to view the default character set of a database or table?

Abdull's profile image Abdull posted 11 years ago in Feature discussion Permalink
In HeidiSQL, when I select a host, there is a column called "Default collation" that shows me the default collation of each contained database.

And when I select a database, there also is a column called "Collation" that shows me the default collation of each contained table.

I'm curious to know why there aren't any columns like "(Default) character set" that show me the character set/charset of a database or table.
TTSneko's profile image TTSneko posted 11 years ago Permalink
The definition of "(Default) character set" indicates that you think too ASCII-table-related ... a collation is the modern day replacement of the former ASCII character sets and massively improves character (and data) handling. You should read up on the basic differences between (old) ASCII and (modern) Unicode.

In short, the default character set (as in ASCII code tables) has become redundant as ASCII character sets merely dictated the pool of visible characters. Unicode copes with them all and offers different preferences (e.g. while sorting). The result is a border-free method of storing/presenting data based on the requirements of modern international data exchange.

For example, the utf-8-general-ci collation nowadays replaces all former ASCII (basic & extended latin) character sets.
Abdull's profile image Abdull posted 11 years ago Permalink
Hm, actually I didn't intend my question to compare ASCII vs. UTF8. I give my question a second try by giving an example to convey what I meant:

Here is one table:
create table authorities1 (username varchar(255) not null,
authority varchar(255) not null )
DEFAULT CHARACTER SET utf8  															DEFAULT COLLATE utf8_general_ci;


Here is another table:
create table authorities2 (username varchar(255) not null,
authority varchar(255) not null )
DEFAULT CHARACTER SET latin1  															DEFAULT COLLATE utf8_general_ci;


Both tables have valid MySQL syntax. Notice how the authorities1 table uses the character set "utf8", while the authorities2 table uses the character set "latin1".

Now let's assume we have created both tables. Using HeidiSQL's GUI, we take a look at these tables. Where does HeidiSQL show that table "authorities1" has a (default) character set of UTF8 and table "authorities2" has a (default) character set of Latin1?
kalvaro's profile image kalvaro posted 11 years ago Permalink
Don't you get this?

SQL Error (1253): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'
Abdull's profile image Abdull posted 11 years ago Permalink
Yes, I get this error message. But let's assume there exists a collation that has two or more character sets associated with it, and the MySQL implementation accepts both.

...Or is it that for all and every cases, for now, the past, and in the future of MySQL, there is always a one-to-one relationship between collation and character set (bijective function)? Then of course it would suffice that HeidiSQL only has a collation column (though it wouldn't hurt that there also is a character set column).
jfalch's profile image jfalch posted 11 years ago Permalink
remarks aside, AFAIK HeidiSQL´s CREATE CODE tab for a table will show its create statement. This contains clauses for the tables´ character set and collation; if none are shown, I think misisng clauses indicate that the appropriate value is the same as the "default" value of the containing database.
A databases´ default character set and collation can be shown by right clicking on it in the tree on the left side of HeidiSQL, and choosing "Edit".

Having said that, I would advise you to check the text shown in the "authorities2" table´s CREATE CODE tab; possibly the MySQL server (NOT HeidiSQL) will have silently changed the "utf8_general_ci" to some other collation that is possible with latin1 (eg "latin1_swedish_ci") on table creation. The valid combinations are eg. shown in the system table information_schema.COLLATION_CHARACTER_SET_APPLICABILITY (on my MariaDB 5.5.27); look them up in the mysql docs if your server should not have this table.

Please login to leave a reply, or register at first.