How to change collation of database, table, column?

Just run this SQL to convert all database tables at once. Change your COLLATION and databaseName to what you need.

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," COLLATE utf8_general_ci;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="databaseName"
AND TABLE_TYPE="BASE TABLE";

Generates query to update each table and column of each table. I have used this to some of my projects before and was able to solved most of my COLLATION problems. (especially on JOINS)

To use, just export results to delimited text (probably new line '\n')

EACH TABLE

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, 
              '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;') 
       AS 'USE `DATABASE_NAME`;' 
FROM   INFORMATION_SCHEMA.TABLES 
WHERE  TABLE_SCHEMA = 'DATABASE_NAME' 
       AND TABLE_TYPE LIKE 'BASE TABLE' 

EACH COLUMN

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME,'` ', 
              DATA_TYPE, IF(CHARACTER_MAXIMUM_LENGTH IS NULL 
       OR DATA_TYPE LIKE 'longtext', '', CONCAT('(', CHARACTER_MAXIMUM_LENGTH, 
                                         ')') 
       ), ' COLLATE utf8mb4_unicode_ci;') AS 'USE `DATABASE_NAME`;' 
FROM   INFORMATION_SCHEMA.COLUMNS 
WHERE  TABLE_SCHEMA = 'DATABASE_NAME' 
       AND (SELECT INFORMATION_SCHEMA.TABLES.TABLE_TYPE 
            FROM   INFORMATION_SCHEMA.TABLES 
            WHERE  INFORMATION_SCHEMA.TABLES.TABLE_SCHEMA = 
                   INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA 
                   AND INFORMATION_SCHEMA.TABLES.TABLE_NAME = 
                       INFORMATION_SCHEMA.COLUMNS.TABLE_NAME 
            LIMIT  1) LIKE 'BASE TABLE' 
       AND DATA_TYPE IN ( 'char', 'varchar' ) /* include other types if necessary */

I was surprised to learn, and so I had to come back here and report, that the excellent and well maintained Interconnect/it SAFE SEARCH AND REPLACE ON DATABASE script has some options for converting tables to utf8 / unicode, and even to convert to innodb. It's a script commonly used to migrate a database driven website (Wordpress, Drupal, Joomla, etc) from one domain to another.

• https://github.com/interconnectit/Search-Replace-DB
• https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

Better variant to generate SQL script by SQL request. It will not ruin defaults/nulls.

SELECT concat
    (
        'ALTER TABLE ', 
            t1.TABLE_SCHEMA, 
            '.', 
            t1.table_name, 
            ' MODIFY ', 
            t1.column_name, 
            ' ', 
            t1.column_type,
            ' CHARACTER SET utf8 COLLATE utf8_general_ci',
            if(t1.is_nullable='YES', ' NULL', ' NOT NULL'),
            if(t1.column_default is not null, concat(' DEFAULT \'', t1.column_default, '\''), ''),
            ';'
    )
from 
    information_schema.columns t1
where 
    t1.TABLE_SCHEMA like 'your_table_here' AND
    t1.COLLATION_NAME IS NOT NULL AND
    t1.COLLATION_NAME NOT IN ('utf8_general_ci');

My solution is a combination of @Dzintars and @Quassnoi Answer.

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, '.', TABLE_NAME," CONVERT TO CHARACTER SET utf8mb4 ;") AS    ExecuteTheString
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA="<your-database>"
AND TABLE_TYPE="BASE TABLE";

By using CONVERT TO, this generates a scripts, which converts all the Tables of <your-database> to your requested encoding. This also changes the encoding of every column!

I used the following shell script. It takes database name as a parameter and converts all tables to another charset and collation (given by another parameters or default value defined in the script).

#!/bin/bash

# mycollate.sh <database> [<charset> <collation>]
# changes MySQL/MariaDB charset and collation for one database - all tables and
# all columns in all tables

DB="$1"
CHARSET="$2"
COLL="$3"

[ -n "$DB" ] || exit 1
[ -n "$CHARSET" ] || CHARSET="utf8mb4"
[ -n "$COLL" ] || COLL="utf8mb4_general_ci"

echo $DB
echo "ALTER DATABASE $DB CHARACTER SET $CHARSET COLLATE $COLL;" | mysql

echo "USE $DB; SHOW TABLES;" | mysql -s | (
    while read TABLE; do
        echo $DB.$TABLE
        echo "ALTER TABLE $TABLE CONVERT TO CHARACTER SET $CHARSET COLLATE $COLL;" | mysql $DB
    done
)

To change the collation of all fields in all tables of a database at once:

I was just adding another loop for the fields within the tables to the solution via Php before mentioned. This has helped, all fields in the tables are also converted.

<?php
$con = mysql_connect('localhost','user','pw');
if(!$con) { echo "Cannot connect to the database ";die();}
mysql_select_db('database_name');
$result=mysql_query('show tables');
while($tables = mysql_fetch_array($result)) {

foreach ($tables as $key => $table) {                   // for each table

    $sql = "ALTER TABLE $table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci";
    echo "\n".$sql;
    mysql_query($sql);

    $sql = "show fields in ".$table." where type like 'varchar%' or type like 'char%' or type='text' or type='mediumtext';";
    $rs2=mysql_query($sql);
    while( $rw2 = mysql_fetch_array($rs2) ){            // for each field in table

        $sql = "ALTER TABLE `".$table."` CHANGE `".$rw2['Field']."` `".$rw2['Field']."` ".$rw2['Type']." CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL;";
        echo "\n".$sql;
        mysql_query($sql);

    } 


}
}
echo "The collation of your database has been successfully changed!";

?>}

Note, after changing the charset for database/table/column, you might need to actually convert the existing data (if you see, for example, something like "مطلوب توريد جÙ") with something like this:

update country set name = convert(cast(convert(name using latin1) as binary) using utf8), cn_flag = convert(cast(convert(cn_flag using latin1) as binary) using utf8), and so on..

While for converting database, tables and fields, I would suggest this answer from this thread which would generate a big set of queries that you will just copy at paste, here I couldn't find an automatic solution yet. Also be warned, if you will convert the same field twice you will get unrecoverable question marks: "???". You will also get this question marks if you will convert data before converting fields/tables.

I've just written a bash script to find all tables in a given database and covert them (and its columns).

Script is available here: https://github.com/Juddling/mysql-charset