[Change all tables encoding] #mysql #centos

bash.sh

mysqldump --user=username --password=password --default-character-set=latin1 --skip-set-charset dbname > dump.sql
sed -r 's/latin1/utf8/g' dump.sql > dump_utf.sql
mysql --user=username --password=password --execute="DROP DATABASE dbname; CREATE DATABASE dbname CHARACTER SET utf8 COLLATE utf8_general_ci;"
mysql --user=username --password=password --default-character-set=utf8 dbname < dump_utf.sql

bitrix.php

<?php
define('STOP_STATISTICS', true);
define('BX_SECURITY_SHOW_MESSAGE', true);
define("NOT_CHECK_PERMISSIONS", true);
$_SERVER['DOCUMENT_ROOT'] = '/home/bitrix/www';
require_once($_SERVER['DOCUMENT_ROOT'] . '/bitrix/modules/main/include/prolog_before.php');

$strDB                 = 'dbName';
$strFromCharset        = 'utf8';
$strToCharset          = 'cp1251';
$strToCharsetCollation = 'cp1251_general_ci';

$sqlGetTables = '
        SELECT T.table_name, CCSA.character_set_name FROM information_schema.`TABLES` T,
                   information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
        WHERE CCSA.collation_name = T.table_collation
          AND T.table_schema = "' . $strDB . '" AND CCSA.character_set_name = "' . $strFromCharset . '"
';

$sqlConvertTablePattern = 'ALTER TABLE #TABLE# CONVERT TO CHARACTER SET #ENCODING# COLLATE #ENCODING_COLLACATION#;';

$obLink = \Bitrix\Main\Application::getConnection();

$obResult = $obLink->query($sqlGetTables);

while ($arTable = $obResult->fetch()) {

        $arNeeds = [
                '#TABLE#'    => $arTable['table_name'],
                '#ENCODING#' => $strToCharset,
                '#ENCODING_COLLACATION#' => $strToCharsetCollation,
        ];
        $sql     = str_replace(array_keys($arNeeds), array_values($arNeeds), $sqlConvertTablePattern);
        echo '<pre>'; var_dump($sql); echo '</pre>';

        $obLink->queryExecute($sql);
        echo 'DONE';
        //die();
}

change.sql

SELECT CONCAT('ALTER TABLE ',TABLE_SCHEMA,'.',TABLE_NAME,' CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;') FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'databasename' AND TABLE_TYPE="BASE TABLE";

mycollate.sh

#!/bin/bash

# mycollate.sh <database> [<charset> <collation>]
# 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
)