0

I am trying to change the collation on a bunch of columns. I don't want to mess up my current data, so I've been looking at doing something like in this answer:

ALTER TABLE something MODIFY name BLOB;

The next step is to convert the column to a nonbinary data type with the proper character set:

ALTER TABLE something MODIFY name VARCHAR(12) CHARACTER SET hebrew COLLATE hebrew_bin;

Or Try with this:

ALTER TABLE something MODIFY name VARCHAR(12) CHARACTER SET utf8 COLLATE utf8_unicode_ci

Unfortunately, mysql won't let me convert an indexed column to a blob.

SQL error: [1170] - BLOB/TEXT column 'baz' used in key specification without a key length

Query: ALTER TABLE foo.bar MODIFY baz blob;

Is there a way around this? Or do I need to somehow remove my indexes and rebuild them after the conversion?

Community
  • 1
  • 1
MirroredFate
  • 12,396
  • 14
  • 68
  • 100
  • Collation orders chars in a character set and indexes are sorted so you will have to drop them anyway and rebuild on a new collation. Changing collation on the same character set won't mess up data. – piotrm Jul 01 '15 at 17:12
  • @piotrm Do you want to make this the answer? I think it pretty much answers my question. It would be nice if you added **how** to drop the indexes and then re-add them pseudo-programmatically. – MirroredFate Jul 01 '15 at 22:50

1 Answers1

1

Don't do the "2-step ALTER" unless the CHARACTER SET was wrong but the data was 'right'. If you want to convert both the CHARACTER SET and the data, use ALTER TABLE something CONVERT TO CHARACTER SET ...;

See Case 5 in my blog, which, as you might guess, has other cases.

As for the error 1170 -- that had to do with some INDEX. Before I help you with that, decide whether it is relevant.

Rick James
  • 135,179
  • 13
  • 127
  • 222