How to change collation of database, table, column?

    |
  • Added:
  • |
  • In: Basic PHP

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?

This Question Has 12 Answeres | Orginal Question | user158469

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

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.

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 

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.

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

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

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

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.

interconnect script buttons


Search
I am...

Sajjad Hossain

I have five years of experience in web development sector. I love to do amazing projects and share my knowledge with all.

Connect Social With PHPAns
Top