Ytukyg

I am contributing here, as the OP asked:

How to change collation of database, table, column?

The selected answer just states it on table level.

Changing it database wide:

ALTER DATABASE <database_name> CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Changing it per table:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Good practice is to change it at table level as it'll change it for columns as well. Changing for specific column is for any specific case.

Changing collation for a specific column:

ALTER TABLE <table_name> MODIFY <column_name> VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci;

You can run a php script.

               <?php

                   $con = mysql_connect('localhost','user','password');

                   if(!$con) { echo "Cannot connect to the database ";die();}

                   mysql_select_db('dbname');

                   $result=mysql_query('show tables');

                   while($tables = mysql_fetch_array($result)) {

                            foreach ($tables as $key => $value) {

                             mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");

                       }}

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

                ?>

To change collation for tables individually you can use,

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8

To set default collation for the whole database,

ALTER DATABASE  `databasename` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin

or else,

Goto PhpMyAdmin->Operations->Collation.

There you an find the select box which contains all the exsiting collations. So that here you can change your collation. So here after database table will follows this collation while you are creating new column . No need of select collation while creating new columns.

you can set default collation at several levels:

http://dev.mysql.com/doc/refman/5.0/en/charset-syntax.html

1) client
2) server default
3) database default
4) table default
5) column

If you run phpMyAdmin >> select database >> select table >> go to "Operations" tab >> in "Table options" section >> you can pick Collation from the drop down list >> and once you press {Go} at the top of the screen you will see a message:

Your SQL query has been executed successfully

and a script

ALTER TABLE `tableName` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci 

But it will NOT change the collations of existing columns.
To do so you can use this script (this one also came from phpMyAdmin)

ALTER TABLE  `tableName` CHANGE  `Name`  `Name` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL

The following query will generate ALTER queries that change the collation for all appropriate columns in all tables to a certain type (utf8_general_ci in my example below).

SELECT concat

        (

            'ALTER TABLE ', 

                t1.TABLE_SCHEMA, 

                '.', 

                t1.table_name, 

                ' MODIFY ', 

                t1.column_name, 

                ' ', 

                t1.data_type, 

                '(' , 

                    CHARACTER_MAXIMUM_LENGTH, 

                ')', 

                ' CHARACTER SET utf8 COLLATE utf8_general_ci;'

        )

from 

    information_schema.columns t1

where 

    t1.TABLE_SCHEMA like 'you_db_name_goes_here' AND

    t1.COLLATION_NAME IS NOT NULL AND

    t1.COLLATION_NAME NOT IN ('utf8_general_ci');

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/

You can change the CHARSET and COLLATION of all your tables through PHP script as follows. I like the answer of hkasera but the problem with it is that the query runs twice on each table. This code is almost the same except using MySqli instead of mysql and prevention of double querying. If I could vote up, I would have voted hkasera's answer up.

<?php

$conn1=new MySQLi("localhost","user","password","database");

if($conn1->connect_errno){

    echo mysqli_connect_error();

    exit;

}

$res=$conn1->query("show tables") or die($conn1->error);

while($tables=$res->fetch_array()){

    $conn1->query("ALTER TABLE $tables[0] CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci") or die($conn1->error);

}

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



$res->free();

$conn1->close();



?>

You can simple add this code to script file

//Database Connection

$host = 'localhost';

$db_name = 'your_database_name';

$db_user =  'your_database_user_name';

$db_pass = 'your_database_user_password';



$con = mysql_connect($host,$db_user,$db_pass);



if(!$con) { echo "Cannot connect to the database ";die();}



  mysql_select_db($db_name);



  $result=mysql_query('show tables');



  while($tables = mysql_fetch_array($result)) {

    foreach ($tables as $key => $value) {

    mysql_query("ALTER TABLE $value CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");

  }

}



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

I read it here, that you need to convert each table manually, it is not true. Here is a solution how to do it with a stored procedure:

DELIMITER $$



DROP PROCEDURE IF EXISTS changeCollation$$



-- character_set parameter could be 'utf8'

-- or 'latin1' or any other valid character set

CREATE PROCEDURE changeCollation(IN character_set VARCHAR(255))

BEGIN

DECLARE v_finished INTEGER DEFAULT 0;

DECLARE v_table_name varchar(255) DEFAULT "";

DECLARE v_message varchar(4000) DEFAULT "No records";



-- This will create a cursor that selects each table,

-- where the character set is not the one

-- that is defined in the parameter



DECLARE alter_cursor CURSOR FOR SELECT DISTINCT TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE()

AND COLLATION_NAME NOT LIKE CONCAT(character_set, '_%');



-- This handler will set the value v_finished to 1

-- if there are no more rows



DECLARE CONTINUE HANDLER

FOR NOT FOUND SET v_finished = 1;



OPEN alter_cursor;



-- Start a loop to fetch each rows from the cursor

get_table: LOOP



-- Fetch the table names one by one

FETCH alter_cursor INTO v_table_name;



-- If there is no more record, then we have to skip

-- the commands inside the loop

IF v_finished = 1 THEN

LEAVE get_table;

END IF;



IF v_table_name != '' THEN



IF v_message = 'No records' THEN

SET v_message = '';

END IF;



-- This technic makes the trick, it prepares a statement

-- that is based on the v_table_name parameter and it means

-- that this one is different by each iteration inside the loop



SET @s = CONCAT('ALTER TABLE ',v_table_name,

' CONVERT TO CHARACTER SET ', character_set);

PREPARE stmt FROM @s;

EXECUTE stmt;

DEALLOCATE PREPARE stmt;



SET v_message = CONCAT('The table ', v_table_name ,

' was changed to the default collation of ', character_set,

'.n', v_message);



SET v_table_name = '';



END IF;

-- Close the loop and the cursor

END LOOP get_table;

CLOSE alter_cursor;



-- Returns information about the altered tables or 'No records'

SELECT v_message;



END $$



DELIMITER ;

After the procedure is created call it simply:

CALL changeCollation('utf8');

For more details read this blog.

if you want to update the default charset on a schema:

 ALTER SCHEMA MYSCHEMA DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

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

)

Just run this SQL. Change your COLLATION to what you need and databaseName.

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

Quick way - export to SQL file, use search and replace to change the text you need to change. Create new database, import the data and then rename the old database and the new one to the old name.

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!