CODEWITHSUNDEEP
mysqlsqldatabasereplacephpmyadmin
Demilio
Demilio

Reputation: 693

How to change all fields in database from latin1_swedish_ci to utf8_general_ci?

The title saiz it all, right? :)

Details: I'm looking for a SQL query I can use in phpmyadmin. Need to Change all my fields in one database from latin1_swedish_ci to utf8_general_ci

Upvotes: 4

Views: 4557

Answers (2)

Subharanjan
Subharanjan

Reputation: 668

Change the default collation to utf8_general_ci in MySql:

Open the my.ini file. (C:\xampp\mysql\bin\my.ini)
Find the text [mysqld] and add the below lines.

[mysqld]

character-set-server = utf8

collation-server = utf8_general_ci

The above two lines will select a character set and collation at server startup. These settings apply server-wide and apply as the defaults for databases created by any application, and for tables created in those databases.

Upvotes: 2

René Höhle
René Höhle

Reputation: 27295

The main problem in your case is when you change the collation/character set from the table the data inside the table are latin1 and the table is utf8 until then.

What you can try is to make a dump of the table with the old character set.

mysqldump -uuser -p --default-character-set=latin1 dbname > dump.sql

and then import the database with the new character set like this:

mysql -uuser -p --default-character-set=utf8 dbname_test < dump.sql

To test i would import the dump to a test database. If the characters are not correct after the import. Use an Editor like pspad or notepad++ and change the file encoding to UTF-8. After this you can try to import another time.

The most times encodings are very annoying but i hope you can solve it.

When you only have phpmyadmin use the "Export" function and make the same with phpmyadmin. Export in the actual encoding and try to import in the new encoding but then you have to change the file encoding i think.

Upvotes: 2

Related Questions