1

I was facing some issues with the character's encoding. Those are resolved by updating the CHARACTER and COLLATE for some columns in the table. So my concern is if this conversion is safe? Or does this can create issues for different encoding techniques? Also how to convert the CHARACTER and COLLATE for multiple columns of the table in the same MySQL query?

Improve this question

1 Answer 1

Reset to default
0

There are many possible impacts. First, what command did you use to change the charset and collation?

The "correct" way is to

ALTER TABLE .. CONVERT TO ...

Any other change is likely to have messed up the data. To check for that, please provide SELECT col, HEX(col) FROM .. WHERE .. for some non-English cell in the table.

If you have "garbage" coming out of your app, see https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored for further clues.

Improve this answer
5
  • I have used ALTER TABLE tablename MODIFY columnname VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; to alter table
    – Mehar
    Jul 27, 2021 at 6:36
  • @Mehar - If you had accented letters in the table, then the data may be garbled. (But you may not see it.) Do that SELECT.
    – Rick James
    Jul 27, 2021 at 6:53
  • It return the same data as stored, like Jan Józef Ignacy Łukasiewicz
    – Mehar
    Jul 27, 2021 at 7:13
  • | Jan Józef Ignacy Łukasiewicz | 4A616E204AC3B37A65662049676E61637920C581756B617369657769637A | | części | 637AC499C59B6369 |
    – Mehar
    Jul 27, 2021 at 7:14
  • C3B3 is the correct utf8mb4 for o-acute and C581 for the L-stroke. One more check: LENGTH(col) will be 2 larger than CHAR_LENGTH(col) -- bytes versus characters.
    – Rick James
    Jul 27, 2021 at 20:03

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.