mysql – Change the "collation" and "character set" of the database, tables and corresponding columns

Question:

Some older projects were thought of their immediate objective and based on that the database, tables and columns were prepared in a limited way to the intended scenario.

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

So far, database queries have been carried out 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 every existing table, this is where the 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 mostly manual, requires the use of tools that may or may not be known to the person designated to carry it out, and is mostly time-consuming and subject to lapses.

Question

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

Answer:

I don't think there is a native command that does everything, it's possible to create a query that does the job for you.

Tables

Generating the queries needed 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.

sqldiddle

Data base

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 '%'

sqldiddle

To automate the process it will be necessary to create a procedure or script and execute each command with EXECUTE .

A procedure template to use as a basis can be found here .


** Update **

With the information already available above and with the documentation for the execution of dynamic queries , I set up the procedure below to make the automatic conversion of the 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 like this:

call change_collation();

Note: I could not properly test the above procedure because in SQLFiddle because an error occurs. Obviously they wouldn't allow such commands on the server.

sqldiddle

Final considerations

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

It is important to always consider the permissions used to execute both the procedure and the executed commands.

Scroll to Top