CODEWITHSUNDEEP
mysqlcollation
Siamak Ferdos
Siamak Ferdos

Reputation: 3299

change all existing collation of columns

I have a table which some of its columns has collation of utf8_bin. But I need to change them to utf8_unicode_ci. I know this query for do it:

ALTER TABLE  `shn_sho_cities` CHANGE  `city_name`  `city_name` VARCHAR( 100 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL ;

but as I want change all columns which have utf8_bin instead of writing a query for each column. Is there any way which I could do this?

Upvotes: 0

Views: 3429

Answers (1)

Mohamed Mo Kawsara
Mohamed Mo Kawsara

Reputation: 4688

The following code will generate the required queries to change your collations, to utf8mb4 with utf8mb4_unicode_ci

SELECT CONCAT('ALTER TABLE `', TABLE_NAME,'` CONVERT TO CHARACTER SET 
utf8mb4 COLLATE utf8mb4_unicode_ci;') AS    mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA= "YOU-DB-NAME"
AND TABLE_TYPE="BASE TABLE"

Upvotes: 4

Related Questions