0

I am a developer so was hoping someone with DBA experience could help me out here.

I am using a MySQL database (MYSQL 5.6).

I came across a situation where a particular column stores Ids generated by any external system (to be provided by user input) and hence requires case sensitivity (abcd is a different id from ABCD). It seemed pretty natural to me to change the collation for that column to latin1_bin from latin1_swedish_ci where my database and tables all use latin1_swedish_ci collation.

I use the following statement to change the collation:

ALTER TABLE T MODIFY C VARCHAR(200) BINARY NOT NULL;

Are there any performance implications of doing this change? My application does not use COLLATE specifically in any query.

Also, column C is part of an index and a unique constraint with 2 other columns say A, B in table T (A,B,C). A and B are also foreign keys in table T.

Do I need to drop the index and unique constraints and re-create them?

Also, how do I rollback this change? Will this statement do the job:

ALTER TABLE T MODIFY C VARCHAR(200) NOT NULL;

The table T looks like :

|id|A|B|C|created_at|

id is auto increment.

Please help.

0

Not significant performance difference.

It will require rebuilding any indexes involving that column. That will take time, but it is a onetime issue.

Any *_ci collation is slightly more complex than a *_bin collation, at least when doing comparisons or sorting.

If you choose to undo the change, be explicit about the collation. Your proposed ALTER hopes that the 'default' will be used.

An ALTER .. COLUMN will rebuild the necessary indexes.

| improve this answer | |
  • Doesn't mysql regenerate the indexes involving the column? On changing the collation of the column, I observed change in cardinality of the indexes that include this column. I am assuming mysql is handling it by itself. – Kushan Sen Dec 14 '19 at 5:05
  • Is that what you meant by: An ALTER .. COLUMN will rebuild the necessary indexes. ? – Kushan Sen Dec 14 '19 at 5:33
  • @KushanSen - Yes. (And "cardinality" is not relevant.) – Rick James Dec 14 '19 at 21:37
  • Thanks. Also while changing collation or undoing the change there will be no data loss right as both collations belong to the same character set? – Kushan Sen Dec 17 '19 at 4:48
  • @KushanSen - The encoding is entirely controlled by the charaset; only the rules of comparing and sorting are controlled by the collation. One charset can have many collations, but not vice versa. So, no data loss when changing only the collation. (But, since an index is ordered according to the collation, it needs rebuilding.) – Rick James Dec 17 '19 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.