ALTER DATABASE to change COLLATE not working

Question

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?

Ineducation

Answer 1

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.

Ulrike

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 @Serenity for the useful basis, "convert to". – Ineducation

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. – Disposition

Answer 2

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;

Serenity

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. – Disposition

@Disposition - 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. – Fed

Thanks @Disposition for information : we have the issue and we doesn't understand why! It's more than a bit silly in my opinion!! – Refresher