How to change collation of database, table, column?

Question

The database is latin1_general_ci now and I want to change collation to utf8mb4_general_ci.

Is there any setting in PhpMyAdmin to change collation of database, table, column? Rather than changing one by one?

See this for the answer: http://stackoverflow.com/questions/5906585/how-to-change-the-default-collation-of-a-database/9531221#9531221 — Timo Huovinen, Jul 20 '15 at 05:36

score 378 · Answer 1 · edited Jul 25 '23 at 12:43

378

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 utf8mb4 
    COLLATE utf8mb4_unicode_ci;

Changing it per table:

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_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 utf8mb4 
    COLLATE utf8mb4_unicode_ci;

edited Jul 25 '23 at 12:43

Guildenstern

2,179
1
17
39

answered Aug 17 '16 at 11:51

Nabeel Ahmed

18,328
4
58
63

25

This one actually answers the question directly. I needed this, as my table-level changes were NOT updating the columns. I'll investigate that later; but this is the information that got me through the hard times. Thanks. – Parapluie Feb 03 '17 at 19:13
10

The best answer for this. – jubi4dition Aug 03 '17 at 15:41
5

Yes, you need to specificities the column type. Here is a magic command to get all the types. Using multi-line editing you can generate the command to update all columns at once starting here: `SELECT table_schema , table_name , column_name , COLLATION_NAME , COLUMN_TYPE FROM information_schema.columns WHERE collation_name != 'utf8_general_ci' AND table_schema not in ('information_schema','mysql', 'performance_schema','sys');` – William Entriken Dec 12 '19 at 19:56
For a single column you can simply do: ALTER TABLE table_name CHANGE column_name VARCHAR(45) COLLATE utf8mb4_bin; – TomoMiha Apr 24 '20 at 14:34
Do you always need the data type (e.g. `varchar(255)`? What about `TEXT`? – testing Apr 13 '22 at 09:59
Does anybody know how can we detect / change collation of a user defined variable? – Daniil Apr 20 '22 at 12:18
i changed the collate (alter table table.name character set = utf8mb4, collate = utf8mb4_0900_ai_ci but when i say show full columns from table, it still shows the columns as latin-swedish – Kevin Crum May 02 '23 at 00:02

score 298 · Accepted Answer · edited Nov 23 '18 at 15:44

298

You need to either convert each table individually:

ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4

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

edited Nov 23 '18 at 15:44

terales

3,116
23
33

answered Aug 18 '09 at 14:14

Quassnoi

413,100
91
616
614

19

But i want to change collation of columns. This is will change only the table collation.. – user158469 Aug 18 '09 at 14:22
15

`@rsensan`: `CONVERT` will change the collation of columns as well. – Quassnoi Aug 18 '09 at 14:33
26

ALTER SCHEMA `database` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci ; – stormwild Jul 17 '15 at 10:10
9

@stormwild: this won't affect existing tables – Quassnoi Jul 17 '15 at 16:03
2

two step process to update them all: http://georgepavlides.info/convert-all-tables-in-a-mysql-database-to-utf8_general_ci/ – i_a Jul 23 '15 at 22:34
56

My query: ALTER TABLE `MYTABLE` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; [Please don't use utf8_general_ci anymore ;-)](http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci) – Kapitein Witbaard Sep 18 '15 at 09:54
3

`ALTER TABLE MYTABLE CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_520_ci` for a sort order that correctly handles most languages – Jeow Li Huan Apr 10 '17 at 09:37
If you use phpMyAdmin -> Select your DB on the left tree > Sheet "Operations" (on top) > "Collation" (on bottom) : select the intended Collation and check "Change all tables collations" + "Change all tables columns collations" and press the button "Go". Done; – Edgaras Nov 27 '20 at 16:00
Yeah, I only want to change a few columns so this doesn't help me – Jonathan Rys Aug 12 '21 at 16:57
@JonathanRys: down votes are for the incorrect answers. Is there any other problem with the answer, except that it does not help you? – Quassnoi Aug 12 '21 at 18:23
@Quassnoi The title of the question implies that the OP wants three answers. There is another answer down lower that I feel better answers the question. I feel that should be the accepted answer so I downvoted this one and upvoted that one. – Jonathan Rys Aug 12 '21 at 20:19
be aware that columns of large data amounts might not change though. – Nandostyle Sep 02 '21 at 20:26

hkasera · Answer 3 · 2012-10-04T11:22:56.033

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

score 50 · Answer 4 · answered Jan 16 '13 at 08:05

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.

score 26 · Answer 5 · answered Mar 14 '16 at 01:26

26

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

answered Mar 14 '16 at 01:26

Parampal Pooni

2,958
8
34
40

1

+1 I like this answer the most. Not everyone has PHP magically somewhere. Some of use use other languages with MySQL. This was easy to run in MySQL Workbench, copy the rows and paste. I just did the extra step to run the above for `information_schema.tables` and the code in concat `ALTER TABLE 'schema'.'table' CHARACTER SET = utf8mb4 , COLLATE = utf8mb4_bin ;` – Pierre Jan 31 '19 at 16:39
1

This bugs out on (medium/long)text column types, which have to be cleaned up manually – stiebrs May 11 '19 at 12:03
You can use SCHEMA() function, instead of 'you_db_name_goes_here' – Pierre-Olivier Vares Apr 02 '21 at 11:01
2

And you're losing NULLable and DEFAULT options. – Pierre-Olivier Vares Apr 02 '21 at 11:21

score 18 · Answer 6 · edited Mar 03 '21 at 09:38

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 */

Worked for me. You can use `SET foreign_key_checks = 0;` if this is causing constraint errors, just turn it back on with `SET foreign_key_checks = 1;` after the database updates. — Michael Fulton, Jun 15 '21 at 18:13
Bear in mind that the "each column" script does not consider the "not null" constraint and the generated lines will change previously non null columns to nullable. — digitalbreed, Apr 29 '23 at 00:38

Yevgeniy Afanasyev · Answer 7 · 2015-09-04T04:44:59.280

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

score 9 · Answer 8 · answered Aug 18 '09 at 14:15

9

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

answered Aug 18 '09 at 14:15

andersonbd1

5,266
14
44
62

Dzintars · Answer 9 · 2020-01-31T16:28:55.700

8

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

edited Jan 31 '20 at 16:28

answered Oct 23 '17 at 15:13

Dzintars

1,410
21
29

score 7 · Answer 10 · answered Nov 04 '19 at 13:37

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

Changing only table collation is not applied for columns. Only this scripts help with my case. — Oleg Bondarenko, Feb 26 '21 at 09:26

score 5 · Answer 11 · answered Feb 02 '15 at 05:36

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

?>

This worked for me perfectly, after upgrading to Zabbix 5. Just to say that I changed the **Charset** and **Collation** like this: `CONVERT TO CHARACTER SET utf8 COLLATE utf8_bin`. — robe007, May 20 '20 at 23:58

score 4 · Answer 12 · edited Nov 22 '18 at 14:07

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.

This saved my Friday. Bookmarked for future reference – Simonluca Landi Oct 28 '22 at 08:26 — Simonluca Landi, Oct 28 '22 at 08:26

score 4 · Answer 13 · answered May 05 '16 at 11:41

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

score 4 · Answer 14 · answered Oct 19 '16 at 20:10

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.

score 3 · Answer 15 · answered Nov 27 '18 at 23:22

3

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!

answered Nov 27 '18 at 23:22

Florian Kirmaier

424
5
8

This is certainly the best answer! I just wanted to paste it by myself but it's here already! Note, it seems like you don't really need to add "COLLATE utf8mb4_unicode_ci;" part at then like in some of the answers it seems to be extra! – user109764 Feb 11 '21 at 11:50

score 2 · Answer 16 · answered Jul 31 '17 at 03:44

2

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

 ALTER SCHEMA MYSCHEMA DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

answered Jul 31 '17 at 03:44

Mircea Stanciu

3,675
3
34
37

score 2 · Answer 17 · answered Oct 11 '17 at 10:52

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
)

score 0 · Answer 18 · answered Nov 09 '15 at 14:25

0

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.

answered Nov 09 '15 at 14:25

kickoff3pm

1

Kim Michael · Answer 19 · 2020-05-01T21:55:21.903

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

?>}

score 0 · Answer 20 · answered Feb 11 '21 at 11:57

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.

score 0 · Answer 21 · edited Jan 10 '23 at 05:19

I had to change the collates of all databases, tables and columns in a cluster with many bases.

I used a script running on php 8.1 and mysql 8.0

function changeCollate() {
    $databases = $this->fetchQueryToArray("SHOW DATABASES LIKE 'nova_%'")->rows;
    foreach ($databases as $value) {
        $db = $value['Database (nova_%)'];
        $this->LOG("-- banco de dados --- " . $db);
        $this->exeQuery("ALTER DATABASE `$db` COLLATE utf8mb4_0900_ai_ci;");
        $this->exeQuery("use $db");
        $tables = $this->fetchQueryToArray("SHOW tables")->rows;
        foreach ($tables as $table) {
            $tb_name = $table["Tables_in_$db"];
            $this->exeQuery("ALTER TABLE `$tb_name` COLLATE utf8mb4_0900_ai_ci;");
            $QUERY = "ALTER TABLE `$db`.`$tb_name`\n";
            $columns = $this->fetchQueryToArray("SHOW FULL COLUMNS FROM $tb_name WHERE Type LIKE 'varchar%' OR Type = 'text' OR Type like 'enum%' OR Type = 'longtext' OR Type = 'mediumtext'")->rows;
            foreach ($columns as $column) {
                $QUERY .= "CHANGE `{$column['Field']}` `{$column['Field']}` {$column['Type']} COLLATE 'utf8mb4_0900_ai_ci'";
                $QUERY .= ($column['Null'] == 'YES') ?  " NULL" : " NOT NULL";
                if ($column['Default']) $QUERY .= " DEFAULT '{$column['Default']}'";
                if ($column['Comment']) $QUERY .= " COMMENT '{$column['Comment']}'";
                $QUERY .= ",\n";
            }
            if ($QUERY == "ALTER TABLE `$db`.`$tb_name`\n") continue;
            $QUERY = substr($QUERY, 0, -2) . ";\n\n";
            $this->exeQuery($QUERY);
        }
    }
}

score -1 · Answer 22 · answered Apr 19 '19 at 02:34

-1

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

answered Apr 19 '19 at 02:34

Juddling

4,594
8
34
40

How to change collation of database, table, column?

22 Answers22

Linked