Change the "collation" and "Character set" of the database, tables and corresponding columns

Question

Some older projects were designed for their immediate purpose and based on this, the databases, tables and columns were prepared in a way limited to the intended scenario.

In order to update and adapt these projects for greater scalability, we have been updating the databases for a collation and character set more widely, as is the case with UFT-8.

So far, database queries have been conducted to deal with each scenario:

Change the table and its fields:

ALTER TABLE minhaTabela CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

^{It needs to be executed for each existing table, being here where lapses occur and the whole process becomes extremely time consuming.}

Change the database:

ALTER DATABASE minhaBD CHARACTER SET utf8 COLLATE utf8_general_ci;

This process is mainly manual, requires use of tools that may or may not be known to the person assigned to perform it and is mainly time consuming and subject to lapses.

Question

How to amend the collation and character set of the database, its tables and respective fields at once?

Answer 1

I believe there is not a native command that does everything, it is possible to create a query that does the work in its place.

Tables

Generating the necessary queries for each table:

SELECT CONCAT(
    "ALTER TABLE ", 
    TABLE_NAME,
    " CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;")
  FROM INFORMATION_SCHEMA.TABLES T
 WHERE TABLE_TYPE="BASE TABLE"
   AND TABLE_SCHEMA like '<schema>' 
   AND DATA_TYPE in ('varchar', 'text')

Now just copy and paste. Note the filter by DATA_TYPE to select only fields that have a collaction.

Sqlfiddle

Databases

In the case of databases, I arrived at the following command:

SELECT CONCAT(
    "ALTER DATABASE ", 
    schema_name,
    " CHARACTER SET utf8 COLLATE utf8_general_ci;")
FROM information_schema.schemata
WHERE schema_name LIKE '%'

Sqlfiddle

To automate the process you will need to create a precedent or script and run each command with EXECUTE.

A Procedure model to be used as a base can be found here.

---

** Update **

With the information already available above and with the dynamic queries execution documentation, I set up the procedure below to make the automatic conversion of databases and their respective tables:

CREATE PROCEDURE change_collation()
BEGIN
  DECLARE db VARCHAR(255);
  DECLARE tab VARCHAR(255);
  DECLARE done INT DEFAULT 0;
  DECLARE done2 INT DEFAULT 0;
  DECLARE databasesCursor CURSOR FOR 
    SELECT schema_name
      FROM information_schema.schemata
     WHERE schema_name LIKE '%';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


  OPEN databasesCursor;
  REPEAT
    FETCH databasesCursor INTO db;
    IF NOT done THEN

      SET @q = CONCAT(
            "ALTER DATABASE ", 
            db,
            " CHARACTER SET utf8 COLLATE utf8_general_ci");
      PREPARE stmt1 FROM @q;
      EXECUTE stmt1;
      DEALLOCATE PREPARE stmt1;

      innerblock: BEGIN

      DECLARE tablesCursor CURSOR FOR 
        SELECT TABLE_NAME 
          FROM INFORMATION_SCHEMA.TABLES
         WHERE TABLE_TYPE = "BASE TABLE"
           AND TABLE_SCHEMA = db
           AND DATA_TYPE in ('varchar', 'text');
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;

      OPEN tablesCursor;
      REPEAT
        FETCH tablesCursor INTO tab;
        IF NOT done2 THEN

          SET @q = CONCAT(
                "ALTER TABLE ", 
                db, ".", tab,
                " CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci");
          PREPARE stmt1 FROM @q;
          EXECUTE stmt1;
          DEALLOCATE PREPARE stmt1;

        END IF;
      UNTIL done2 END REPEAT;

      CLOSE tablesCursor;

      END;

    END IF;
  UNTIL tablesCursor END REPEAT;

  CLOSE databasesCursor;
END//

To execute the Procedure, just call it that:

call change_collation();

Observing: I could not properly test the above procedure because in Sqlfiddle there is an error. Obviously they would not allow such commands on the server.

Sqlfiddle

Final considerations

To Procedure presented in this solution will list the databases and their tables, applying the change command in each one. Note, however, that there is no exceptional treatment, so if there is any problem, for example related to the lack of permission, the execution will be stopped.

It is important to always consider the permissions used to execute both the Procedure as the executed commands.

Answer 2

Has a solution with shell:

    DB="dbname"
(
    echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'
    mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names \
    | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;'
) \
| mysql "$DB"

Note: Exchange dbname for your database

On a command line to copy and paste:

DB="dbname"; ( echo 'ALTER DATABASE `'"$DB"'` CHARACTER SET utf8 COLLATE utf8_general_ci;'; mysql "$DB" -e "SHOW TABLES" --batch --skip-column-names | xargs -I{} echo 'ALTER TABLE `'{}'` CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;' ) | mysql "$DB"

https://stackoverflow.com/a/11873492/3130590