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?
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.