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: | |
Category: | MySQL Server: Charsets | Severity: | S4 (Feature request) |
Version: | 5.0 | OS: | Any |
Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[7 Oct 2006 13:24]
Oli Sennhauser
[9 Oct 2006 13:09]
Valeriy Kravchuk
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)."
[21 Aug 2008 5:03]
Alexander Barkov
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.