How to change table encoding without altering the data?

I have a few tables that are marked as utf8 (probably due to mistakes when importing from another server, several years ago), although their data is actually greek (single byte). Is there any way I can change the encoding of the table without having MySQL try to convert the data too?

Edit to avoid more misunderstandings: The data IS NOT UTF-8. It's just marked as such. I want to change the encoding of the table to reflect the actual encoding of the data, so that I can proceed to actually con开发者_如何学JAVAvert them to utf8.

Thanks.

First thing you need to think about is - why change what is working? UTF-8 is suitable to storing Greek.

Are you really so much out of space on the database server that such a drastic change will save so much?

As for changing the encoding without converting the data - no, this is not possible. The data is stored as UTF-8 and will need to be converted to the correct encoding, or you will end up with an unreadable set of columns.

Update:

If the data is already in the right encoding, changing the encoding without converting may still end up with corrupt data. Why? Because UTF-8 is not a single byte character set and the column type determines how the database engine stores and accesses the data on disk.

I suggest experimenting - create a UTF-8 column in a test table, populate it with existing data and convert. If the column type change succeeds without conversion, you are good to go.

Apparently the solution is to export the raw data (SELECT * INTO OUTFILE ...), use iconv from the command line to convert from utf8 to latin1 and then import back (LOAD DATA INFILE ... CHARACTER SET GREEK) :)

This is a veeeery old post, but still no correct answer...

I've hit this question because my approach to the problem I had used earlier was somewhat not working. Fortunately, I've discovered that my problem was not my approach, but the tool - phpMyAdmin. I've done the same using the MySQL console and it works perfectly.

The solution

If the mysql thinks that the data is in one charset (i.e. utf8) but the actual data is in some other charset (i.e. latin1) then the solution is to convert the column(s) with the text to a charset unaware type like VARBINARY, BLOB/MEDIUMBLOB... and then convert back to the charset aware column(s) while setting the correct charset.

In case you have a MEDIUMTEXT type column y in table x containing data in latin1 while mysql thinking that it is in some other charset (in your case utf8), use the following recovery intructions:

mysql> ALTER TABLE `x` CHANGE `y` `y` MEDIUMBLOB;
mysql> ALTER TABLE `x` CHANGE `y` `y` MEDIUMTEXT CHARACTER SET latin1 COLLATE latin1_general_ci;

You have to of course make sure that the charset is supported by your instance of MySQL.