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

20

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

In the sense of updating and adapting to a greater scalability of these projects, we have been updating the databases for a collation and character set with a greater amplitude, as is the case of UFT-8 .

So far, database queries have been made 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, 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 the use of tools that may or may not be known to the person designated to do so and is mainly time consuming and time consuming.

Question

How do I change the collation and character set of the database, its tables and their fields at once?

    
asked by anonymous 28.02.2014 / 22:51

2 answers

10

I do not think there is a native command that does everything, you can create a query that does the job instead.

Tables

Generating the required 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 came up with 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 procedure or script and execute each command with EXECUTE .

A procedure template to be used as a base can be found here .

** Update **

With the information already provided above and with dynamic queries , I set up the following procedure to do 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:

call change_collation();

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

Sqlfiddle

Final considerations

The procedure 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, execution will be interrupted.

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

    
01.03.2014 / 00:48
1

You have a shell solution:

    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: Replace dbname with your database

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

link

    
04.05.2015 / 22:20