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:
- 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)
mysqldump --default-character-set=latin1 --skip-set-charset -d -uusername -ppassword db_name > db_name_schema.sql
- 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):
mysqldump --default-character-set=latin1 --skip-set-charset -t -uusername -ppassword db_name > db_name_data.sql
- Change the encoding of both files from arabic to utf8 -check the notes if you r using windows
iconv -f windows-1256 -t utf8 db_name_schema.sql > db_name_schema_utf8.sql iconv -f windows-1256 -t utf8 db_name_data.sql > db_name_data_utf8.sql
- Open the file ‘db_name_schema.sql’ with any editor and replace each “DEFAULT CHARSET=latin1″ phrase with “DEFAULT CHARSET=utf8″ one
- Make a new db ,encoded in utf8:
mysql -uusername -ppassword -e 'CREATE DATABASE new_db DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci'
- Import the schema and data in utf8 encoding:
mysql --default-character-set=utf8 -uusername -ppassword new_db < db_name_schema_utf8.sql mysql --default-character-set=utf8 -uusername -ppassword new_db < db_name_data_utf8.sql
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!!!
nice work. useful. thx.
Hello Sir:
Thanks for posting these useful infirmation. Keep them coming
Pingback: Fixing mixed-encoding MySQL dumpfiles with WordPress
Thanks for the tip ! Will try it with latin1
WOW…
great article, i will try to do later today.
thank khalid.
Adel M
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.
Worked flawlessly. Thanks!
Thanks, this saved me from a problem I had been stuck in 1256 for the past 2 years.
Perfect instructions.
and just why can’t you simply use mysql and write alter database character set utf8?
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.
It’s really a nice and useful piece of information. I am satisfied that you simply shared this useful information with us. Please stay us informed like this. Thank you for sharing.
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
PS: tips are welcome
UPDATE:
found the answer..!! convert with command
iconv -f iso-8859-15 -t utf8 database_latin1.sql > database_utf8.sqlAndré
Pingback: The merely of all the world part and study - Page 28 - All Cars India Forums
hello
where i will put this codes in SQL or in query or where?
thank u
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.
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