ALTER DATABASE change COLLATE not working

Question

I am using Django on Bluehost. I created a form for user input, but the Unicode inputs from this form cannot be saved or displayed in characters. So I did an SO and google search to change the Collate and Character set of my database. I ran this sql

ALTER DATABASE learncon_pywithyou CHARACTER SET utf8 COLLATE utf8_unicode_ci;

from python27 manage.py dbshellwhich initiated the mysql shell, which is shown on the screen Query OK, 1 row affected (0.00 sec).

So, I assume that the problem is solved, but in fact it is not. This sql did nothing, as I later found it in phpMyAdmin provided by Bluehost. All Varchar fields of all tables are still in lantin1_swedish_cicollate.

Suppose it alter tableshould work. I run this on mysql alter table mytable character set utf8 collate utf8_unicode_ci;

although he shows on the screen Query OK. 4 rows affected, he also did nothing, the comparison of these fields in has mytablenot changed at all.

So, I finally manually change the fields in phpMyAdmin for mytable, and it works, now I can insert this table in unicode and also display them correctly, but I have about 20 such tables, I don’t want to change them one by one manually.

Do we generally have a simple and effective way to change the sorting of each field to store and display the correct unicode?

Answer 1

In addition to @StuartLC , the following query is used to modify all 20 encoding and mapping tables. Here, the world is the name of the database.

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";

ALTER, .

Answer 2

- .

, (_. ).

alter table mytable character set utf8 collate utf8_unicode_ci;

convert to:

alter table mytable convert to character set utf8 collate utf8_unicode_ci;