distal-attribute
distal-attribute
distal-attribute
distal-attribute

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

User, date Message
Written by Abdull
2 years ago
Category: Feature discussion
8 posts since Sat, 03 Nov 12
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.
Written by TTSneko
2 years ago
41 posts since Thu, 19 Jul 12
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.
Written by Abdull
2 years ago
8 posts since Sat, 03 Nov 12
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?
Written by kalvaro
2 years ago
587 posts since Thu, 29 Nov 07
Don't you get this?

SQL Error (1253): COLLATION 'utf8_general_ci' is not valid for CHARACTER SET 'latin1'

Written by Abdull
2 years ago
8 posts since Sat, 03 Nov 12
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).
Written by jfalchMoney, Euro
2 years ago
380 posts since Sat, 17 Oct 09
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.