MySQL Bugs: #23073: alter table ... character set keeps old character set on column

Bug #23073	alter table ... character set keeps old character set on column
Submitted:	7 Oct 2006 13:24	Modified:	21 Aug 2008 5:03
Reporter:	Oli Sennhauser	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S4 (Feature request)
Version:	5.0	OS:	Any
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
Altering a table with alter table ... character set = utf8 keeps the character set on column. this is imho not what user wants because it changes behaviour (inherit from table default)

How to repeat:
create table test (data varchar(32)) character set = latin1;

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `data` varchar(32) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

alter table test character set utf8;

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `data` varchar(32) character set latin1 default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

Suggested fix:
mysql should not change the default behaviour (inherit from default)

Thank you for a reasonable feature request. I can not call it a bug, as current implementation is described in the manual, http://dev.mysql.com/doc/refman/5.0/en/alter-table.html:

"If you want to change the table default character set and all character columns (CHAR, VARCHAR, TEXT) to a new character set, use a statement like this:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Warning: The preceding operation converts column values between the character sets. This is not what you want if you have a column in one character set (like latin1) but the stored values actually use some other, incompatible character set (like utf8). In this case, you have to do the following for each such column:

ALTER TABLE t1 CHANGE c1 c1 BLOB;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

The reason this works is that there is no conversion when you convert to or from BLOB columns.

If you specify CONVERT TO CHARACTER SET binary, the CHAR, VARCHAR, and TEXT columns are converted to their corresponding binary string types (BINARY, VARBINARY, BLOB). This means that the columns no longer will have a character set and a subsequent CONVERT TO operation will not apply to them.

To change only the default character set for a table, use this statement:

ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

The word DEFAULT is optional. The default character set is the character set that is used if you do not specify the character set for a new column which you add to a table (for example, with ALTER TABLE ... ADD column)."

Closing as not a bug.

"ALTER TABLE t1 [DEFAULT] CHARACTER SET newcs" only changes table default
character set and does not change the column.

If you need to change column, you should use:

"ALTER TABLE t1 CONVERT TO CHARACTER SET newcs".

This is by design.