MySQL Bugs: #12743: INFORMATION_SCHEMA.TABLES is missing DEFAULT_CHARACTER_SET

Bug #12743	INFORMATION_SCHEMA.TABLES is missing DEFAULT_CHARACTER_SET_NAME field
Submitted:	23 Aug 2005 3:42	Modified:	13 Sep 2005 8:25
Reporter:	Timothy Smith	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.12 (recent BK pull)	OS:	Any (any)
Assigned to:	Jim Winstead	CPU Architecture:	Any

Description:
It isn't possible to get the default character set for a table.  The SCHEMATA table has DEFAULT_CHARACTER_SET_NAME, and the COLUMNS table has CHARACTER_SET_NAME.

TABLES needs DEFAULT_CHARACTER_SET_NAME as well.

How to repeat:
15:41 ~/m/50/m$ mysql -A information_schema
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.12-beta-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select default_character_set_name from tables where table_schema = 'test' and table_name = 'foo';
ERROR 1054 (42S22): Unknown column 'default_character_set_name' in 'field list'
mysql>

Note that you can get this by joining against INFORMATION_SCHEMA.COLLATIONS using the TABLE_COLLATION field in I_S.TABLES.

select character_set_name as default_character_set_name, table_collation as default_collation_name from information_schema.tables left join information_schema.collations on table_collation = collation_name where table_schema='information_schema' and table_name='tables';

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/28735

As this is a non-standard field, and it will duplicate existing field (collation. one can turn it into charset with a join, as Jim pointed out), we should not add it.