0

I came up with the weird situation with my database. My site is already in production and there are lots of data. I need to export CSV file for the client and give them CSV file. I am using my database export button to export my data in CSV file.

My Problem While exporting CSV file the polish characters are the unreadable character like ą,ć,ó,ś.Means these characters are changed to an unreadable character like "?". Are lots of data so is impossible to change in CSV file manually.

Mistake I mistakenly set my character set "latin1" and collation "latin1_swedish_ci".

Question Is it possible to get back this unreadable character into the readable format without losing these data?

I try to googled and try something like https://nicj.net/mysql-converting-an-incorrect-latin1-column-to-utf8/ but no luck.

Any solution? Thanks in advance.

Dev
  • 345
  • 1
  • 15
  • Possible duplicate of [UTF-8 all the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) – Martin Apr 05 '18 at 12:51
  • @Martin I want it to be fixed in my database eg phpmyadmin using the export link, not while showing to site. These data are used by many others which not belong to me. So I want my data should be readable while using export link in phpmyadmin. – Dev Apr 05 '18 at 13:12
  • erm.... Do you want to be told how to change your column collation from `latin1_` to `utf8mb4_` ? can you please **edit** your question and explain why the question linked above doesn't help you? – Martin Apr 05 '18 at 13:15
  • @Martin yes you are right but I want to make this unreadable data to readable also without losing my data. Wait I am editing my question as well. – Dev Apr 05 '18 at 13:20
  • @Martin I try to do the same but no luck. Yes, I am using PHPMyAdmin. – Dev Apr 05 '18 at 13:24
  • [UTF-8 All the way through](https://stackoverflow.com/questions/279170/utf-8-all-the-way-through) and [Migrating MySQL UTF8 to UTF8MB4](https://stackoverflow.com/questions/29194270/migrating-mysql-utf8-to-utf8mb4-problems-and-questions) will both answer your question. – Martin Apr 05 '18 at 14:00
  • Is helping for only new entry data but not to recover my old data. – Dev Apr 05 '18 at 14:32
  • Please *READ* the question and answers. My Answer below will fix your immediate issue with your core data; however you will still have issues if your PHP is **not in the correct encoding format** or if your HTML output is **not in the correct character set**. Reading the linked questions ***IN FULL*** will tell you this and will tell you how to fix this. – Martin Apr 05 '18 at 15:28

1 Answers1

0

Reading that the Queston asker wants to know how to update columns to the correct character set in PHPMyAdmin:

1) In PhpmyAdmin Select the table you want to update.
2) Select "Operations" from the top menu
3) Under "Table Options" you want to select the Collation you want to use,
4) And also tick the box stating

Change all column collations

5) Click Go.

All your table data will now be reformed into full UTF-8.

NOTE:
This means that each "char" in a "varchar" is now equal to 4 chars so it can cause some issues with using indexed column keys prior to MySQL 5.7 as indexes were limited to 767 bytes and UTF-8 characters are 4 bytes each -- this an indexed VARCHAR column of 255 characters would be 255 * 4 = 1020 and so exceed the index limit.

enter image description here

Martin
  • 22,212
  • 11
  • 70
  • 132
  • but my data are still the same. I want to get back my this unreadable character. Is it possible? – Dev Apr 05 '18 at 13:45
  • You need to better describe what *exactly* you're trying to do and why? Your messages so far are not seeming to make sense!! – Martin Apr 05 '18 at 13:46