Web Designing, Web Development, Website Design, Website Development, SEO

0091 98233 72069

COGS: CUSTOMISED, OPTIMISED, GENERALISED WEB DESIGN & WEB DEVELOPMENT SOLUTIONS

how to change Collation of all tables in mysql

To change the default character set and collation of a table including those of existing columns (note the convert to clause):

alter table <some_table> convert to character set utf8 collate utf8_unicode_ci;


To apply this to all tables in database you have to execute alter command for each table. Easy way to prepare alter command is to use following sql command

SELECT CONCAT("ALTER TABLE `", TABLE_NAME,"` convert to character set utf8 collate utf8_unicode_ci;") AS    mySQL
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="database_name"
AND TABLE_TYPE="BASE TABLE"

Above sql command will generate alter query for all tables in database. You can copy those all and execute at once.