Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ALTER DATABASE to change COLLATE not working

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?

like image 835
TonyTony Avatar asked Jan 25 '14 17:01

TonyTony


People also ask

How do I change the collation of an existing database?

Set or change the database collation using SSMS Alternatively, if the database already exists, right-click the database that you want and select Properties. Select the Options page, and select a collation from the Collation drop-down list. After you are finished, select OK.

How do I transfer a database from one collation to another collation in SQL Server?

The collation of the user databases is not changed. To change the collation of an existing user database or to create a new database with the appropriate collation, use the ALTER DATABASE command, and then use DTS or the bcp utility to transfer the data to the new database.

Is SQL_Latin1_General_CP1_CI_AS deprecated?

These are definitely obsolete, even if not officially deprecated, and are mainly for pre-SQL Server 2000 compatibility. Although, quite unfortunately SQL_Latin1_General_CP1_CI_AS is very common due to it being the default when installing on an OS using US English as its language.


1 Answers

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;
like image 61
StuartLC Avatar answered Sep 28 '22 17:09

StuartLC