Changing database encoding from latin1 to UTF8

Now a days, UTF-8 is the most used data encoding format, and the fact that your database is not using UTF8 encoding is really annoying, specially additionally when it comes to integrating different systems, that has no one unified encoding format.
So if you think it’s time to change your data encoding to utf8 format, then here what this post is all about.
I’ll list here the steps to do so, i just have to clarify that the main data encoding here is windows-1256 (which is the main Arabic encoding used in web applications), but it’s saved in latin1 encoding in the database (mydata ->windows-1256 -> latin1) ,also note that i’m using Mysql database.
Here are the steps:

  1. Export (only) the schema of the db,without “set Names” phrase in the outputted sql file, this will bring you back the data in the original encoding (windows-1256)
  2. Export the data of the db without “set Names” phrase in the outputted sql file, this will bring you back the data in the original encoding (windows-1256):
  3. Change the encoding of both files from arabic to utf8 -check the notes if you r using windows
  4. Open the file ‘db_name_schema.sql’ with any editor and replace each “DEFAULT CHARSET=latin1” phrase with “DEFAULT CHARSET=utf8” one
  5. Make a new db ,encoded in utf8:
  6. Import the schema and data in utf8 encoding:

notes

  • If you are wondering why to separate schema from data upon exporting , then the answer is that the operation of replacing “DEFAULT CHARSET=latin1” phrase with “DEFAULT CHARSET=utf8” one , is taking place only on schema files, so it’s recommended to separate them so that you dont stuck when loading the big data files.
  • If you are a windows user and can’t use iconv , then u can use any editor to do the job for u, try scite or note++ or even dreamweaver

enjoy!!!

23 Replies to “Changing database encoding from latin1 to UTF8”

  1. This is actually the only reliable solution that works 🙂
    Just substitute your own encodings to fit and there you go.

    Thanks dude, i’ve been fighting this problem for 3 full workdays.

  2. Generally I don’t read article on blogs, but I would like to say that this write-up very compelled me to try and do it! Your writing taste has been surprised me. Thanks, quite great article.

  3. when i move a database (with mysqldump ) from windows to Linux, it doesn’t recognize encoding. I export with UTF8, but on linux mysql says its Latin1.
    my dump code:
    mysqldump.exe --defaults-extra-file="c:\users\verwijs\appdata\local\temp\tmp_cg1mx.cnf" --complete-insert=TRUE --order-by-primary=TRUE --disable-keys=FALSE --allow-keywords=TRUE --default-character-set=utf8 --max_allowed_packet=1G --host=localhost --user=root --lock-tables=FALSE --add-locks=FALSE --port=3306 --single-transaction=TRUE --routines --events "osgrid" "estate_users" "estate_groups" "terrain" "migrations" "regionwindlight" "landaccesslist" "regionban" "land" "primitems" "estate_map" "regionsettings" "estate_settings" "regions" "estateban" "prims" "estate_managers" "primshapes"

    20:17:04 Export of C:\Users\Verwijs\Downloads\OSGRID_INI_FILES_MetalFan\Backups\osgrid-2.sql has finished

  4. I as well as my buddies happened to be taking note of the nice pointers from your site and so all of a sudden I had a terrible suspicion I never thanked the web site owner for those strategies. The men appeared to be as a consequence joyful to learn them and have pretty much been enjoying these things. I appreciate you for truly being simply considerate and then for picking this form of magnificent things most people are really desirous to know about. My very own honest apologies for not saying thanks to you earlier.

  5. Thanks for sharing all these wonderful blogposts. In addition, the optimal travel along with medical insurance plan can often relieve those concerns that come with visiting abroad. Some sort of medical crisis can shortly become very costly and that’s certain to quickly impose a financial load on the family’s finances. Having in place the suitable travel insurance package prior to setting off is worth the time and effort. Thank you

  6. i really need to know where to put that code, is it in mysql command ??
    i did that but i have errors when run the code, if any body can help me?

Leave a Reply

Your email address will not be published. Required fields are marked *