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

User · Answer

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

User · Answer

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

User · Answer

if you want to update the default charset on a schema    ALTER SCHEMA MYSCHEMA DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8 unicode ci

User · Answer

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

User · Answer

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 XD  Hope some of you find it useful  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

User · Answer

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

User · Answer

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

User · Answer

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   gt   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

User · Answer

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

User · Answer

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

User · Answer

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  quot                                                    quot   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   quot     quot   You will also get this question marks if you will convert data before converting fields tables

User · Answer

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

User · Answer

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  lt database gt    lt charset gt   lt collation gt     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

User · Answer

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  lt database name gt  CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci    Changing it per table   ALTER TABLE  lt table name gt  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  lt table name gt  MODIFY  lt column name gt  VARCHAR 255  CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci

User · Answer

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    lt  php  conn1 new MySQLi  localhost   user   password   database    if  conn1- gt connect errno       echo mysqli connect error        exit     res  conn1- gt query  show tables   or die  conn1- gt error   while  tables  res- gt fetch array          conn1- gt query  ALTER TABLE  tables 0  CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4 unicode ci   or die  conn1- gt error     echo  The collation of your database has been successfully changed      res- gt free     conn1- gt close       gt

User · Answer

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

User · Answer

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   lt your-database gt   AND TABLE TYPE  BASE TABLE     By using CONVERT TO  this generates a scripts  which converts all the Tables of  lt your-database gt  to your requested encoding  This also changes the encoding of every column

User · Answer

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

User · Answer

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

User · Answer

You can run a php script                   lt  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   gt   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                      gt

User · Answer

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    lt  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   gt   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       gt

[mysql] How to change collation of database, table, column?

Examples related to mysql

Examples related to collation