Mysql: How to change collation of database, table, column?

Now the database is latin1_general_ci and I want to change collation to 

utf8_general_ci. Is there any setting in PhpMyAdmin to change collation of database,

 table, column? Rather than changing one by one?

 Answers

You need to either convert each table individually:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8 

(this will convert the columns just as well), or export the database with latin1 and import it

 back with utf8.

---

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');

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

$result=mysql_query('show tables');

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

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

?>

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

---

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

---

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 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 post.

---

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

from one domain to another.

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

---

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