CODEWITHSUNDEEP
mysqlcollation
user158469
user158469

Reputation: 3033

How to change collation of database, table, column?

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?

Upvotes: 303

Views: 404461

Answers (25)

Zag
Zag

Reputation: 11

In PhpMyadmin I found that clicking "Change all column collations" wasn't working in some instances. Instead I had to change the table collation first to something different, then back to the one I wanted.

Make sure "Change all column collations" is clicked in both operations and it should work!

Upvotes: 0

Vaibhav Panmand
Vaibhav Panmand

Reputation: 397

A simple way to change charset and collation of all tables and columns. You can use below bash script

#!/bin/bash

# MySQL connection parameters

DB_USER="mysql_user"
DB_PASSWORD="password here"
DB_NAME="db name here"

# Get list of tables in the database
tables=$(mysql -u$DB_USER -p$DB_PASSWORD -D $DB_NAME -N -B -e "SHOW TABLES;")

# Iterate over each table
for table in $tables; do
    # Get list of columns in the table
    mysql -u$DB_USER -p$DB_PASSWORD -D $DB_NAME -e "ALTER TABLE $table CONVERT TO CHARACTER SET utf8mb4;"
    columns=$(mysql -u$DB_USER -p$DB_PASSWORD -D $DB_NAME-N -B -e "SHOW COLUMNS FROM $table;" | awk '{print $1}')

    # Iterate over each column
    for column in $columns; do
        # Alter the column collation
        mysql -u$DB_USER -p$DB_PASSWORD -D $DB_NAME -e "ALTER TABLE $table MODIFY $column VARCHAR(255) COLLATE utf8mb4_general_ci;"
        echo "Changed collation of column $column in table $table"
    done
done

echo "All columns collation changed successfully."

Upvotes: 0

Nabeel Ahmed
Nabeel Ahmed

Reputation: 19232

Changing for database:

ALTER DATABASE <database_name> CHARACTER SET utf8mb4 
    COLLATE utf8mb4_unicode_ci;

Note that it will only set a new default, that will be used for new tables created since, but wouldn't change for existing tables.

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;

Upvotes: 413

Sel
Sel

Reputation: 181

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 ', IF(INFORMATION_SCHEMA.COLUMNS.IS_NULLABLE = 'YES', 'DEFAULT NULL;', 'NOT NULL;')) 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 */

Upvotes: 18

Jonas Eberle
Jonas Eberle

Reputation: 2921

Here is a bash version.

#! /bin/bash
# Converts database, table and column collations.
# Only executes needed changes.
# Outputs SQL commands that are being run.
# Does not ask questions! Have a backup!
# @see https://stackoverflow.com/questions/1294117/how-to-change-collation-of-database-table-column

# saner programming env: these switches turn some bugs into errors
set -o errexit -o pipefail -o noclobber -o nounset

### settings:
# mysqlCmd: Use `mysql --host --user ...` as needed in your environment.
# Depending on what you want to do, you can use `ssh mysql`
# or `mysql` wrappers like `ddev mysql`, `docker-compose exec -T db mysql` etc., too.
mysqlCmd="mysql"
db=db
charset=utf8mb4
collation=utf8mb4_general_ci

### script:
cmds="$($mysqlCmd --skip-column-names <<<"
    -- DB
    SELECT
        -- some debug output: what has it been before?
        CONCAT (
            'SELECT \"Old: ',
            DEFAULT_CHARACTER_SET_NAME,
            ', ',
            DEFAULT_COLLATION_NAME,
            '\";'
        ),
        CONCAT (
            'ALTER DATABASE \`',
            SCHEMA_NAME,
            '\` CHARACTER SET $charset COLLATE $collation;\n'
        )
    FROM
        information_schema.SCHEMATA
    WHERE
            SCHEMA_NAME like '$db'
        AND (
                DEFAULT_CHARACTER_SET_NAME != '$charset'
            OR
                DEFAULT_COLLATION_NAME != '$collation'
        )
    ;

    -- tables
    SELECT
        -- some debug output: what has it been before?
        CONCAT (
            'SELECT \"Old: ',
            TABLE_COLLATION,
            '\";'
        ),
        CONCAT (
            'ALTER TABLE \`',
            TABLE_SCHEMA,
            '\`.\`',
            TABLE_NAME,
            '\` CONVERT TO CHARACTER SET $charset COLLATE $collation;\n'
        )
    FROM
        \`information_schema\`.TABLES
    WHERE
        TABLE_SCHEMA like '$db' AND
        TABLE_COLLATION IS NOT NULL AND
        TABLE_COLLATION NOT IN ('$collation')
    ;


    -- columns
    SELECT
        -- some debug output: what has it been before?
        CONCAT (
            'SELECT \"Old: ',
            CHARACTER_SET_NAME,
            ', ',
            COLLATION_NAME,
            '\";'
        ),
        CONCAT (
            'ALTER TABLE \`',
            TABLE_SCHEMA,
            '\`.\`',
            TABLE_NAME,
            '\` MODIFY \`',
            COLUMN_NAME,
            '\` ',
            COLUMN_TYPE,
            ' CHARACTER SET $charset COLLATE $collation;\n'
        )
    FROM
        \`information_schema\`.COLUMNS
    WHERE
        TABLE_SCHEMA like '$db' AND
        COLLATION_NAME IS NOT NULL AND
        COLLATION_NAME NOT IN ('$collation')
    ;
")"

# output SQL commands
#echo -e "$cmds"

# execute
$mysqlCmd -v --skip-column-names <<<"$cmds"

Tested with MariaDB 10.4 - 10.6, MySQL 5.7

Upvotes: 0

jales cardoso
jales cardoso

Reputation: 636

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

Upvotes: 0

user109764
user109764

Reputation: 654

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.

Upvotes: 0

Kim Michael
Kim Michael

Reputation: 53

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

?>}

Upvotes: 0

Dzintars
Dzintars

Reputation: 1571

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

Upvotes: 8

Max Ivanov
Max Ivanov

Reputation: 171

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

Upvotes: 8

Juddling
Juddling

Reputation: 4690

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

Upvotes: -1

Florian Kirmaier
Florian Kirmaier

Reputation: 572

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!

Upvotes: 3

Quassnoi
Quassnoi

Reputation: 425251

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.

Upvotes: 301

Andr&#225;s Ott&#243;
Andr&#225;s Ott&#243;

Reputation: 7695

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.

Upvotes: 4

Petr Stastny
Petr Stastny

Reputation: 173

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
)

Upvotes: 3

Mircea Stanciu
Mircea Stanciu

Reputation: 3753

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

 ALTER SCHEMA MYSCHEMA DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

Upvotes: 2

Adam Nofsinger
Adam Nofsinger

Reputation: 4174

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.

interconnect script buttons

Upvotes: 5

Chandra Kumar
Chandra Kumar

Reputation: 4205

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

Upvotes: 4

Parampal Pooni
Parampal Pooni

Reputation: 3108

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

Upvotes: 29

kickoff3pm
kickoff3pm

Reputation: 1

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.

Upvotes: 0

Yevgeniy Afanasyev
Yevgeniy Afanasyev

Reputation: 41300

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

Upvotes: 12

mtmehdi
mtmehdi

Reputation: 61

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

?>

Upvotes: 5

jeeva
jeeva

Reputation: 1603

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.

Upvotes: 52

hkasera
hkasera

Reputation: 2148

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

Upvotes: 69

andersonbd1
andersonbd1

Reputation: 5386

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

Upvotes: 9

Related Questions