CODEWITHSUNDEEP
mysqlunicodealter-table
buck
buck

Reputation: 1532

MySQL column name is a weird character - how do I change it?

I'm examining a table in MySQL that has a weird column name. I want to change the name of the column to not be weird. I can't figure out how to do so.

Firstly, if I first do

SET NAMES utf8;
DESC `tblName`;

I get

| Ԫ                           | varchar(255)  | YES  | MUL | NULL    |                |

Instead, doing

SET NAMES latin1;
DESC `tblName`;

Results in

| ?                           | varchar(255)  | YES  | MUL | NULL    |                |

Fair enough - this make me think the column name is simply a latin1 question mark. But this statement doesn't work:

mysql> ALTER TABLE `tblName` CHANGE COLUMN `?` `newName` VARCHAR(255);
ERROR 1054 (42S22): Unknown column '?' in 'tblName'

So I went to the information_schema table for some info:

mysql> SELECT column_name, HEX(column_name), ordinal_position FROM information_schema.columns WHERE table_schema = 'myschema' AND table_name = 'tblName' ;
| ?                           | D4AA                                                   |               48 |

I looked up this hex point, and assuming I looked it up correctly (which may not be true), I determined this character is "풪" which is the "hangul syllable pweoj". So I tried that in an alter table statement to no avail:

ALTER TABLE `tblName` change column `풪` `newName` VARCHAR(255);

So that's where I'm stuck.

Upvotes: 2

Views: 1915

Answers (2)

Alastair McCormack
Alastair McCormack

Reputation: 27704

I believe that Ԫ (question mark in a box) is actually shown because your system does not have a font at that code point. From your `hex(column_name)' we can see that the value is xD4AA, which is the UTF-8 value. This translates to Unicode point 052a for which I don't have the font either on my Windows box.

Setting the char set to latin1, simply meant that Mysql was unable to translate that char to a latin1/cp1252 value so replaced it with "?". (xD4AA could easily be translated to two cp1252 chars, "Ôª". For some reason Mysql chose not to. Perhaps it knew the original encoding?)

Now, how to rename the column? It should be as simple as you say with ALTER TABLE CHANGE COLUMN etc etc. However, it seems that the Mysql console doesn't play nice with non-ASCII chars, especially variable length chars found in UTF-8.

The solution was to pass the SQL as an argument to mysql from Bash instead. For example (Ensure terminal translation is UTF-8 before pasting Ԫ):

 mysql --default-character-set=utf8 -e "ALTER TABLE test change column Ԫ test varchar(255);" test

Upvotes: 2

buck
buck

Reputation: 1532

I figured out a way to do this (but I wonder if there's a better solution?)

I did a SHOW CREATE statement:

mysql> SHOW CREATE TABLE `tblName`;
...
`Ԫ` varchar(255) DEFAULT NULL,

I looked for the column in question, which was printed strangely (what you see above doesn't quite match it). The closing backtick wasn't visible. But I highlighted what was visible and pasted that into my ALTER TABLE and that finally fixed the issue.

Upvotes: 3

Related Questions