19

Convert All Default Characters of MySQL Databases & Tables To utf8mb4

 3 years ago
source link: https://www.codeproject.com/Articles/5328564/Convert-Default-Characters-Collations-of-MySQL-Dat
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Introduction

This article explains the simiple solution (written in C#) to convert the default characters to utf8mb4 for all MySQL databases and tables.

Background

utf8mb4 is the default character set started in MySQL 8. It has the best support for all the language characters of the world, including emoji characters etc. Thus, using utf8mb4 has the best compatible if your application uses variety of unicode characters. This article will be useful when you need to perform a batch conversion throughout all the databases and tables from old projects at once.

Using the code

You may add one of the following Nuget Package : MySQL connector for this to work:

The idea is basically get the list of databases, then loop through the database and get the tables. Perform a loop on all tables and check it's default character set, if it is not utf8mb4, then convert it one by one.

First, obtain the list of database:

Copy Code
SELECT * FROM information_schema.SCHEMATA;

In C#,

Copy Code
using (MySqlConnection conn = new MySqlConnection(constr))
{
    using (MySqlCommand cmd = new MySqlCommand())
    {
        conn.Open();
        cmd.Connection = conn;

        DataTable dtDatabase = new DataTable();

        cmd.CommandText = "SELECT * FROM information_schema.SCHEMATA;";
        MySqlDataAdapter da1 = new MySqlDataAdapter(cmd);
        da1.Fill(dtDatabase);

        conn.Close();
    }
}

This will return a table that has the following column details:

  • SCHEMA_NAME
  • DEFAULT_CHARACTER_SET_NAME
  • DEFAULT_COLLATION_NAME

Here, character set can be checked. If it is not set to "utf8mb4", then it will be modified.

But first, the following databases are needed to be ignored, as they are MySQL readonly specific info:

  • information_schema
  • mysql
  • performance_schema
  • sys

The SQL statement for conversion of database character set:

Copy Code
ALTER DATABASE `{database name}` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

In C#,

Copy Code
foreach (DataRow dr in dtDatabase.Rows)
{
    string database = dr["SCHEMA_NAME"] + "";

    // ignore
    switch (database)
    {
        case "information_schema":
        case "myql":
        case "performance_schema":
        case "sys":
            continue;
    }

    string db_charset = dr["DEFAULT_CHARACTER_SET_NAME"] + "";
    string db_collation = dr["DEFAULT_COLLATION_NAME"] + "";

    if (db_charset == "utf8mb4" && db_collation == "utf8mb4_general_ci")
    {
        // do nothing
    }
    else
    {
        cmd.CommandText = $"ALTER DATABASE `{database}` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;";
        cmd.ExecuteNonQuery();
    }
}

Next, is to obtain the list of tables with the following SQL statement:

Copy Code
show table status;

In C#,

Copy Code
cmd.CommandText = $"use `{database}`";
cmd.ExecuteNonQuery();

cmd.CommandText = "show table status;";

DataTable dtTables = new DataTable();

MySqlDataAdapter da2 = new MySqlDataAdapter(cmd);
da2.Fill(dtTables);

Then, loop through each table to obtain the following column value:

  • Name = the table's name
  • Collation = the collation character set

The SQL statement for conversion of table's character set:

Copy Code
ALTER TABLE `{tablename}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

In C#,

Copy Code
foreach (DataRow dr2 in dtTables.Rows)
{
    string tablename = dr2["Name"] + "";
    string tableCollation = dr2["Collation"] + "";

    if (tableCollation != "utf8mb4_general_ci")
    {
        try
        {
            cmd.CommandText = $"ALTER TABLE `{tablename}` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;";
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            // log the error
        }
    }
}

History

2022-03-29: First release


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK