7

I am using Django on Bluehost. I created a form for user generated input, but unicode inputs from this form fails to be stored or displayed of characters. So I did a SO and google search that I should change the Collate and Character set of my database. I run this sql

ALTER DATABASE learncon_pywithyou CHARACTER SET utf8 COLLATE utf8_unicode_ci;

from python27 manage.py dbshell, which initiated a mysql shell, what shows on screen is Query OK, 1 row affected (0.00 sec).

So I assume the problem is solved, but it is not actually. This sql has not done anything, as I later find it in phpMyAdmin provided by Bluehost. All the Varchar fields of all the tables are still in lantin1_swedish_ci collate.

So assume that alter table should work instead. I run this on mysql alter table mytable character set utf8 collate utf8_unicode_ci;

although on screen it shows Query OK. 4 rows affected, it actually did nothing either, the collate of those fields in mytable did not change at all.

So I finally manually change the fields in phpMyAdmin for mytable and this works, now I am able to insert in this table with unicode and also they display correctly, but I have around 20 tables of such, I don't want to change them one by one manually.

Do we at all have a simple and effective way of changing Collate of each field to store and display correct unicodes?

TonyTony
  • 1,344
  • 15
  • 23

2 Answers2

10

Changing collation at the database level sets the default for new objects - existing collations will not be changed.

Similarly, at a table level, only new columns (See comment at the bottom) are affected with this:

alter table mytable character set utf8 collate utf8_unicode_ci;

However, to convert the collation of existing columns, you need to add convert to:

alter table mytable convert to character set utf8 collate utf8_unicode_ci;
StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • 7
    Just dropping a note there: if nothing is converted in the table, the charset stays unchanged - you have to use the query without "convert to" in that case. – korkman Dec 26 '16 at 18:49
  • 1
    @korkman - This was the exact issue with our database we were trying to update. It's a bit silly that it won't update the collation if there is no conversion needed. – Origin Mar 27 '17 at 06:00
  • Thanks @korkman for information : we have the issue and we doesn't understand why! It's more than a bit silly in my opinion!! – rlm Sep 28 '18 at 08:32
4

In addition to @StuartLC , For Changing All 20 tables charset and collation use below query, Here world is database name

SELECT 
CONCAT("ALTER TABLE ",TABLE_SCHEMA , ".",TABLE_NAME," CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci") AS AlterSQL
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = "world";

The above will prepare all ALTER queries which you need to run.

Community
  • 1
  • 1
Abdul Manaf
  • 4,768
  • 3
  • 27
  • 34
  • Thanks for this experienced answer, now I believe those DBMS do have considered automation in design, even with not much keywords. This is what I want, it should save me a lot of time. And thanks for @StuartLC for the useful basis, "convert to". – TonyTony Jan 25 '14 at 19:14
  • 1
    Same note as below here: if nothing is converted in the table, the charset stays unchanged - you have to use the query without "convert to" in that case. MariaDB 10.0.27. – korkman Dec 26 '16 at 18:50